Enforce Foreign Key Integrity in SQLite with Triggers

After some some Googling and experimentation, I've figured out how to enforce foreign key constraints in SQLite. 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.

Since I couldn't find documentation for this elsewhere on the Net (though I'm sure it exists somewhere), I decided to just put an example here. Interested? Read on!

Say you have these 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 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 CASE
     WHEN ((SELECT id FROM foo WHERE id = NEW.foo_id) IS NULL)
     THEN RAISE(ABORT, 'insert on table "bar" violates foreign key '
                || 'constraint "fk_foo_id"')
  END;
END;

(You can put the RAISE error string all on one line; I've concatenated two lines to keep line lengths reasonable here.) If your foreign key column is not NOT NULL, the trigger's SELECT CASE clause needs to an extra case:

CREATE TRIGGER fki_bar_foo_id
BEFORE INSERT ON bar
FOR EACH ROW BEGIN 
   SELECT CASE
     WHEN ((new.foo_id IS NOT NULL)
           AND ((SELECT id FROM foo WHERE id = new.foo_id) IS NULL))
     THEN RAISE(ABORT, 'insert on table "bar" violates foreign key '
                || 'constraint "fk_foo_id"')
  END;
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 CASE
     WHEN ((SELECT id FROM foo WHERE id = new.foo_id) IS NULL))
     THEN RAISE(ABORT, 'update on table "bar" violates foreign key '
                || 'constraint "fk_foo_id"')
  END;
END;

And if NULLs are allowed, do this:

CREATE TRIGGER fku_bar_foo_id
BEFORE UPDATE ON bar
FOR EACH ROW BEGIN 
   SELECT CASE
     WHEN ((new.foo_id IS NOT NULL)
           AND ((SELECT id FROM foo WHERE id = new.foo_id) IS NULL))
     THEN RAISE(ABORT, 'update on table "bar" violates foreign key '
                || 'constraint "fk_foo_id"')
  END;
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 whit, 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 CASE
    WHEN ((SELECT foo_id FROM bar WHERE foo_id = OLD.id) IS NOT NULL)
    THEN RAISE(ABORT, 'delete on table "foo" violates foreign key '
               || ' constraint "fk_foo_id"')
  END;
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 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.

Backtalk

Witold Czarnecki wrote:

Another way

Little shorter syntax:

CREATE TRIGGER fki_bar_foo_id
BEFORE INSERT ON bar
FOR EACH ROW BEGIN 
  SELECT RAISE(ABORT, 'Blah-blah') 
  WHERE  (SELECT id FROM foo WHERE id = NEW.foo_id) IS NULL;
END;

James wrote:

foreign keys with SQLite

Hi there

I tried your code but get this error on the command line:

SQL error: near "on": syntax error

I suspect it is something to do with this SQL command

CREATE TRIGGER fki_bar_foo_id
	BEFORE INSERT ON PCT_details
	FOR EACH ROW BEGIN 
		SELECT RAISE(ABORT, ‘insert on table "PCT_details" violates foreign key’)
		WHERE (SELECT Client_id FROM Client_details WHERE Client_id = NEW.Client_id) IS NULL;
	END;

any ideaS? please.

Theory wrote:

Re: foreign keys with SQLite

James

Change those curly single quotation marks around the error to the straight single quotation mark and you'll be in business.

—Theory

sql monkey wrote:

Does not work for me. Does it work for you, or are you just assuming it works? I have working triggers that create timestamps and even check for duplicate entries in tables, but my foreign key triggers won't work. I've been banging my head agaist a wall for 2 weeks on this... any help appreciated.

Theory wrote:

Re: Doesn't Work

sql monkey

Yes, they work for me. Do you have a link to your SQL? And what version of SQLite are you using?

—Theory

sql monkey wrote:

sqlite version I am using is 2.8.16 I don't have a link but:

CREATE TABLE picture (
        pictureid INTEGER PRIMARY KEY,
        filename TEXT NOT NULL ON CONFLICT ROLLBACK,
	parentdirid INTEGER NOT NULL ON CONFLICT ROLLBACK,
	parentdirprefixid INTEGER NOT NULL ON CONFLICT ROLLBACK,
	md5sum TEXT NOT NULL ON CONFLICT ROLLBACK,
        countryid INTEGER,
	regionid INTEGER,
        stateid INTEGER,
        cityid INTEGER,
        captionid INTEGER,
        descriptionid INTEGER,
        matesid INTEGER,
        expodateid INTEGER,
        photographerid INTEGER,
        imagingdeviceid INTEGER,
        rotateid INTEGER,
        gammaid INTEGER,
        timestamp TEXT);

