Database Reference
In-Depth Information
When a record in the EMPLOYEES table is deleted, what should
happen to related records in the ORDERS table?
Theansweryoureceivedependsonhowtheorganizationisusingthedatawithinthetables
and will usually indicate which deletion rule you should use for the relationship.
You can't delete an employee record; you have to designate the employee as inact-
ive. (Use a
Deny
rule.)
You can't delete an employee record if there are related order records. (Use a
Restrict
rule.)
You must first delete the orders associated with the employee from the ORDERS
table and then delete the employee from the EMPLOYEES table. (Use the
Restrict
rule.)
All orders associated with the employee must be deleted from the ORDERS table
as well. (Use the
Cascade
rule.)
The employee number for all orders associated with the employee must be deleted.
(Use a
Nullify
rule.)
The employee number for all orders associated with the employee must be reset to
the lead salesperson's employee number. (Use a
Set Default
rule.)
If you (or the people you're working with) cannot easily provide an answer, make note of
the relationship and continue with another relationship. You'll revisit all of these relation-
ships when you establish business rules for the database in
Chapter 11
, “
Business Rules
.”
For now, let's assume you received the first reply and you're going to use a Deny rule for
the relationship.
Once you've identified the type of deletion rule you want to use for the relationship, des-
ignate the rule on the relationship diagram. Use (D) for Deny, (R) for Restrict, (C) for
Cascade, (N) for Nullify, and (S) for Set Default. Place the designation
under
the connec-
tion line of the
parent
table.
Figure 10.57
shows the revised relationship diagram for the
EMPLOYEES and ORDERS tables.