Course contentsShow
← Data Science
Lesson 1058 of 2,14527. Database Design and OptimizationFree lesson

Referential Integrity Violations

Common scenarios that violate referential integrity and how databases prevent or handle these errors.

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.