CREATE TABLE description (
        descriptionid INTEGER PRIMARY KEY,
	description TEXT NOT NULL UNIQUE);

CREATE TABLE SlideshowPicture (
        slideshowid INTEGER NOT NULL,
	pictureid INTEGER NOT NULL,
	ordernumber INTEGER,
        timestamp TEXT);
CREATE TRIGGER SlideshowPictureContraint
 BEFORE INSERT ON SlideshowPicture
  BEGIN
   SELECT CASE WHEN foo.a > 0 THEN RAISE(ROLLBACK, 'Any given picture can only be in a slideshow once')
    ELSE 1 END FROM (SELECT COUNT(*) AS a FROM SlideshowPicture where new.slideshowid = SlideshowPicture.slideshowid AND new.pictureid = SlideshowPictureid) AS foo;
END;

-- the above trigger works
-- various attempts at foreign key triggers (only difference is these involve more than one table) do not work, eg. (these two fail for every insert on picture, whether the foreign key is valid or not):
--FOR EACH ROW WHEN new.descriptionid IS NOT NULL
--BEGIN
--SELECT CASE
--WHEN ((SELECT description.descriptionid FROM description, picture WHERE description.descriptionid = NEW.descriptionid) IS NULL)
--  THEN RAISE(ROLLBACK, 'insert on table picture references a descriptionid that does not exist')
--END;
--END;
        

-- BORKED: also stops valid inserts
--CREATE TRIGGER insert_description AFTER INSERT on picture
--FOR EACH ROW BEGIN
--SELECT CASE
-- WHEN ((new.descriptionid IS NOT NULL)
--     AND ((SELECT descriptionid FROM description WHERE descriptionid = NEW.descriptionid) IS NULL))
-- THEN RAISE(ROLLBACK, 'insert on table picture references a descriptionid that does not exist')
--END;
--END;

sql monkey wrote:

whoa, no formatting?...

sql monkey wrote:

FYI, I had the same errors trying sqlite 3.latest_greatest The key difference between triggers that work and those that don't is the failing ones involve more than one table.

Theory wrote:

Re: Doesn't Work

I added a bit of formatting for you. I should add a note to the comment box so that people know that they can use some HTML.

Anyway, it may be that it doesn't work in SQLite 2. I dunno, I never tried. I've only been using SQLite 3.0 and later.

—Theory

Theory wrote:

Re: Doesn't Work

I think you need a JOIN clause or a WHERE clause in your SELECT statement. This works for me:

CREATE TRIGGER insert_description AFTER INSERT on picture
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'whoops')
WHERE  NEW.descriptionid IS NOT NULL
       AND ((SELECT descriptionid
             FROM   description
	     WHERE  descriptionid = NEW.descriptionid)
	    IS NULL);
END;

Dunno about the other one, but you might want to consider ugins a UNIQUE index instead of that nasty hack to catch duplicate entries.

—Theory

sql monkey wrote:

Holy smokes! It works! (Your code above).

SQL monkey loves you.

Well, now I'll have to figure out what exactly I was doing wrong... (why it works) I had tried 100s of permutations, believe me. Ridiculous.

