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