Database Reference
In-Depth Information
Figure 6-30
Actions to Apply to Enforce
Minimum Cardinality
Relationship
Minimum
Cardinality
Action to Apply
Remarks
O-O
Nothing
M-O
Parent-required actions
[Figure 6-28(a)]
Easily enforced by DBMS;
define referential integrity
constraint and make foreign
key NOT NULL.
O-M
Child-required actions
[Figure 6-28(b)]
Difficult to enforce. Requires
use of triggers or other
application code.
M-M
Parent-required actions
and child-required actions
[Figures 6-28(a) and 6-28(b)]
Very difficult to enforce. Requires
a combination of complex
triggers. Triggers can lock each
other out. Many problems!
M-O relationships require that the actions in Figure 6-28(a) be enforced. We need to
make sure that every child has a parent and that operations on either parent or child rows
never create orphans.
Fortunately, these actions are easy to enforce using facilities available in most DBMS
products. It turns out that we can enforce these actions with just two limitations. First, we
need to define a referential integrity constraint that ensures that every foreign key value has
a match in the parent table. Second, we make the foreign key column NOT NULL. With these
two restrictions, all of the actions in Figure 6-28(a) will be enforced.
Consider the DEPARTMENT-to-EMPLOYEE example. If we define the referential integrity
constraint
DepartmentName in EMPLOYEE must exist in DepartmentName in DEPARTMENT
then we know that every value of DepartmentName in EMPLOYEE will match a value in
DEPARTMENT. If we then make DepartmentName required, we know that every row in
EMPLOYEE will have a valid DEPARTMENT.
Almost every DBMS product has facilities for defining referential integrity constraints.
You will learn how to write SQL statements for that purpose in the next chapter. In those
statements, you will have the option of declaring whether updates and deletions are to cas-
cade or are to be prohibited. Once you have defined the constraint and made the foreign key
NOT NULL, the DBMS will take care of all of the actions in Figure 6-28(a) for you.
By The Way
Recall that, in a 1:1 relationship between strong entities, the key of either
table can be placed in the other table. If the minimum cardinality of such
a relationship is either M-O or O-M, it is generally best to place the key in the optional
table. This placement will make the parent required, which is easier to enforce. With a
required parent, all you have to do is define the referential integrity constraint and set
the foreign key to NOT NULL. However, if you place the foreign key so that the child is
required, let the work begin! You will have your hands full, as you are about to see.
Implementing actions for O-M Relationships
Unfortunately, if the child is required, the DBMS does not provide much help. No easy mech-
anism is available to ensure that appropriate child foreign keys exist nor is there any easy way
to ensure that valid relationships stay valid when rows are inserted, updated, or deleted. You
are on your own.
Search WWH ::
Custom Search