Referential Constraint
To validate an entry against the contents of a column in another table (or another column in the same table), specify a referential constraint using the REFERENCES option. The references option maintains the referential integrity of your tables.
The column-level referential constraint has the following syntax:
REFERENCES [schema.] table_name (column_name)[referential actions] [constraint_with_clause]
The following example of a column-level referential constraint ensures that no employee is assigned to a department that is not present in the dept table:
CREATE TABLE emp (ename CHAR(10),
edept CHAR(10) REFERENCES dept(dname));
The table-level referential constraint has the following syntax, including the FOREIGN KEY... REFERENCES option:
FOREIGN KEY (column_name{,column_name})
REFERENCES [schema.] table_name [(column_name{,column_name}][referential actions] [constraint_with_clause]
The following example of a table-level referential constraint verifies the contents of the name and empno columns against the corresponding columns in the emp table to ensure that anyone entered into the table of managers is on file as an employee:
CREATE TABLE mgr (name CHAR(10),
empno CHAR(5),
...
FOREIGN KEY (name, empno) REFERENCES emp);
The preceding example omits the names of the referenced column; the emp table must have a primary key constraint that specifies the corresponding name and employee number columns.
referential actions
Allow the definition of alternate processing options in the event a referenced row is deleted, or referenced columns are updated when there are existing matching rows. A referential action specifies either an update rule or a delete rule, or both, in either sequence.
The ON UPDATE and ON DELETE rules have the following syntax:
ON UPDATE {CASCADE | SET NULL | RESTRICT | NO ACTION}
or
ON DELETE {CASCADE | SET NULL | RESTRICT | NO ACTION}
ON UPDATE CASCADE
Causes the values of the updated referenced columns to be propagated to the referencing columns of the matching rows of the referencing table.
ON DELETE CASCADE
Specifies that if a delete is attempted on a referenced row that has matching referencing rows, the delete is “cascaded” to the referencing table as well. That is, the matching referencing rows are also deleted. If the referencing table is itself a referenced table in some other referential relationship, the delete rule for that relationship is applied, and so forth. (Because rule types can be mixed in a referential relationship hierarchy, the second delete rule can be different from the first delete rule.) If an error occurs somewhere down the line in a cascaded operation, the original delete fails, and no update is performed.
NO ACTION
Is the default behavior of returning an error upon any attempt to delete or update a referenced row with matching referencing rows.
RESTRICT
Behaves the same as NO ACTION, but returns a different error code. Both options are supported for ANSI SQL compliance.
SET NULL
Causes the referencing columns of the matching rows to be set to the null value (signifying that they do not currently participate in the referential relationship). The columns can be updated later to a non-null values, at which time the resulting row must find a match somewhere in the referenced table.
Example
Here is an example of the delete and update rules:
CREATE TABLE employee (empl_no INT NOT NULL PRIMARY KEY)
emp_name CHAR(20) NOT NULL,
dept_id CHAR(6) REFERENCES department (dept_id)
ON DELETE CASCADE ON UPDATE CASCADE,
mgrno INT REFERENCES employee (empl_no) ON UPDATE CASCADE
ON DELETE SET NULL);
If a department row is deleted, all employees in that department are also deleted. If a department ID is changed in the department table, it is also changed in all referencing employee rows.
If a manager's ID is changed, his employees are changed to match. If the manager is fired, all his employees have mgr_id set to null.
The following considerations apply to the table and column being referenced (the column specified following the keyword references):
• The referenced table must be an existing base table (it cannot be a view).
• The data types of the columns must be comparable.
• You must have references privilege for the referenced columns.
• If the table and column names are specified, the referenced columns must compose a unique or primary key constraint for the referenced table.
• In a table-level referential constraint, if multiple columns are specified, the columns specified for the referencing table must correspond in number, data type, and position to the columns specified for the referenced table, and must compose a unique or primary key constraint for the referenced table.
• If the referenced table is specified and the column name is omitted, the referenced table must have a primary key constraint; the referencing columns are verified against the primary key of the referenced table.