The Problem
As is well documented, SQLite does not support foreign key constraints. But I'm a stickler for maintaining relational integrity, and so did some research on how to add foreign key constraint functionality to my SQLite databases.
As I mentioned in my original blog post on this topic, I got most of the code from Cody Pisto's sqlite_fk utility. I couldn't get it to work, but the essential code for the triggers was in its fk.c file, so I just borrowed from that (public domain) code to figure it out.
Here I share with you the results of that research, as well as some improvements to my original implementation, thanks to comments in my blog.
The Solution
(There is an online tool which automatically generates the triggers proposed below based on the input CREATE TABLE statement.)
The trick to getting SQLite to enforce foreign key constraints is to use its marvelous trigger functionalilty. Here's an example. Say you have two table declarations:
create table foo (
id INTEGER NOT NULL PRIMARY KEY
);
CREATE TABLE bar (
id INTEGER NOT NULL PRIMARY KEY,
foo_id INTEGER NOT NULL
CONSTRAINT fk_foo_id REFERENCES foo(id) ON DELETE CASCADE
);
Table bar has a foreign key reference to the primary key column in the foo table. Although SQLite supports this syntax (as well as named foreign key constraints), it ignores them. So if you want the references enforced, you need to create triggers to do the job.
Triggers were added to SQLite version 2.5, so most users can take advantage of this feature. Each constraint must have three triggers: one for INSERTs, one for UPDATESs, and one for DELETESs. The INSERT trigger looks like this:
CREATE TRIGGER fki_bar_foo_id
BEFORE INSERT ON bar
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'insert on table "bar" violates foreign key constraint "fk_foo_id"')
WHERE (SELECT id FROM foo WHERE id = NEW.foo_id) IS NULL;
END;
If your foreign key column is not NOT NULL, the trigger's WHERE clause needs an extra expression:
CREATE TRIGGER fki_bar_foo_id
BEFORE INSERT ON bar
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'insert on table "bar" violates foreign key constraint "fk_foo_id"')
WHERE NEW.foo_id IS NOT NULL
AND (SELECT id FROM foo WHERE id = new.foo_id) IS NULL;
END;
The UPDATE statements are almost identical; if your foreign key column is NOT NULL, then do this:
CREATE TRIGGER fku_bar_foo_id
BEFORE UPDATE ON bar
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'update on table "bar" violates foreign key constraint "fk_foo_id"')
WHERE (SELECT id FROM foo WHERE id = NEW.foo_id) IS NULL;
END;
And if NULLs are allowed, do this:
CREATE TRIGGER fku_bar_foo_id
BEFORE UPDATE ON bar
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'update on table "bar" violates foreign key constraint "fk_foo_id"')
WHERE NEW.foo_id IS NOT NULL
AND (SELECT id FROM foo WHERE id = NEW.foo_id) IS NULL;
END;
The DELETE trigger is, of course, the reverse of the INSERT and UPDATE triggers, in that it applies to the primary key table, rather than the foreign key table. To wit, in our example, it watches for DELETEs on the foo table:
CREATE TRIGGER fkd_bar_foo_id
BEFORE DELETE ON foo
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'delete on table "foo" violates foreign key constraint "fk_foo_id"')
WHERE (SELECT foo_id FROM bar WHERE foo_id = OLD.id) IS NOT NULL;
END;
This trigger will prevent DELETEs on the foo table when there are existing foreign key references in the bar table. This is generally the default behavior in databases with referential integrity enforcement, sometimes specified explicitly as ON DELETE RESTRICT. But sometimes you want the deletes in the primary key table to cascade to the foreign key tables. Such is what our example declaration above specifies, and this is the trigger to do the job:
CREATE TRIGGER fkd_bar_foo_id
BEFORE DELETE ON foo
FOR EACH ROW BEGIN
DELETE from bar WHERE foo_id = OLD.id;
END;
Pretty simple, eh? The trigger support in SQLite is great for building your own referential integrity checks. Hopefully, these examples will get you started down the path of creating your own.