Referential Integrity


Referential integrity is a relational rule that requires foreign key values are either fully NULL or match some primary key value.

  • fully NULL means all columns are NULL
  • 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:
    1. A primary key is updated
    2. A foreign key is updated
    3. A row containing a primary key is deleted
    4. 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 RESTRICT for foreign key inserts and updates
  • MySQL supports all 4 actions for primary key updates and deletes
    • actions are specified in optional ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint
      • followed by one of the 4 actions
  • MySQL has limitations on primary key updates and deletes:
    • RESTRICT is applied when the ON UPDATE or ON DELETE clause is omitted
    • SET NULL cannot be used when a foreign key is not allowed NULL values
    • SET DEFAULT is 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
);