Re nasty hack, the reason for the trigger is that I can NOT use UNIQUE (this is a table that stands between a many to many- "junction table"- is that the term? Anyways.

sql monkey wrote:

hmmm... wait a sec. Are you suggesting that if one can not make a column unique, there is a way to constrain two columns (i.e. pairs) to be unique via an index?

Theory wrote:

Multicolumn Unique Index

CREATE INDEX idx_foo ON foo(bar_id, bid_id);

—Theory

sql monkey wrote:

Can an insert affect an existing row, or does it only add rows (if so, UPDATE is the only choice to modify an existing row- assuming you don't delete and somehow readd)?

Theory wrote:

Re: INSERT

An INSERT can only add a new row. It cannot affect an existing row unless a RULE changes the INSERT statement to something else or unless it sets off a TRIGGER that affects another row.

But I think you might want to start posting to pgsql-general for these kinds of questions. My blog isn't really a support forum.

—Theory

sql monkey wrote:

Thanks. You can delete the (off topic or all) entries I made... I won't get offended.

Lloyd wrote:

and what about?

what if I want the trigger to create the related line for me? I'm new to SQLite and trying to do this....

Theory wrote:

Re: and what about?

Lloyd,

I don't know what you mean. Can you explain it to me in more detail? Also, you can ask on the SQLite mail list.

—Theory

Chris Smith wrote:

Brilliant

Thanks for this post. You should consider adding it to the SQLite wiki.

Theory wrote:

Re: Brilliant

Chris,

Good call. Done.

—Theory

Randy Syring wrote:

CASCADE UPDATE OF PK

I am trying to get a cascade update trigger to work. IE, if your parent table's primary key gets update (naughty, naughty) it cascades that update to the foreign key so that referential integrity is not lost.

Here is my code:


CREATE TRIGGER fkuc_smvcModClient_id_smvcModClientSearchProjects_clientId
BEFORE UPDATE OF id ON smvcModClient
FOR EACH ROW BEGIN 
    UPDATE smvcModClientSearchProjects SET clientId = NEW.id 
    WHERE smvcModClientSearchProjects.clientId = OLD.id;
END;

The problem is that when this fires, my previous update constraint gives me an error:

CREATE TRIGGER fku_smvcModClientSearchProjects_clientId_smvcModClient_id
BEFORE UPDATE ON [smvcModClientSearchProjects] FOR EACH ROW 
  BEGIN 
  SELECT CASE
     WHEN ((SELECT id 
             FROM smvcModClient 
             WHERE id = NEW.clientId) 
         IS NULL)
     THEN RAISE(ABORT, 'update on table "smvcModClientSearchProjects" violates foreign key constraint "fku_smvcModClientSearchProjects_clientId_smvcModClient_id"')
     END;
END;

So, it would seem that the first query I posted is trying to update clientId with value that is not a valid ID in the smvcModClient table. However, I can't seem to figure out why and I am not sure how to debug triggers.

Any Ideas?

Theory wrote:

Re: CASCADE UPDATE OF PK

Randy

Uh, don't change primary keys! If you have an application that is likely to do that, use a surrogate key, instead.

The problem is that your foreign key constraint on smvcModClientSearchProjects is enforcing the constraint, of course. If you must cascade updates to primary keys, change fkuc_smvcModClient_id_smvcModClientSearchProjects_clientId to AFTER UPDATE OF id.

—Theory

chethana wrote:

about commit

i created a table but after creating i had to log out for some reason. giving commit doesn't work. i heard about sqlite using autocommit mode & thought it'll save automatically. but later when i gave .tables it showed nothing,it was empty. how do i save my tables (or commit) in sqlite?

pls do reply soon, awaiting reply.

thank u theory.

Theory wrote:

Re: about commit

chethana

I don't know. Try making your first statement BEGIN, create your table, and then COMMIT. That will be faster, too.

Other than that, I have no suggestions. But then this is not an SQLite support forum. I suggest that you post a query on the SQLite Users mail list.

—Theory

Randy Syring wrote:

Trigger Generator

I have created an online tool to generate the triggers based on a CREATE TABLE with column definitioans that have a CONSTRAINT just like your example tables. I use this method a lot and this saves me time and takes the guesswork out of getting the SQL right.

Also, FYI, concerning my previous comment on CASCADE UPDATE OF PK. Turns out that this is supported in later versions of 3.x but not in 2.x. The SQL is pretty simple, but the 2.x version just didn't support it. However, I agree that you should not be updating your primary keys!

Theory wrote:

Re: Trigger Generator

Randy,

Very cool! You might want to adopt the alternate sytnax suggested by Witold, however. I find it much more readable, and I think that I used it in the Wiki entry.

—Theory

Randy Syring wrote:

Re: Trigger Generator

Theory,

Thanks for the suggestion, I hadn't caught the updated syntax. The online generator now uses the syntax suggested by Witold.

Russell wrote:

Code looked a little wrong to me:

Here is the correct version :)

FK example:

CREATE TABLE dept 
(
  dept_id INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE emp 
(
  emp_id INTEGER NOT NULL PRIMARY KEY,
  dept_id INTEGER NOT NULL
         CONSTRAINT fk_dept_id REFERENCES dept(dept_id) ON DELETE CASCADE
);

Table emp has a foreign key reference to the primary key column in the dept 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 emp_bi
BEFORE INSERT ON emp
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT dept_id 
              FROM dept 
             WHERE dept_id = NEW.dept_id
           ) IS NULL
          )
     THEN RAISE(ABORT, ‘insert on table "emp" violates foreign key constraint "fk_dept_id"’)
  END;
