THE RELATIONAL DATABASE MODEL: ADDITIONAL CONCEPTS - Fundamentals of Database Management Systems - page 152

Customer 1525

Salesperson 361
Mr. Carlyle

Customer 1700

F IGURE 6.12
Delete rule: Restrict

Delete Rule: Restrict

Three Delete Rules
Delete Rule: Restrict Again, consider the two relations in Figure 6.11. If the delete
rule between the two relations is restrict and an attempt is made to delete a record on
the '' one side'' of the one-to-many relationship, the system will forbid the delete to
take place if there are any matching foreign key values in the relation on the '' many
side.'' For example, if an attempt is made to delete the record for salesperson 361 in
the SALESPERSON relation, the system will not permit the deletion to take place
because the CUSTOMER relation records for customers 1525 and 1700 include
salesperson number 361 as a foreign key value, Figure 6.12. This is as if to say,
''You can't delete a salesperson record as long as there are customers for whom
that salesperson is responsible.'' Clearly, this is a reasonable and necessary course
of action in many business situations.

Delete Rule: Cascade If the delete rule between the two relations is cascade and
an attempt is made to delete a record on the '' one side'' of the relationship, not
only will that record be deleted but all of the records on the '' many side'' of the
relationship that have a matching foreign key value will also be deleted. That is,
the deletion will cascade from one relation to the other. For example, if an attempt
is made to delete the record for salesperson 361 in the SALESPERSON relation
and the delete rule is cascade, that salesperson record will be deleted and so too,
automatically, will the records for customers 1525 and 1700 in the CUSTOMER
relation because they have 361 as a foreign key value, Figure 6.13. It is as if the
assumption is that when a salesperson leaves the company she always takes all of
her customers along with her. While that might be a bit of a stretch in this case,
there are many other business situations where it is not a stretch at all. For example,
think about a company that has a main employee relation with name, home address,
telephone number, etc., plus a second relation that lists and describes the several
skills of each employee. Certainly, when an employee leaves the company you
would expect to delete both his record in the main employee relation and all his
records in the skills relation.

Delete Rule: Set-to-Null If the delete rule between the two relations is set-to-null
and an attempt is made to delete a record on the '' one side'' of the one-to-many
relationship, that record will be deleted and the matching foreign key values in