In the preceding example, a FOREIGN KEY is declared on the Customer_Number column;
any values entered into this column must also exist in the Customer_Number column of another
table (in this case, the CUSTOMER table).
As with other constraints that allow NULL values, a column with a referential integrity
constraint can be NULL without requiring that the referenced column contain a NULL value.
Furthermore, a FOREIGN KEY constraint can be self-referential. In an EMPLOYEE table whose
primary key is Employee_Number, the Manager_Number column can have a FOREIGN KEY
declared against the Employee_Number column in the same table. This allows for the creation
of a reporting hierarchy within the EMPLOYEE table itself.
Indexes should almost always be declared on a FOREIGN KEY column to improve performance;
the only exception to this rule is when the referenced primary or unique key in the parent table is
never updated or deleted.
Complex In-Line Integrity
More complex business rules may be enforced at the column level by using a CHECK constraint.
In the preceding example, the Order_Line_Item_Qty column must never exceed 99.
A CHECK constraint can use other columns in the row being inserted or updated to evaluate
the constraint. For example, a constraint on the STATE_CD column would allow NULL values
only if the COUNTRY_CD column is not USA. In addition, the constraint can use literal values
and built-in functions such as TO_CHAR or TO_DATE, as long as these functions operate on
literals or columns in the table.
Pages:
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80