_By Theory on 2005-12-01_

**The Problem**

As is {link: http://www.sqlite.org/omitted.html 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
{link: http://www.justatheory.com/computers/databases/sqlite/foreign_key_triggers.html blog post} on 
this topic, I got most of the code from Cody Pisto's 
{link: http://www.sqlite.org/contrib 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**

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 a(id) ON DELETE CASCADE
  );

Table <code>bar</code> has a foreign key reference to the primary key column in the <code>foo</
code> 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 <code>INSERT</code>s, one for <code>UPDATES</
code>s, and one for <code>DELETES</code>s. The <code>INSERT</code> 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 <code>NOT NULL</code>, the trigger's
<code>WHERE</code> clause needs to 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 <code>UPDATE</code> statements are almost identical; if your foreign key column is 
<code>NOT NULL</code>, 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 <code>NULL</code>s 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 <code>DELETE</code> trigger is, of course, the reverse of the <code>INSERT</code> and 
<code>UPDATE</code> triggers, in that it applies to the primary key table, rather than the foreign key 
table. To whit, in our example, it watches for <code>DELETE</code>s on the <code>foo</code> 
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 <code>DELETE</code>s on the <code>foo</code> table when there are 
existing foreign key references in the <code>bar</code> table. This is generally the default behavior 
in databases with referential integrity enforcement, sometimes specified explicitly as <code>ON 
DELETE RESTRICT</code>. But sometimes you want the deletes in the primary key table to 
<q>cascade</q> to the foreign key tables. Such is what our example declaration above specifies, and 
this is the trigger to to 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.