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.
Reject Technique for Enforcing Referential Integrity
Rejecting a value that violates an integrity constraint rolls back the statement that fired the rule. The raise error statement performs this function, informing the application that the results from the statement firing the rule violated some specified condition or constraint. The response to a raise error statement is the same as if the statement that fired the rule experienced a fatal error—the firing statement is aborted and any changes to the database resulting from the statement and subsequent rule firing are rolled back.
Example: Enforce Referential Integrity Between an Employee and Manager
For example, the following database procedure can be invoked by a rule to enforce referential integrity between an employee and the employee’s manager and department. The code for the procedure, which has a procedure name of valid_mgr_dept, is shown as it is entered in the VDBA Create Procedure dialog:
Parameters
ename varchar(30),
mname varchar(30),
dname varchar(10)
Declare Section
msg varchar(80) not null;
check_val integer;
mgr_dept varchar(10);
Statements
/* Check to see if there is a matching manager */
select count(*) into :check_val from manager
where name = :mname and dept = :dname;
if check_val = 0 then
msg = 'Error 1: Manager "' +
:mname + '" not found in that dept.';
raise error 1 :msg;
return;
endif;
/* Check to be sure there is a matching dept */
select count(*) into :check_val
from dept where name = :dname;
if check_val = 0 then
msg = 'Error 2: Department "' +
:dname + '" not found.';
raise error 2 :msg;
return;
endif;
msg = 'Employee "' + ename + '" updated ' +
'(mgr = "' + mname + '", dept = "' + dname + '")';
message :msg;
insert into emplog values (:msg);
This procedure checks the manager table to make sure that the employee’s manager manages the department to which the employee is assigned. It checks the department table to see that the department is valid. If any of these checks fail, an error is issued and the new employee is not inserted into the employee table. If the constraint is met, a message is displayed and a log record is inserted into a journal table.
After defining this database procedure, create a rule to invoke it after updates and inserts, and enter the following for the procedure parameters:
ename = new.name, mname = new.mgr, dname = new.dept
Note: Any value referring to a column name in a parameter list must be preceded by a correlation name. Using the correlation name “old” or “new,” specify whether you want to use the column value before or after the operation, respectively.
Nullify Technique for Enforcing Referential Integrity
Nullifying is a second course of action in response to a violation of a referential integrity constraint if a foreign key does not have a matching primary key. (Nullifying means that the columns in the records in violation of the constraint are made null, as opposed to deleting the records or returning an error that the constraint was violated.)
You are not restricted to nullifying the foreign key. You can modify the value to another defined value. Because null is not a value, it traditionally does not participate in the referential integrity relationship. Thus, a child row with a null foreign key value is not generally considered an orphan. However, rules provide you with the facilities to do such things as simulate matches on nulls.
For example, the following database procedure, nullify_children, can be invoked by a rule, when a parent row is deleted, to nullify all child entries belonging to that parent:
Parameters
me varchar(10)
Declare Section
msg varchar(80) not null;
Statements
msg = 'Nullifying child(ren) of "' + :me + '"';
message :msg;
update person set parent = NULL where parent = :me;
if iirowcount > 0 then
msg = 'Nullified ' + varchar(:iirowcount) +
' child(ren) from "' + :me + '"';
else
msg = 'No children nullified from "' + :me + '"';
endif;
message :msg;
After defining this database procedure, create a rule to invoke it after deletes, and enter the following for the procedure parameters:
me = old.name
Cascade Technique for Enforcing Referential Integrity
Cascading is the third available option in response to a violation of a referential integrity constraint. (Cascading means that the original update applies to other records that violate the constraint.) If the statement that violates the constraint is:
• An insert or update, cascading consists of inserting the offending foreign key into the primary key column.
• A delete, cascading means not only deleting the primary key, but also deleting all foreign keys that match that primary key.
The database procedure shown in this example, delete_children, can be used to implement a cascading delete rule. The procedure can be invoked by a rule, when a parent row is deleted, to delete all child entries belonging to that parent:
Parameters
me varchar(10)
Declare Section
msg varchar(80) not null;
Statements
msg = 'Deleting child(ren) from "' + :me + '"';
message :msg;
delete from person where parent = :me;
if iirowcount > 0 then
msg = 'Deleted ' + varchar(:iirowcount) +
' child(ren) from "' + :me + '"';
else
msg = 'No children deleted from "' + :me + '"';
endif;
message :msg;
After defining this database procedure, create a rule to invoke it after deletes, and enter the following for the procedure parameters:
me = old.name
When the rule is fired after the initial delete statement, it executes the delete_children database procedure, which deletes all children whose parent is the current person. Each delete statement in the delete_children procedure, in turn, also fires the delete rule, until a particular person has no descendants. The message statements that are executed before and after a row is deleted demonstrate the order in which the tree is traversed.
Note: In this example, the person table is self-referencing, and functions like a self-join. Referential integrity does not require two separate tables. Here the primary key is name and the foreign key is parent, both of which are in the person table.