Enforcing Referential Integrity
A referential integrity asserts a relationship between two tables such that the values in a column of one table must match the values in a column of the second table. Traditionally, the two tables have a parent-child relationship:
• The parent table has a column, called the primary key, containing values against which other values are compared. The primary key is normally unique.
• The child table has a column, called the foreign key, whose values must match those of the primary key in the parent table.
A primary key does not have to be referenced by a foreign key (that is, there can be a parent without a child). However, every foreign key must match a primary key. There cannot be a child without a parent (that is, an orphan)--this constitutes a referential integrity violation.
For example, for the parent table, create a rule to fire on an update or delete of the primary key (an insert simply creates a parent without a child, which is not an integrity violation). The database procedure can check for foreign keys that reference the primary key and enforce the referential integrity.
For example, for the child table, create a rule to fire on an update or insert of the foreign key. The database procedure checks to make sure there is a parent.
The advantage of using a rule (as opposed to a constraint) to enforce referential integrity is that the actions performed by a rule can be more complex than merely checking for the existence of a primary key in a parent table. For example, a rule can fire a procedure to create an appropriate parent record if one does not already exist.
There are a number of ways that a referential integrity violation can be handled. Three common techniques are to reject, nullify, or cascade the firing statement.