I want to know what are the different referential actions supported by FOREIGN KEY integerity constraint. Please let me know if anyone knows the answer.
Answer by KillerDBA ·
From a book:
"Foreign key constraints check whether a column value is found in a list of values in a column designated as a primary key column in the same or a different table."
From a practical perspective, FK's:
Ensure that a record relates back to another table in a predefined way. Think parent-child relationship but it's a little more flexible. Each "child" has a valid "parent." For every department, the employees in it are required to be listed in the employee table.
Ensure that certain records can't be deleted if they are still referenced. A "parent" can't be deleted unless asll the children are also deleted. An employee can't be deleted if he's still part of a department. A payroll record can't be deleted if there's a check written for it.
The foreign key doesn't have to be defined on a single field. Let's say we had payroll records and checks. The payroll record might be primary keyed by the employee number and pay period id. The check might have a primary key on the check number but also a foreign key relationship on the emploeey number and pay period id fields.
Let's say you have project records... you might have multiple foreign keys back to the employee table for different roles in the project... project manager, project champion and maybe project sacrificial goat. These could all be foreign keyed, individually, back to the employee table. Note, you don't have to assign the project sacrificial goat until you relly need one, nulls are usually permitted in foreigh key fields but, when assigned, must have a corresponding record in the referenced table. Each of the foreign key relationships is assessed individually.
There's also a CASCADE function on foreign keys. If you delete the referenced (parent) record, all the referencing (children) records can be deleted, too. That strikes me as a little bit dangerous.