Referential Integrity (RI) is the means by which primary/foreign key relationships are enforced in a database. By specifying RI rules you can have the database guarantee, for example, that every sales representative is assigned to a valid office. Through the use of RI constraints, many business rules can be enforced by the database server, instead of your application.
The terms "primary key" and "foreign key" are used throughout this documentation.
Primary Key - A unique identifier for a table. A column or column combination with the property that, at any given time, no two rows of the table contain the same value in that column or column combination.
Foreign Key - A foreign key is a column or combination of columns whose values match the primary key of some other table. A foreign key does not have to be unique; in fact, foreign keys are often in a many-to-one relationship to a primary key. Foreign key values should be copies of the primary key values; no value in the foreign key except NULL should ever exist unless the same value exists in the primary key. A foreign key may be NULL; if any part of a composite foreign key is NULL, the entire foreign key is NULL.
Referential Integrity rules are stored in an Advantage Data Dictionary.
Lets look at a simple example using two tables: SALES_REPS and OFFICES. The following SQL statement is syntactically correct, and with the current state of our example database this statement would execute and add a new sales rep, "Doug Henry", who works in office number 45:
INSERT INTO SALES_REPS (EMPL_NUM, NAME, REP_OFFICE)
VALUES (69, ‘Doug Henry’, 45)
No validity checking has been enforced, and even if office number 45 does not exist in the OFFICES table, Doug Henry will still exist in our database.
To remedy this situation, we’ll define one RI rule linking the OFFICES.OFFICE field (as the primary key) to the SALES_REPS.REP_OFFICE field (as the foreign key). With this rule in place, the previous SQL statement would not execute without returning an error. Before adding Doug to the SALES_REPS table, the Advantage Database Server will first ensure that all foreign keys in this new row reference existing primary keys in their parent tables. Because office number 45 does not exist, the INSERT operation will fail. The application developer does not write any code to enforce this rule. The database server does all the work; the developer can simply catch this error, notify the user of the violation, and request correct data.
Referential Integrity allows update and delete rules to be specified for each relation you define. These rules affect the behavior of the Advantage Database Server when updating and deleting existing parent rows. There are four possible update/delete rules that can be performed:
RESTRICT - Prevents deletion of a row from a parent table if children of the row still exist in a child table. If applied to our example above, this would make it illegal to delete an office if any sales representatives were still assigned to the office.
CASCADE - When a parent row is deleted, automatically delete all child rows. If applied to our example above, deleting an office would automatically delete every sales representative assigned to the office.
SET_NULL - When a parent row is deleted, automatically set all foreign key values to NULL. If applied to our example above, this would make deleting an office set every sales representative’s office assignment to an unknown office.
SET_DEFAULT - When a parent row is deleted, automatically set all foreign key values to their default values. See Advantage Data Dictionary for more information on default field values. If applied to our example above, this rule would assign sales representatives to some default office if their office were ever removed. The default office is stored within the data dictionary and is the default field value for the office field.
RESTRICT - Prevents updating a primary key if foreign key values still exist in a child table. If applied to our example above, this would make it illegal to change an office number if sales representatives were still assigned to the office.
CASCADE - When a primary key is updated, automatically update all foreign key values. If applied to our example above, updating an office number would also update the REP_OFFICE field for each sales representative currently assigned to the office.
SET_NULL - When a primary key is updated, automatically set all foreign key values to NULL. If applied to our example above, this would make updates to the office number set every sales representative’s office assignment to an unknown office.
SET_DEFAULT - When a primary key is updated, automatically set all foreign key values to their default values. See Advantage Data Dictionary for more information on default field values. If applied to our example above, this rule would assign sales representatives to some default office if their office number were ever updated. The default office is stored within the data dictionary and is the default field value for the office field.
Advantage primary keys can contain one NULL value. Advantage foreign keys (as long as they are not defined with the unique index type) can contain multiple NULL values. NULL values in foreign keys are often a necessity when dealing with updates to a database that utilizes RI constraints to break a dependency between primary and foreign keys.
If you have defined multiple RI rules in your database (a very likely scenario) it is important to keep in mind the operations that the server will be performing on your behalf. Tables involved in RI rules are grouped together into graphs. The server must have all tables in the graph open to enforce the RI rules that you have placed on the database.
For example, if your application is designed to use the example database described above, and opens the OFFICES table, no extra work will be done. But the first time you attempt an update operation on the OFFICES table, the server will also open the SALES_REPS table in the background, to maintain your RI constraint.
There are three different ways to define and modify RI rules; Advantage Data Architect, TAdsDictionary component in the Advantage TDataSet Descendant, and Advantage Client Engine (ACE) API calls.
At design-time, the easiest way to define and view your database dictionary setup is through the Advantage Data Architect utility. See the Advantage Data Architect Help (ARC.HLP) for more information. (Note that each of the Advantage products and their corresponding Help files are installed separately.)
If you would like run-time access to data dictionary information, and if you are using the Advantage TDataSet Descendant, see TAdsDictionary (see ADE.HLP). (Note that each of the Advantage products and their corresponding Help files are installed separately.)
Direct access to the Database Dictionary is also available through Advantage Data Dictionary API's (prefixed AdsDD, for example AdsDDCreateRefIntegrity) by accessing the Advantage Client Engine API directly.
There is no additional server configuration related to the use of referential integrity. However, depending on your database design and the number of concurrent users, it may be necessary to increment the configured number of work areas and locks. The Advantage Database Server will use more work areas when enforcing referential integrity rules. Also, cascading deletes may often need to acquire a large number of record locks (depending on the number of records affected by the cascade) before performing the actual cascade operation. For more information on configuration parameters, see Advantage Database Server Configuration.
Advantage Data Dictionary documentation
James R. Groff & Paul N. Weinberg Lan Times Guide to SQL. Berkeley, CA: Osborne McGraw-Hill