Referential Integrity and ON DELETE Actions

Objectives

By the end of this lesson, you should be able to:

• Explain what referential integrity means
• Choose between CASCADE, SET NULL, and RESTRICT for ON DELETE
• Predict what happens to child rows when a parent row is deleted, under each option

💡 Why this matters: Deleting a department that still has employees pointing at it would leave those employees referencing something that no longer exists, unless PostgreSQL is told exactly what to do about it. ON DELETE makes that choice explicit instead of leaving it undefined.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Referential Integrity

Referential integrity is the guarantee that every foreign key value actually matches a real row in the referenced table, no employee ever points at a department that doesn’t exist. Every foreign key from this module has been enforcing referential integrity all along, on insert. This lesson covers the other side: what happens when the referenced row itself is deleted or changed.

The Default: RESTRICT

CREATE TABLE dept2 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE emp_restrict (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    dept_id INTEGER REFERENCES dept2(id) ON DELETE RESTRICT
);

INSERT INTO dept2 (name) VALUES ('Temp Dept');
INSERT INTO emp_restrict (name, dept_id) VALUES ('Restrict Person', 1);

DELETE FROM dept2 WHERE id = 1;

ERROR:  update or delete on table "dept2" violates RESTRICT setting of foreign key constraint "emp_restrict_dept_id_fkey" on table "emp_restrict"

ON DELETE RESTRICT blocks the delete entirely, as long as any row still references it. This is also PostgreSQL’s default behavior if ON DELETE isn’t specified at all, referential integrity is protected by refusing the delete rather than leaving orphaned data behind.

ON DELETE CASCADE

CREATE TABLE emp_cascade (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    dept_id INTEGER REFERENCES dept2(id) ON DELETE CASCADE
);

INSERT INTO emp_cascade (name, dept_id) VALUES ('Cascade Person', 1);

DELETE FROM dept2 WHERE id = 1;
SELECT * FROM emp_cascade;

(0 rows)

ON DELETE CASCADE deletes the child row automatically when its parent is deleted, Cascade Person disappeared along with the department. This is powerful and dangerous in equal measure, appropriate when child rows genuinely have no meaning without their parent (an order’s line items, once the order itself is deleted), risky when they represent something that should survive independently.

ON DELETE SET NULL

CREATE TABLE emp_setnull (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    dept_id INTEGER REFERENCES dept2(id) ON DELETE SET NULL
);

INSERT INTO emp_setnull (name, dept_id) VALUES ('SetNull Person', 1);

DELETE FROM dept2 WHERE id = 1;
SELECT * FROM emp_setnull;

 id |      name       | dept_id
----+------------------+---------
  1 | SetNull Person  |

ON DELETE SET NULL keeps the child row, but resets its foreign key column to NULL. SetNull Person still exists, no longer tied to any department, exactly the shape Devon Ellery’s NULL department (Module 6) represented, “no department” being valid data. SET NULL only works if the foreign key column allows NULL, it can’t be combined with NOT NULL on that column.

Choosing Between Them

RESTRICT (or the plain default) fits when a deletion should require cleaning up related data first, a deliberate, safe default. CASCADE fits when related rows are genuinely part of the parent and meaningless without it. SET NULL fits when related rows should survive independently, just losing that particular connection. The same three options apply to ON UPDATE, covering what happens if a referenced key’s value itself changes rather than being deleted, though this comes up far less often in practice since primary keys rarely change.

Try It

1. Create a authors table and a books table with author_id ON DELETE CASCADE, insert an author and two books, delete the author, and confirm both books are gone.
2. Repeat the same setup with ON DELETE SET NULL instead, and confirm the books survive with author_id reset to NULL.
3. Repeat once more with ON DELETE RESTRICT (or no ON DELETE at all), and confirm the author can’t be deleted while books still reference them.
4. Explain, in your own words, which of the three options you’d choose for an order_items table referencing orders, and why.

Recap

• Referential integrity guarantees every foreign key value matches a real row, ON DELETE decides what happens when that row is removed.
• RESTRICT (the default) blocks the delete while references exist.
• CASCADE deletes dependent rows automatically, SET NULL keeps them but clears the foreign key column.
• The same three options apply to ON UPDATE, for when a referenced key’s value changes instead of being deleted.

Next lesson: this module’s exercises, designing related tables and testing referential integrity for yourself.