Entity and Referential Integrity
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
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 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
- 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 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
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 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.
- A valid foreign key value must always reference an existing primary
key or contain a null
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:
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 (INSERT, UPDATE) can create a non-null foreign
key unless a corresponding primary key exists.
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.
- No operation (UPDATE, DELETE) can remove or change a primary key
while a referencing foreign keys exist.
Return to Contents Page:
Protect Your Data?
Return to Can your Database do this?
Copyright © 1997 FFE Software, Inc. All Rights Reserved WorldWide