Database Redesign - Database Processing: Fundamentals, Design, and Implementation - page 380

Changing Minimum Cardinalities
The action to be taken in changing minimum cardinalities depends on whether the change is
on the parent side or on the child side of the relationship.
Changing Minimum Cardinalities on the Parent Side
If the change is on the parent side, meaning that the child will or will not be required to have
a parent, making the change is a matter of changing whether null values are allowed for the
foreign key that represents the relationship. For example, suppose that in the 1:N relation-
ship from DEPARTMENT to EMPLOYEE the foreign key DepartmentNumber appears in the
EMPLOYEE table. Changing whether an employee is required to have a department is simply
a matter of changing the null status of DepartmentNumber.
If the change is from a minimum cardinality of zero to one, then the foreign key, which
would have been null, must be changed to NOT NULL. Changing a column to NOT NULL can
only be done if all the rows in the table have a value. In the case of a foreign key, this means
that every record must already be related. If not, all records must be changed so that all have
a relationship before the foreign key can be made NOT NULL. In the previous example, every
employee must be related to a department before DepartmentNumber can be changed to
NOT NULL.
Depending on the DBMS product in use, the foreign key constraint that defines the rela-
tionship may have to be dropped before the change is made to the foreign key. Then the foreign
key constraint can be re-added. The following SQL will work for the preceding example:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-ALTER-TABLE-CH08-08 *** */
ALTER TABLE EMPLOYEE
DROP CONSTRAINT DepartmentFK;
ALTER TABLE EMPLOYEE
ALTER COLUMN DepartmentNumber Int NOT NULL;
ALTER TABLE EMPLOYEE
ADD CONSTRAINT DepartmentFK FOREIGN KEY (DepartmentNumber)
REFERENCES DEPARTMENT (DepartmentNumber)
ON UPDATE CASCADE;
Also, cascade behavior for UPDATE and DELETE must be specified when changing the
minimum cardinality from zero to one. In this example, updates are to cascade, but deletions
will not (recall that the default behavior is NO ACTION).
Changing the minimum cardinality from one to zero is simple. Just change
DepartmentNumber from NOT NULL to NULL. You also may want to change the cascade
behavior on updates and deletions, if appropriate.
Changing Minimum Cardinalities on the Child Side
As noted in Chapter 6, the only way to enforce a minimum cardinality other than zero on the
child side of a relationship is to write triggers or application code that enforce the constraint.
So, to change the minimum cardinality from zero to one, it is necessary to write the appro-
priate triggers. Design the trigger behavior using Figure 6-28, and then write the triggers. To
change the minimum cardinality from one to zero, just drop the triggers that enforce that
constraint.
In the DEPARTMENT-to-EMPLOYEE relationship example, to require each DEPARTMENT
to have an EMPLOYEE triggers would need to be written on INSERT of DEPARTMENT and on
UPDATE and DELETE of EMPLOYEE. The trigger code in DEPARTMENT ensures that an
EMPLOYEE is assigned to the new DEPARTMENT, and the trigger code in EMPLOYEE ensures
that the employee being moved to a new department or the employee being deleted is not the
last employee in the relationship to its parent.