Databases Reference
In-Depth Information
to one or more matching columns (with the same or compatible data types) in the
referenced table. The referenced column values must be unique, so the foreign key
must be related to the referenced table's primary key or a unique constraint.
Think about customer orders. Each order contains one or more inventory items.
You would use referential integrity between the orders table(s) and the inventory
table to ensure valid part numbers, and through these, valid inventory items.
In some cases, the requirements for ensuring referential integrity go beyond
what can be accomplished through a foreign key constraint. In that situation, it
might be necessary to use a trigger to enforce integrity. A
trigger
is a specialized
type of executable procedure, in short, a program.
Adding Policy Integrity
It's difficult to make any blanket statements about policy integrity because the
requirements will be business specific. Policy integrity requirements are defined
through business rules which will naturally vary, based on businesses' specific
requirements. The way in which policy integrity is enforced depends on the busi-
ness rule.
In many cases, policy integrity is defined as an extension of other types of
data integrity. Others require more complicated programming logic. For example:
▲
You require that each customer order must identify the employee who
wrote up the order. You could enforce this through relational integrity,
relating orders to employees.
▲
You require a phone number for each customer. This can be enforced
through nullability requirements and by applying a check constraint.
▲
More than 8 hours worked in a day or 40 hours worked in a week must
be identified as overtime. You could use a simple check constraint to
limit the hours, but to calculate the overtime hours and store them sepa-
rately would require more. In this case, you would likely create a trigger
to perform the calculation and save the overtime hours appropriately.
Policy integrity is usually enforced through the DBMS whenever possible, as
long as it does not adversely impact performance to the point that it becomes
unacceptable. Policy integrity requirements, especially more complicated
requirements, can also be implemented through the database application. In
some cases, where the requirements are extensive, they are implemented through
a separate application server.
Introducing Triggers
Triggers run, or fire, in response to events. Events can be related to data or data
objects. For example, you can have triggers that fire when data is entered, updated,
or modified in a table, either through the actions of a GUI-based utility or a DML
