Right, so you’ve set up your tables, you’ve got your relationships, and you’ve slapped a FOREIGN KEY on there. Good for you. But now comes the fun part: what actually happens when you try to delete a record that other records depend on? If you just try to yank it out, the database will, quite rightly, throw a fit. It’s like trying to remove the bottom block from a Jenga tower—everything comes crashing down. That’s where ON DELETE comes in. It’s your rulebook for this exact scenario, and you get to decide how the game is played.

The default behavior, if you don’t specify anything, is often RESTRICT. But let’s be explicit, because guessing is for horoscopes, not database schemas.

The Bouncer: RESTRICT

This is the strictest and safest option. RESTRICT tells the database, “If anyone is referencing this record, do not let this deletion happen. Throw an error. Be a bouncer.”

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE RESTRICT
);

Now, try to delete an author who has books? Absolutely not. The operation is aborted with a loud, foreign key violation error. It’s the database’s way of forcing you to deal with the dependent records first (maybe by reassigning or deleting them manually). This is my go-to for most relationships because it prevents catastrophic, accidental data loss. You have to be intentional.

The Bomb Squad: CASCADE

CASCADE is the “hold my beer” of database operations. It says, “If you delete this record, automatically delete every single record that references it.” It’s efficient, it’s brutal, and if you’re not careful, it will obliterate your data in a silent, cascading chain reaction.

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    post_id INTEGER NOT NULL,
    FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE
);

Delete a blog post? Poof. All its comments are gone, instantly. No error, no warning. This is incredibly useful for true composition relationships—where the child record has no meaning without the parent (like an order and its line items). The massive pitfall? The cascade can go further than you think. If another table had a foreign key to comments also with ON DELETE CASCADE, those would get wiped out too. Always, always know the full depth of your relationships before using this.

The Ghost Town: SET NULL

This one is a bit more philosophical. SET NULL doesn’t delete the dependent records; instead, it sets their foreign key column to NULL. The record becomes an orphan, floating in the database without a parent.

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
);

If you delete the ‘Engineering’ department, every employee’s department_id becomes NULL. They’re still employed, but they just don’t have a department anymore. This only works if your foreign key column is nullable, which this one is. The obvious gotcha? Your application logic must be prepared to handle these null values gracefully. If your UI always expects a department name, it’s going to break.

The Fallback Plan: SET DEFAULT

This is the option everyone thinks is a good idea until they actually try to use it. SET DEFAULT tries to set the foreign key column to its defined DEFAULT value when the parent is deleted.

CREATE TABLE order_statuses (
    id SERIAL PRIMARY KEY,
    status TEXT NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status_id INTEGER NOT NULL DEFAULT 1, -- assuming 1 is 'pending'
    FOREIGN KEY (status_id) REFERENCES order_statuses(id) ON DELETE SET DEFAULT
);

Seems clever, right? Delete an order_status and the order falls back to ‘pending’. Here’s the brutal reality: it will only work if the default value (1) exists as a valid primary key in the order_statuses table at the moment of deletion. If you’ve also deleted the ‘pending’ status (id 1), the entire operation fails. This creates a hidden, temporal dependency that makes your data integrity surprisingly fragile. I almost never use this in production. It’s more trouble than it’s worth.

The Bottom Line: Your choice here is a business logic decision, not just a technical one. RESTRICT for “must handle manually,” CASCADE for “true composition,” SET NULL for “optional relationships,” and avoid SET DEFAULT unless you have a very, very specific and controlled use case. And for the love of all that is holy, never let your framework choose for you. Think it through.