Database Reference
In-Depth Information
In most cases, required children constraints are enforced using
triggers
, which are
modules of code that are invoked by the DBMS when specific events occur. Almost all DBMS
products have triggers for insert, update, and delete actions. Triggers are defined for these ac-
tions on a particular table. Thus, you can create a trigger on
CUSTOMER INSERT
or a trigger on
EMPLOYEE UPDATE
, and so forth. You will learn more about triggers in Chapter 7.
To see how you would use triggers to enforce required children, consider Figure 6-28(b)
again. On the parent side, we need to write a trigger on insert and update on the parent row.
These triggers either create the required child or they steal an existing child from another par-
ent. If they are unable to perform one of these actions, they must cancel the insert or update.
On the child side, a child can be inserted without problem. Once a child gets a parent,
however, it cannot leave that parent if it is the last or only child. Hence, we need to write up-
date and delete triggers on the child that have the following logic: If the foreign key is null, the
row has no parent, and the update or delete can proceed. If the foreign key does have a value,
however, check whether the row is the last child. If the row
is
the last child, then the trigger
must do one of the following:
Delete the parent.
●
Find a substitute child.
●
Disallow the update or delete.
●
None of these actions will be automatically enforced by the DBMS. Instead, you must write code
to enforce these rules. You will see generic examples of such code in the next chapter and real
examples for SQL Server 2012 in Chapter 10A, Oracle Database 11
g
Release 2 in Chapter 10B, and
MySQL 5.6 in Chapter 10C.
Implementing actions for M-M Relationships
It is very difficult to enforce M-M relationships. All of the actions in both Figure 6-28(a) and
Figure 6-28(b) must be enforced simultaneously. We have a needy parent and a needy child,
and neither will let go of the other.
Consider, for example, what would happen if we change the relationship between
DEPARTMENT and EMPLOYEE in Figure 6-29 to M-M, and the effect that would have on
the creation of new rows in DEPARTMENT and EMPLOYEE. On the DEPARTMENT side, we
must write an insert department trigger that tries to insert a new EMPLOYEE for the new
DEPARTMENT. However, the EMPLOYEE table will have its own insert trigger. When we try
to insert the new EMPLOYEE, the DBMS calls the insert employee trigger, which will prevent
the insertion of an EMPLOYEE unless it has a DEPARTMENT row. But the new DEPARTMENT
row does not yet exist because it is trying to create the new EMPLOYEE row, which does not
exist because the new DEPARTMENT row does not yet exist, and 'round and 'round we go!
Now consider a deletion in this same M-M relationship. Suppose we want to delete a
DEPARTMENT. We cannot delete a DEPARTMENT that has any EMPLOYEE children. So,
before deleting the DEPARTMENT, we must first reassign (or delete) all of the employees
in that department. However, when we try to reassign the last EMPLOYEE, an EMPLOYEE
update trigger will be fired that will not allow the last employee to be reassigned. (The trig-
ger is programmed to ensure that every DEPARTMENT has at least one EMPLOYEE.) We
have a stalemate; the last employee cannot get out of the department, and the department
cannot be deleted until all employees are gone!
This problem has several solutions, but none are particularly satisfying. In the next chapter,
we will show one solution using SQL Views. That solution is complicated and requires careful pro-
gramming that is difficult to test and fix. The best advice is to avoid M-M relationships if you can.
If you cannot avoid them, budget your time with foreknowledge that a difficult task lies ahead.
Designing Special Case M-M Relationships
Not all M-M relationships are as bad as the last section indicates. Although M-M relation-
ships between strong entities generally are as complicated as described, M-M relationships
between strong and weak entities are often easier. For example, consider the relationship
between COMPANY and PHONE_CONTACT in Figure 6-29. Because PHONE_CONTACT
Search WWH ::
Custom Search