Database Reference
In-Depth Information
to 'Finance', then there must already be a DEPARTMENT row with the primary key value of
'Finance'. If not, the modification must be prohibited.
If the parent row is required, there are no restrictions on the deletion of the child row. The
child can go away without consequence on the parent.
By The Way
When the parent has a surrogate key, the enforcement actions for update
are different between the parent and the child. On the parent side, the
surrogate key will never change, and hence update actions can be ignored. On the
child side, however, the foreign key can change if the child switches to a new parent.
Hence, on the parent side, you can ignore actions when the key is a surrogate. On the
child side, however, you must consider update actions even when the parent's key is a
surrogate.
actions When the Child Is Required
When the child is required, we need to ensure that there is at least one child row for the par-
ent at all times. The last child cannot leave the parent. For example, in the DEPARTMENT-to-
EMPLOYEE relationship, if a DEPARTMENT requires an EMPLOYEE, then the last EMPLOYEE
cannot leave the DEPARTMENT. This has ramifications on actions on the child, as shown in
Figure 6-28(b).
Enforcing required children is much more difficult than enforcing required parents. To
enforce a required parent, we just need to check for a match between primary key and foreign
key values. To enforce a required child, we must count the number of children that a parent
has. This difference forces us to write code to enforce required children. To begin, consider the
required child actions from the perspective of the parent.
actions on the Parent Row When the Child Is Required
If the child is required, then we cannot create a new parent without also creating a relation-
ship to a child. This means that either we must find an existing child row and change its
foreign key to match that of the new parent or we must create a new child row at the same
time the parent is created. If neither action can be taken, then the insertion of the new parent
must be prohibited. These rules are summarized in the first row of Figure 6-28(b).
If the child is required, then to modify the parent's primary key, either the key of at least one
child must also be changed or the update must be disallowed. This restriction never applies to
parents with surrogate keys because their values never change.
Finally, if the child is required and the parent is deleted, no action need be taken.
Because it is the child that is required, and not the parent, the parent can disappear without
any consequence.
actions on the Child Row When the Child Is Required
As shown in Figure 6-28(b), if the child is required, then no special action needs to be taken
when inserting a new child. The child comes into existence without influencing any parent.
However, there are restrictions on updating the foreign key of a required child. In partic-
ular, if the child is the last child of its current parent, then the update cannot occur. If it were
to occur, the current parent would be childless, and that is not allowed. Thus, a procedure
must be written to determine the number of children of the current parent. If that number
is two or greater, then the child foreign key value can be changed. Otherwise the update is
prohibited.
A similar restriction pertains to the deletion of required children. If the child is the last
child to the parent, then the deletion is not allowed. Otherwise, the child can be deleted
without restriction.
Implementing actions for M-O Relationships
Figure 6-30 summarizes the application of the actions in Figure 6-28 for each type of minimum
cardinality. As stated earlier, O-O relationships pose no restrictions and need not be considered.
Search WWH ::
Custom Search