SQLite On Delete Callbacks

Leverage the built-in callback handlers of sqlite to ensure that no dangling foreign keys or orphan records exist in your database. Pure SQL solution.

---

Assuming we have the following table:

CREATE TABLE IF NOT EXISTS blog_posts (
    id INTEGER PRIMARY KEY NOT NULL,
    title TEXT NOT NULL,
    html TEXT NOT NULL,
    parent_blog_post_id INTEGER,
		FOREIGN KEY(parent_blog_post_id) REFERENCES blog_posts(id) ON DELETE SET NULL,
    inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

then the parent_blog_id field can be NULL, or point to some other blog post. Now with the ON DELETE SET NULL bit on the foreign key declaration, if the linked blog post gets deleted, this field will be set to NULL. automatically instead of pointing to a now non-existent record.

Let`s assume we have even more tables that model a n:m relation:

CREATE TABLE IF NOT EXISTS blog_categories (
    id INTEGER PRIMARY KEY NOT NULL,
    title TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS blog_posts_categories (
    blog_post_id INTEGER NOT NULL,
    blog_category_id INTEGER NOT NULL,
    FOREIGN KEY(blog_post_id) REFERENCES blog_posts(id) ON DELETE CASCADE,
    FOREIGN KEY(blog_category_id) REFERENCES blog_categories(id) ON DELETE CASCADE,
    UNIQUE(blog_post_id, blog_category_id) ON CONFLICT REPLACE
);

here the two tables blog_posts and blog_categories don't know about each other, so there is way to trigger an ON DELETE hook when one of them get deleted.

But! the blog_posts_categories table is a join table between the two others and the existence of a record with the two foreign keys means there is a link. And when one side gets deleted, it doesn't make sense to keep any record in the join table around, therefore ON DELETE CASCADE is the answer: it will propagate the deletion of the foreign record to this table, and delete all associated records.

And thats it! No need to write custom application code to keep things in order (which might fail anyways due to bugs).