Database Reference
In-Depth Information
referential integrity action will be overloaded with these two purposes, and its meaning
will be ambiguous, at best. Hence, in this text, we will use referential integrity actions only
for documenting required parents. We will use another technique, described next, for
documenting required children.
Documenting Required Children
One easy and unambiguous way for defining the actions to enforce a required child is to use
Figure 6-28(b) as a boilerplate document. Create a copy of this figure for each relationship that
has a required child and fill in the specific actions for insert, update, and delete operations.
For example, consider Figure 6-31, which shows the O-M relationship between DEPARTMENT
and EMPLOYEE. A given department must have a least one employee, but an employee does not
have to be assigned to a specific department. For example, the company may have an employee
who is an expediter (whose job is to solve problems throughout the company and in which-
ever department is experiencing a problem), who is not formally assigned to a department.
DEPARTMENT has a surrogate key, DepartmentID, and other columns are shown in Figure 6-31.
DEPARTMENT
Figure 6-31
DEPARTMENT-to-
EMPLOYEE O-M
Relationship
DepartmentID
DepartmentName
Budgetcode
MailStop
EMPLOYEE
EmployeeNumber
EmployeeName
Phone
Email (AK1.1)
HireDate
ReviewDate
EmpCode
DepartmentID (FK)
Figure 6-32
Actions to Enforce the
O-M Relationship Between
DEPARTMENT and
EMPLOYEE
EMPLOYEE Is
Required Child
Action on DEPARTMENT
Action on EMPLOYEE
None.
Insert
Trigger to create row in
EMPLOYEE when inserting
DEPARTMENT. Disallow
DEPARTMENT insert if EMPLOYEE
data are not available.
Modify key or
foreign key
Not possible, surrogate
key.
Trigger needed:
If not last EMPLOYEE, OK.
If last EMPLOYEE, prohibit
or assign another EMPLOYEE
Delete
None.
Trigger needed:
If not last EMPLOYEE, OK.
If last EMPLOYEE, prohibit
or assign another EMPLOYEE
Search WWH ::
Custom Search