On DELETE CASCADE in sqlite3

sql

foreign-keys

database-design

sqlite

byAlex Kataev·Sep 15, 2024

To automatically delete related rows when the referenced row is removed, set ON DELETE CASCADE on a foreign key. Enable it with:

PRAGMA foreign_keys = ON; -- It's like flipping a switch, boom! ON CASCADE is ON.

And define it in your table creation:

CREATE TABLE child (
    ...
    FOREIGN KEY(parent_id) REFERENCES parent(id) ON DELETE CASCADE
); -- This feels like shouting: "Hey owner, you leave, your kids leave too!"

This safeguards your data integrity by ensuring child records are automatically deleted when their parent record is deleted.

Check and double-check

Are foreign keys playing ball?

SQLite by default is like an easy-going parent: it doesn't enforce foreign key constraints. Change this behavior by setting: PRAGMA foreign_keys = ON; at the start of each session!

Defining tables like a pro

When establishing your tables, make sure you're following correct syntax structure:

CREATE TABLE parent (
    id INTEGER PRIMARY KEY
);
CREATE TABLE child (
    id INTEGER PRIMARY KEY,
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
);

Always cross-check table structures to guarantee constraints are properly allocated. Dropping FOREIGN KEY declaration is a classic blunder, don't be that developer! 🙈

Cascading swings into action

After setting up, run deletion tests on the parent row:

DELETE FROM parent WHERE id = 1; -- "I AM YOUR FATHER... and now I'm not."

If your setup is correct, any children rows with parent_id = 1 should automatically join their parent in the void.

Triggers, your plan B

Having trouble with ON DELETE CASCADE, or need a more hands-on approach? Say hello to triggers:

CREATE TRIGGER delete_child AFTER DELETE ON parent
    FOR EACH ROW
    BEGIN
        DELETE FROM child WHERE parent_id = OLD.id; -- "No child left behind... or actually, ALL children left behind."
    END;

Troubleshooting

Stuck? Verify the following:

Each new session should start with PRAGMA foreign_keys = ON;. Remember this is not a permanent setting, you gotta repeat it like a mantra 🧘‍♂️.
Forgetting to add ON DELETE CASCADE in your foreign key declaration is like forgetting your umbrella for a rain dance! ☔️
Stuck with an older SQLite version that does not support cascading deletes? It's time for an upgrade!💡

End your troubleshooting by verifying cascading behavior. Write up test cases and learn from them!