Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Interactive Paradox Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


Referential Integrity Introduction
By: Liz Woodhouse
ParadoxCommunity.com

Introduction

This article is meant as an introduction to the built-in referential integrity feature of Paradox®. For details on how to establish referential integrity between two tables, and on the impact of referential integrity, the reader should consult Paradox's help file. This article assumes the reader is familiar with interactive Paradox, especially the table structure dialog, and with the basics of relational database design (papers on the later can be found on the Paradox Resources page at RDA World Wide). It is also assumed that the reader has read the Data Integrity Introduction article on this site.

Like all forms of data integrity, referential integrity is vital in a relational database. Referential integrity is consistency of data which is shared between two tables (that is, the data in one table must reference data in another table). By this definition, one could consider table lookup, discussed in the Data Integrity Introduction article, a form of referential integrity, as it restricts valid values to those which exist in another table. However, the referential integrity feature built into Paradox further restricts our definition by adding the requirement that the referenced data (the data in the other table) must be the key (the entire key) of that other table.


What It Does

The built-in referential integrity does what we described in the introduction; it ensures that values entered into child fields exist in the parent table's key field(s). It does this in the following ways:
  1. It prevents child values not in the corresponding parent key field(s).
  2. It does one of the following, depending on your selection:
    • cascades changes made to the parent key field(s) down to the corresponding field(s) in the child records

    • OR
    • prohibits changes to the parent key field(s) if there are matching values in the corresponding field(s) of any child records.
  3. It prevents emptying or deleting a parent table if a child table both exists and has values.
  4. It creates a secondary index in the child table on the fields involved (if one doesn't already exist).
  5. It can prevent Paradox for DOS from opening the tables (if the Strict Referential Integrity box is checked).
  6. It prevents restructures which would break the referential integrity relationship.
The built-in referential integrity does these things at the table level as well as in forms and code. It should be noted that the built-in referential integrity is the only way to ensure these constraints are enforced at the table level during direct table access.


Basic Rules for Establishing Referential Integrity

We won't go into grave detail here on how to establish referential integrity, for that, please consult the help file included with Paradox. Instead, we'll cover a few basic rules which apply to referential integrity relationships in Paradox:
  • Referential integrity is established in the child table via the table create or restructure dialog.
  • Referential integrity information is stored in the .VAL files of both tables involved, so neither table can be in use when establishing the link.
  • Both tables must be keyed (some version help files or manuals may disagree with this, however, testing in v10 and v8 indicates it is accurate).
  • Both tables must be in the same directory.
  • The fields involved must be of the same type and size.
  • The entire key of the parent table must be used.
  • Existing values in the child table may be removed and placed in the temporary :PRIV:KEYVIOL.DB table if they do not have matching records in the parent table.
  • It is possible to establish referential integrity between two fields in the same table, however, you cannot have a "circular" reference (that is, you cannot establish referential integrity with just one field in one table).
When establishing referential integrity between two tables which already have data in them, it may be helpful to run not-in queries to identify existing data which already violates referential integrity constraints. Details on not-in queries can be found on the FAQ newsgroup in the following thread:
       Subject: TIP:PdoxWin:Not-in Queries:1999:09.26
      Date: Sat, 10 Feb 2001 12:24:12 -0500
      From: Michael Irwin [CTech] <mirwin@ctech.com>
Organization: CTech
Newsgroups: corel.wpoffice.paradox-faq

Limitations

The above rules give some hints to the limitations of the built-in referential integrity feature of Paradox. In this section we'll go into a bit more detail about the limitations so the reader can decide whether or not to use the built-in referential integrity. Should the reader chose not to implement the built-in referential integrity, I'd recommend taking other steps to ensure data integrity. These include those covered in the Data Integrity Introduction article, and those covered in the Do It Yourself Referential Integrity article.

The following list is not meant to comment on whether the limitations are good or bad (some limits can be good), only that they exist. Some limits are self explanatory, others are explained in more detail.
  • Tables must be in the same directory.
  • Only the parent's key can be referenced.
  • Table corruption. There have been a few reports that this is more common when tables are linked via referential integrity. Logic would dictate that when tables are linked (whether through referential integrity, table lookup or a data model), and one or more of these tables is being edited, that should a corruption-causing incident occur (hardware failure, power failure, user action), that all tables linked to the ones in use would be at greater risk for corruption than those not linked to the ones in use.
  • Deletes. The built-in referential integrity does not handle cascading deletes, thus, if you wish to delete a parent, you must first delete all of its children after first deleting the child's children, etc. beginning with the table at the end of the referential integrity link(s).
  • Referential integrity is only one level deep. This means that you cannot have the same field as child in one referential integrity relationship and parent in another. However, in most circumstances, with a properly normalized database, I believe this would not be an issue.
  • The same field cannot have more than one referential integrity master. Again, I believe this to be a non-issue with a properly normalized database.
  • Blanks. The built-in referential integrity allows blanks. If cascading updates is selected and you blank a parent field, the corresponding child field will also be blanked (it's now possible you have child records belonging to different parents, but with the same value (blank) in the linking field). However, if you then change the blank parent value, the child records are not updated (they're considered outside the scope of the built-in referential integrity).
  • Data model links. When referential integrity is used, you cannot use the child table as the parent table in a data model link. If you do, attempts to post a child record with a value in the linking field which does not already exist in the parent table will result in the "Master Record missing" error.
  • Table level operations. Things such as copying, renaming, deleting, restructuring, etc., and especially table repair can be very tricky when referential integrity is involved. For example, deleting a table using the file system (rather than Paradox) will leave behind the referential integrity "partner" table making it impossible to open said partner table unless you first delete its .VAL file. Even within Paradox, table level functions can cause headaches if you aren't familiar with all the rules. For example, the table repair utility makes a backup copy of a table when repairing it; if the table is the child in a referential integrity link, the copy becomes another child to the parent in that link and any changes to the relationship must be made in the copy table as well as the original (or you must delete the copy via Paradox (though I cannot guarantee that all versions of Paradox will remove the relationship from the parent table, so you should probably remove the relationship by restructuring the child table and then delete the child table)). So, if you decide to implement the built-in referential integrity, please read all of the help topics on that subject in the Paradox help file. I would recommend removing any referential integrity link before performing table repair.
  • A search of the Paradox newsgroups (particularly older threads) will reveal some of the difficulties others have run into when dealing with tables which use the built-in referential integrity.

Conclusion

Regardless of how you choose to implement referential integrity in your database, it's a vital part of keeping your data consistent and useful. With an understanding of the strengths and weaknesses of the built-in referential integrity feature of Paradox, you can make an educated decision about how to handle your referential integrity needs.


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.