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.
Note Referential Integrity is only supported with the Advantage proprietary tables.
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.