Referential Integrity

Referential Integrity
A foreign key is a column, or set of columns, that links each row in the child table containing
the foreign key to the row of the parent table containing the matching candidate
key value. Referential integrity means that, if the foreign key contains a value, that value
must refer to an existing, valid row in the parent table (see Section 3.3.3). For example,
the branch number column branchNo in the PropertyForRent table links the property to that
row in the Branch table where the property is assigned. If the branch number is not null, it
must contain a valid value from the column branchNo of the Branch table, or the property
is assigned to an invalid branch office.
The ISO standard supports the definition of foreign keys with the FOREIGN KEY
clause in the CREATE and ALTER TABLE statements. For example, to define the foreign
key branchNo of the PropertyForRent table, we include the clause:
FOREIGN KEY(branchNo) REFERENCES Branch
SQL rejects any INSERT or UPDATE operation that attempts to create a foreign key
value in a child table without a matching candidate key value in the parent table. The
action SQL takes for any UPDATE or DELETE operation that attempts to update or delete
a candidate key value in the parent table that has some matching rows in the child table is
dependent on the referential action specified using the ON UPDATE and ON DELETE
subclauses of the FOREIGN KEY clause. When the user attempts to delete a row from a
parent table, and there are one or more matching rows in the child table, SQL supports four
options regarding the action to be taken:
n CASCADE Delete the row from the parent table and automatically delete the matching
rows in the child table. Since these deleted rows may themselves have a candidate
key that is used as a foreign key in another table, the foreign key rules for these tables
are triggered, and so on in a cascading manner.
n SET NULL Delete the row from the parent table and set the foreign key value(s) in
the child table to NULL. This is valid only if the foreign key columns do not have the
NOT NULL qualifier specified.
n SET DEFAULT Delete the row from the parent table and set each component of the
foreign key in the child table to the specified default value. This is valid only if the
foreign key columns have a DEFAULT value specified (see Section 6.3.2).
n NO ACTION Reject the delete operation from the parent table. This is the default
setting if the ON DELETE rule is omitted.
QL supports the same options when the candidate key in the parent table is updated.
With CASCADE, the foreign key value(s) in the child table are set to the new value(s) of
the candidate key in the parent table. In the same way, the updates cascade if the updated
column(s) in the child table reference foreign keys in another table.
For example, in the PropertyForRent table, the staff number staffNo is a foreign key referencing
the Staff table. We can specify a deletion rule such that, if a staff record is deleted
from the Staff table, the values of the corresponding staffNo column in the PropertyForRent
table are set to NULL:
FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL
Similarly, the owner number ownerNo in the PropertyForRent table is a foreign key referencing
the PrivateOwner table. We can specify an update rule such that, if an owner number is
updated in the PrivateOwner table, the corresponding column(s) in the PropertyForRent table
are set to the new value:
FOREIGN KEY (ownerNo) REFERENCES PrivateOwner ON UPDATE CASCADE