Referential Integrity Violations
You'll learn: How databases detect and respond when operations threaten to break foreign key relationships.
What Violates Referential Integrity?
Referential integrity violations occur when you try to perform an operation that would leave a foreign key pointing to a non-existent parent record—like having a library card that references a patron who was deleted from the system.
Common Violation Scenarios
1. Inserting Orphan Records
Trying to add a child record with a foreign key value that doesn't exist in the parent table:
-- orders references customers(customer_id)
INSERT INTO orders (customer_id, total) VALUES (9999, 50.00);
-- ERROR: customer_id 9999 doesn't exist
2. Deleting Referenced Parents
Attempting to delete a parent record that still has children pointing to it:
DELETE FROM customers WHERE customer_id = 5;
-- ERROR: orders still reference this customer
3. Updating Primary Keys Carelessly
Changing a primary key value when foreign keys still reference the old value creates orphans.
How Databases Prevent Violations
When you define a foreign key constraint, the database automatically rejects any INSERT, UPDATE, or DELETE that would violate referential integrity—unless you specify cascading actions (ON DELETE CASCADE, ON UPDATE CASCADE) or other handling (SET NULL, SET DEFAULT).
Key Takeaway: Referential integrity violations happen when you try to create orphaned child records or delete parents with existing children; foreign key constraints act as guardrails that block these operations unless you've explicitly defined how to handle them with cascading actions.