END;


CREATE TRIGGER emp_bu
BEFORE UPDATE ON emp
FOR EACH ROW BEGIN 
   SELECT CASE
     WHEN ((SELECT dept_id 
              FROM dept 
             WHERE dept_id = new.dept_id
           ) IS NULL
          )
     THEN RAISE(ABORT, ‘update on table "emp" violates foreign key constraint "fk_dept_id"’)
  END;
END;

This trigger will prevent DELETEs on the dept table when there are existing foreign key references in the emp table.

CREATE TRIGGER dept_bd
BEFORE DELETE ON dept
FOR EACH ROW BEGIN 
  SELECT CASE
    WHEN ((SELECT dept_id 
             FROM emp 
            WHERE dept_id = OLD.dept_id
           ) IS NOT NULL
         )
    THEN RAISE(ABORT, ‘delete on table "dept" violates foreign key constraint "fk_dept_id"’)
  END;
END;



CREATE TRIGGER emp_ad
AFTER DELETE ON emp
FOR EACH ROW BEGIN 
    DELETE from dept WHERE dept_id = OLD.dept_id;
END;

Tests

Insert into dept values(1);
Insert into dept values(2);
Insert into dept values(3);

insert into emp values(1,1);
sqlite> insert into emp values(1,5);
SQL error: insert on table "emp" violates foreign key constraint "fk_dept_id"

UPDATE emp 
   SET dept_id = 6 
 WHERE emp_id = 1
;

SQL error: update on table "emp" violates foreign key constraint "fk_dept_id"
sqlite>


DELETE 
  FROM dept
 WHERE dept_id = 1
;

SQL error: delete on table "dept" violates foreign key constraint "fk_dept_id"
sqlite>

DELETE 
  FROM emp
 WHERE dept_id = 1
;

select * from dept;  --Row 1 deleted

Theory wrote:

Russell

Your code looks the same as my original example; am I missing something?

Also, you might be interested in Witold Czarnecki's shorter syntax, above, and as documented in the

Perry Valdez wrote:

Simpler trigger code for foreign key constraints

I've created a set of trigger code that is simpler IMHO than most of the code posted here but fully implements a foreign key constraint. I tested my code in SQLite version 3.3.13.

Here's my code plus some descriptions:

Scenario: A department has many employees. An employee belongs to only one department.

Database constraint: When an SQL insert, delete or update command violates a foreign key constraint, the command is cancelled.

Tables:

create table departments (
    dept_id   integer  not null primary key autoincrement,
    dept_name text not null unique
);

create table employees (
    emp_id   integer  not null primary key autoincrement,
    emp_name text     not null unique,
    dept_id  integer  not null,
    constraint fkey_dept_id foreign key (dept_id) references departments (dept_id)
);

The employees table has the foreign key constraint fkey_dept_id. Here's the trigger code for implementing the constraint:

create trigger fkey_dept_id_ins before insert on employees
  for each row
     when ((select dept_id from departments where dept_id = new.dept_id) is null)
       begin
          select raise(abort, 'insert on table "employees" violates foreign key constraint "fkey_dept_id"');
       end;

create trigger fkey_dept_id_upd before update on employees
  for each row
     when ((select dept_id from departments where dept_id = new.dept_id) is null)
       begin
          select raise(abort, 'update on table "employees" violates foreign key constraint "fkey_dept_id"');
       end;

create trigger fkey_dept_id_del before delete on departments
  for each row
     when ((select dept_id from employees where dept_id = old.dept_id) is not null)
       begin
          select raise(abort, 'delete on table "departments" violates foreign key constraint "fkey_dept_id" on "employees"');
       end;

And here are the tests I made inside the sqlite3 environment:

