tailieunhanh - Oracle Database 11g DBA Handbook P2

Referential Integrity Values The referential integrity or FOREIGN KEY constraint is more complicated than the others we have covered so far because it relies on another table to restrict what values can be entered into the column with the referential integrity constraint. 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. | Chapter 1 Getting Started with the Oracle Architecture 17 Referential Integrity Values The referential integrity or FOREIGN KEY constraint is more complicated than the others we have covered so far because it relies on another table to restrict what values can be entered into the column with the referential integrity constraint. 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. Multiple CHECK constraints are allowed on a column. All the CHECK constraints must evaluate to TRUE to allow a value to be entered in the column. For .