Database Reference
In-Depth Information
Defining a Deletion Rule for Each Relationship
The first characteristic you'll establish for the relationship is a deletion rule. This rule de-
termineswhatyourRDBMSshoulddowhenyouplacearequesttodeleteagivenrecordin
the parent table of the relationship. Deletion rules are crucial to relationship-level integrity
because they help guard against orphaned records, which are records in the child table that
have no relationship whatsoever to any records in the parent table.
ThesearethefivetypesofdeletionrulesyoucandefineandtheactionstheRDBMSshould
take when a given rule is in force.
1. Deny: The RDBMS will not delete the record in the parent table, but will instead
keep the record and designate it as “inactive.”
2. Restrict: The RDBMS will not delete the record in the parent table if related re-
cords exist in the child table. You must have the RDBMS delete all of the related
records in the child table before you can have it delete the record in the parent
table.
3. Cascade: The RDBMS will take two specific actions: It will delete the record in
the parent table, and it will also automatically delete all related records in the child
table.
4. Nullify: The RDBMS will delete the record in the parent table and will then update
the foreign key values of related records in the child table to null. If you are going
to use this deletion rule, you must modify the foreign key's field specifications and
set the Null Support logical element to “Nulls Allowed.”
5. Set Default: The RDBMS will delete the record in the parent table and will then
update the foreign key values of related records in the child table to the current De-
fault Value logical element setting in the foreign key's field specifications. Obvi-
ously, you must have a setting for the Default Value element in order to use this
rule.
Use a Restrict deletion rule as a matter of course and the other rules as appropriate. The
best way to determine which deletion rule is appropriate for a given relationship is to ex-
amine the relationship diagram. Consider the diagram in Figure 10.56 .
Search WWH ::




Custom Search