Referential Integrity
Referential integrity is a relational rule that requires foreign key values are either fully NULL or match some primary key value.
- fully
NULLmeans all columns areNULL - cannot be partially
NULL - relational databases must always obey referential integrity
- prevents bad or broken data and contributes to quality data
Referential Integrity Violations
- 4 ways to violate referential integrity:
- A primary key is updated
- A foreign key is updated
- A row containing a primary key is deleted
- A row containing a foreign key is inserted
- primary key inserts and foreign key deletes never violate referential integrity
Referential Integrity Actions
- referential integrity violations can be corrected manually or automatically
- databases automatically correct referential integrity violations in 4 ways:
- RESTRICT rejects an insert, update, or delete that violates referential integrity
- SET NULL sets invalid foreign keys to
NULL - SET DEFAULT sets invalid foreign keys to the foreign key default value
- CASCADE propagates primary key changes to foreign keys
- if a primary key is deleted, rows containing matching foreign keys are deleted
- if a primary key is updated, matching foreign keys are updated to the same value
ON UPDATE and ON DELETE Clauses
- MySQL only supports
RESTRICTfor foreign key inserts and updates - MySQL supports all 4 actions for primary key updates and deletes
- actions are specified in optional
ON UPDATEandON DELETEclauses of theFOREIGN KEYconstraint- followed by one of the 4 actions
- actions are specified in optional
- MySQL has limitations on primary key updates and deletes:
RESTRICTis applied when theON UPDATEorON DELETEclause is omittedSET NULLcannot be used when a foreign key is not allowedNULLvaluesSET DEFAULTis not supported in some MySQL configurations
Exmaple
CREATE TABLE Department ( Code TINYINT UNSIGNED, Name VARCHAR(20), ManagerID SMALLINT UNSIGNED, PRIMARY KEY (Code), FOREIGN KEY (ManagerID) REFERENCES Employee(ID) ON DELETE SET NULL ON UPDATE CASCADE );