Definition
Referential integrity ensures that relationships between tables remain valid. A foreign key must either match an existing primary key value or be null.
Key Point
Referential Integrity Rules:
• Cannot insert a record with a foreign key that doesn't exist in the parent table
• Cannot delete a parent record if child records reference it
• Cannot update a primary key if foreign keys reference it
Enforcement:
Modern DBMSs enforce these rules automatically when foreign key constraints are defined.
• Cannot insert a record with a foreign key that doesn't exist in the parent table
• Cannot delete a parent record if child records reference it
• Cannot update a primary key if foreign keys reference it
Enforcement:
Modern DBMSs enforce these rules automatically when foreign key constraints are defined.
Worked Example
Referential Integrity Example
Tables:
• Department: DeptID (PK), DeptName
• Employee: EmpID (PK), Name, DeptID (FK)
Valid Operation:
Insert Employee with DeptID = 'D001' only if 'D001' exists in Department table
Invalid Operation:
Insert Employee with DeptID = 'D999' if 'D999' doesn't exist - would violate referential integrity
Cascading Actions:
• ON DELETE CASCADE: Delete child records when parent deleted
• ON UPDATE CASCADE: Update foreign keys when primary key changes
Tables:
• Department: DeptID (PK), DeptName
• Employee: EmpID (PK), Name, DeptID (FK)
Valid Operation:
Insert Employee with DeptID = 'D001' only if 'D001' exists in Department table
Invalid Operation:
Insert Employee with DeptID = 'D999' if 'D999' doesn't exist - would violate referential integrity
Cascading Actions:
• ON DELETE CASCADE: Delete child records when parent deleted
• ON UPDATE CASCADE: Update foreign keys when primary key changes