Progress
Embedded SQL-92
Guide and Reference
Referential Constraints
Sometimes specific columns in tables in two different locations in a database must be identical. The values for each row in a column or group of columns taken together must be identical to a set of columns in another table. You define the requirement that a matching row must exist in the referenced table with a referential constraint. For example, the employee number of the employee table might be in the given range, but the employee number might no longer exist in the master employee table because the employee has resigned. Use referential constraints to enforce this kind of limit.
EXAMPLEIn the next example, the value in the item_no column of the supplier_item table depends on the value in the item_no column of the item table. The item_no column of the supplier_item table references the item_no column of the item table. The item_no column is a foreign key in the supplier_item table:
A foreign key is a column or combination of columns that references a primary key or a candidate key of some table. The foreign key value is either NULL or exists as the primary key value. The table that contains the foreign key is called the referencing table. The table that contains the primary or the candidate key is called the referenced table. You can specify a referential constraint at the column level or at the table level.
During INSERT or UPDATE operations on a table containing a foreign key, the database checks to determine if the foreign key value matches a corresponding primary key value. If it does not match, the operation returns an error.
During UPDATE or DELETE operations on a table containing a primary or candidate key, if the values to be deleted or updated match the foreign key of the referencing table, the operation returns an error. A value corresponding to a primary or candidate key cannot be updated or deleted if there are references to it.
When you want to drop a table containing a primary or candidate key, the database checks to see if the table has any references to it. If there are tables containing foreign keys that reference the primary or candidate keys of the table you want to drop, the operation returns an error.
Column Level Foreign Key Constraint
If a foreign key constraint specification involves only one column, you specify a column level foreign key constraint.
EXAMPLEIn the next example, item_no is the foreign key referencing the item table, and the foreign key is specified at the column level:
If a foreign key references a candidate key, you must name the referenced column in a column list. If a foreign key references a primary key, the column list is optional.
EXAMPLEThe next example illustrates both conditions. In the example, invoice.item_no references the primary key of the item table. The invoice.partnum column references parts.part_no. Since parts.part_no is a primary key, the parts (part_no) column list reference in invoice.part_no is optional:
Table Level Foreign Key Constraint
If a foreign key constraint specification involves more than one column, you must specify the constraint at the table level.
EXAMPLEIn this example, the foreign key (empno, projno) of the hours_worked table references the primary or candidate key (empno, projno) of the assignments table:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |