Referential Integrity

Advantage Concepts

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.

Referential Integrity rules are stored in an Advantage Data Dictionary.

Note Referential Integrity is only supported with the Advantage proprietary tables.

Example

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.

Update and Delete Rules

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:

Delete Rules

Update Rules

NULL Values

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.

Behind The Scenes

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.

How to Define RI Rules

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.

Server Configuration

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.

Recommended Reading

Advantage Data Dictionary documentation

James R. Groff & Paul N. Weinberg Lan Times Guide to SQL. Berkeley, CA: Osborne McGraw-Hill

ISBN: 007882026X