The basis of Referential Integrity is foreign keys. A foreign key in one table references a primary key in another table. The primary key for a table uniquely identifies entities (rows) in the table. Primary keys are maintained with Entity Integrity, foreign keys with Referential Integrity.
Entity Integrity
Entity Integrity is the mechanism the system provides to maintain primary keys. The primary key serves as a unique identifier for rows in the table. Entity Integrity ensures two properties for primary keys:
The primary key for a row is unique; it does not match the primary key of any other row in the table.
The primary key is not null, no component of the primary key may be set to null.
The uniqueness property ensures that the primary key of each row uniquely identifies it; there are no duplicates. The second property ensures that the primary key has meaning, has a value; no component of the key is missing.
The system enforces Entity Integrity by not allowing operations (INSERT, UPDATE) to produce an invalid primary key. Any operation that creates a duplicate primary key or one containing nulls is rejected.
Referential Integrity
Referential Integrity is the mechanism the system provides to maintain foreign keys. The definition of a foreign key must specify the table whose primary key is being referenced. Referential Integrity ensures only one property for foreign keys:
A valid foreign key value must always reference an existing primary key or contain a null
A foreign key may contain a null; it does not need to reference an existing primary key (actually, it can't reference any primary key since primary keys cannot contain nulls). In FirstSQL, foreign keys may be optionally defined as not allowing nulls. To accomplish this with other DBMSs, each component of the foreign key must be separately defined as not allowing nulls.
While the Referential Integrity property looks simpler than those for Entity Integrity, the consequences are more complex since both primary and foreign keys are involved. The rule for foreign keys is:
No operation (INSERT, UPDATE) can create a non-null foreign key unless a corresponding primary key exists.
Any operation that produces a non-null foreign key value without a matching primary key value is rejected. Primary keys are also constrained by Referential Integrity:
No operation (UPDATE, DELETE) can remove or change a primary key while a referencing foreign keys exist.
The Referential Integrity rule for primary keys can be enforced in several ways. FirstSQL supports a complete set of options. These are described in the next section, Restrict, Null, Cascade.