sqlite> insert into departments (dept_name) values ('Marketing');
sqlite> insert into departments (dept_name) values ('MIS');
sqlite> insert into departments (dept_name) values ('Accounting');
sqlite> select * from departments;
1|Marketing
2|MIS
3|Accounting
sqlite> insert into employees (emp_name, dept_id) values ('John', 1);
sqlite> insert into employees (emp_name, dept_id) values ('Tim', 2);
sqlite> insert into employees (emp_name, dept_id) values ('Gene', 5);
SQL error: insert on table "employees" violates foreign key constraint "fkey_dept_id"
sqlite> select emp_id, emp_name, dept_id from employees;
1|John|1
2|Tim|2
sqlite> update employees set dept_id = 3 where emp_id = 1;
sqlite> update employees set dept_id = 5 where emp_id = 2;
SQL error: update on table "employees" violates foreign key constraint "fkey_dept_id"
sqlite> select emp_id, emp_name, dept_id from employees;
1|John|3
2|Tim|2
sqlite> delete from departments where dept_id = 1;
sqlite> delete from departments where dept_id = 2;
SQL error: delete on table "departments" violates foreign key constraint "fkey_dept_id" on "employees"
sqlite> select * from departments;
2|MIS
3|Accounting
sqlite>

Perry Valdez wrote:

A much simpler trigger code for foreign key constraints

Here's another set of code plus a repeat of the descriptions from my previous post, but this time using Witold Czarnecki's syntax:

Scenario: A department has many employees. An employee belongs to only one department.

Database constraint: When an SQL insert, delete or update command violates a foreign key constraint, the command is cancelled.

Tables:

create table departments (
    dept_id   integer  not null primary key autoincrement,
    dept_name text not null unique
);

create table employees (
    emp_id   integer  not null primary key autoincrement,
    emp_name text     not null unique,
    dept_id  integer  not null,
    constraint fkey_dept_id foreign key (dept_id) references departments (dept_id)
);

Again, the employees table has the foreign key constraint fkey_dept_id. Here's the trigger code using Witold Czarnecki's syntax:

create trigger fkey_dept_id_ins before insert on employees
  for each row
    begin
      select
        raise(abort, 
             'insert on table "employees" violates foreign key constraint "fkey_dept_id"')
          where (select dept_id from departments 
                   where dept_id = new.dept_id) is null;
    end;

create trigger fkey_dept_id_upd before update on employees
  for each row
    begin
      select
        raise(abort, 
             'update on table "employees" violates foreign key constraint "fkey_dept_id"')
          where (select dept_id from departments 
                   where dept_id = new.dept_id) is null;
    end;

create trigger fkey_dept_id_del before delete on departments
  for each row
    begin
      select
        raise(abort,
             'delete on table "departments" violates foreign key constraint "fkey_dept_id" on "employees"')
          where (select dept_id from employees 
                   where dept_id = old.dept_id) is not null;
    end;

And again, here are the tests I made inside the sqlite3 environment:

sqlite> insert into departments (dept_name) values ('Marketing');
sqlite> insert into departments (dept_name) values ('MIS');
sqlite> insert into departments (dept_name) values ('Accounting');
sqlite> select * from departments;
1|Marketing
2|MIS
3|Accounting
sqlite> insert into employees (emp_name, dept_id) values ('John', 1);
sqlite> insert into employees (emp_name, dept_id) values ('Tim', 2);
sqlite> insert into employees (emp_name, dept_id) values ('Gene', 5);
SQL error: insert on table "employees" violates foreign key constraint "fkey_dept_id"
sqlite> select emp_id, emp_name, dept_id from employees;
1|John|1
2|Tim|2
sqlite> update employees set dept_id = 3 where emp_id = 1;
sqlite> update employees set dept_id = 5 where emp_id = 2;
SQL error: update on table "employees" violates foreign key constraint "fkey_dept_id"
sqlite> select emp_id, emp_name, dept_id from employees;
1|John|3
2|Tim|2
sqlite> delete from departments where dept_id = 1;
sqlite> delete from departments where dept_id = 2;
SQL error: delete on table "departments" violates foreign key constraint "fkey_dept_id" on "employees"
sqlite> select * from departments;
2|MIS
3|Accounting
sqlite>