Referential Integrity in DBMS
In this lesson: Learn referential integrity with foreign keys, ON DELETE CASCADE, SET NULL and RESTRICT — MySQL examples with output.
What is referential integrity
Referential integrity is a rule that keeps linked tables consistent: a foreign key value must match an existing value in the table it references, or be null. This stops "orphan" rows that point to records which do not exist, so the relationships in your database always make sense.
The foreign key link
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
The foreign key orders.customer_id must match a real customers.id. The DBMS enforces this automatically.
What it prevents
-- customers has ids 1 and 2 only INSERT INTO orders VALUES (10, 99); -- ERROR: customer 99 does not exist
The DBMS rejects this insert because customer 99 has no matching row. Without referential integrity, you could create an order for a non-existent customer — an orphan row that corrupts the data.
On delete and update
What if you delete a customer who has orders? By default, the DBMS blocks the delete to protect integrity:
DELETE FROM customers WHERE id = 1; -- ERROR if orders reference id 1
You can control this behaviour with referential actions defined on the foreign key.
ON DELETE CASCADE
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE
With ON DELETE CASCADE, deleting a customer automatically deletes all their orders too. This keeps things consistent — no orphaned orders are left behind. Use it when child rows have no meaning without their parent.
Other referential actions
| Action | On deleting the parent |
|---|---|
| RESTRICT / NO ACTION | Block the delete (default) |
| CASCADE | Delete the child rows too |
| SET NULL | Set the child's foreign key to NULL |
Choose based on what should happen to child rows when a parent goes away.
Common mistakes
- Inserting a child before its parent. The parent row must exist first.
- Deleting a referenced parent without a plan. Decide CASCADE, SET NULL or RESTRICT.
- Mismatched column types. Foreign and referenced keys must share a type.
- Forgetting foreign keys entirely. Then nothing enforces integrity.
Summary
- Referential integrity keeps foreign keys pointing to real rows.
- The DBMS rejects inserts that reference non-existent parents.
- By default, deleting a referenced parent is blocked.
- ON DELETE CASCADE deletes child rows with the parent.
- SET NULL and RESTRICT are other referential actions.
Next, learn the keys behind it in primary key vs foreign key, or add rules with constraints.
