A referential CONSTRAINT definition defines an integrity condition (restrictions for columns values, see data integrity) that must be satisfied by all the rows in two tables. The resultant dependency between two tables affects changes to the rows contained in them.
FOREIGN KEY [<referential_constraint_name>] (<referencing_column>,...)
REFERENCES <referenced_table> [(<referenced_column>,...)] [<delete_rule>]
Reference table, referenced column (table/column that is to be addressed)
Referencing column (column that establishes the link to the column that is to be addressed)
Dependency between the model tables customer and reservation. The referential CONSTRAINT definition is specified when the reservation table is defined. The reservation table is assigned a foreign key that corresponds to the key in the customer table.
CREATE TABLE reservation (rno FIXED(4) PRIMARY KEY, cno FIXED(4), hno FIXED(4), roomtype CHAR(6), arrival DATE, departure DATE,
FOREIGN KEY customer_reservation (cno) REFERENCES customer ON DELETE CASCADE)
The defined relationship is called customer_reservation. The DELETE rule ON DELETE CASCADE specifies that deleting rows in the customer table causes the associated rows in the reservation table to be deleted automatically.
A referential CONSTRAINT definition can be used in a CREATE TABLE statement or ALTER TABLE statement. The table specified in the corresponding statement ( table_name) is referred to in the following sections as the referencing table.
The referencing columns are specified in the referential CONSTRAINT definition. The referencing columns must denote columns in the referencing table and must all be different. They are also called foreign key columns.
· If no referencing columns are specified, the result is the same as if the key columns in the referenced table were specified in the defined sequence.
· If referenced columns are specified that are not the key in the referencing table, the referenced table must have a UNIQUE definition whose column names and sequence match those of the referenced columns.
· The number of referenced columns is equal to the number of referencing columns.
· The nth referencing column corresponds to the nth referenced column.
· The data type and the length of each referencing column must match the data type and length of the corresponding referenced column.
The referencing table and the referenced table must be base tables, but not temporary base tables.
The current user must have the ALTER privilege for the referencing table and the REFERENCE privilege for the referenced table.
The name of a referential constraint can be specified after the keywords FOREIGN KEY.
· If the name of a referential constraint is specified, it must be different from all other names of referential constraints for the referencing table.
· If no referential constraint name is specified, the database system assigns a unique name (based on the referencing table).
The following restrictions apply when rows in the referencing table are added or modified:
Let Z be an inserted or modified row. Rows can only be inserted or modified if one of the following conditions is fulfilled for the associated referenced table:
· Z is a
· Z contains a NULL value in one of the referencing columns.
· The referential CONSTRAINT definition defines the DELETE rule ON DEFAULT SET DEFAULT, and Z contains the DEFAULT value in each referencing column.
A referential CONSTRAINT
definition is self-referencing if the referenced and referencing tables
With self-referencing referential CONSTRAINT definitions, the order in which a DELETE statement is processed can be important.
Specifying CASCADE: all of the rows affected by the DELETE statement are first deleted irrespective of the referential CONSTRAINT conditions. All matching rows in the rows that have just been deleted are then also deleted. As a result, all of the matching rows in the previous deletion operation are deleted, etc.
Specifying SET NULL or SET DEFAULT: all of the rows affected by the DELETE statement are first deleted irrespective of the referential CONSTRAINT conditions. Following this, SET NULL or SET DEFAULT is applied to the matching row.
· When rows are deleted from a referenced table, the number of rows deleted is entered in the third SQLERRD entry in the SQLCA database.
When an INSERT or UPDATE
statement is applied to a referencing table, irrespective of the isolation level
defined for the current session, the database uses a blocking behavior for the
referenced table that corresponds to isolation level
When a DELETE statement is applied to a referenced table, the database system uses a locking behavior that corresponds to isolation level 3.