*** 3,43 ****
**The Problem**
As is {link: http://www.sqlite.org/omitted.html well documented}, SQLite does not support foreign key
! constraints. Buus: 200 OK
! ETag: 61fe3da8cb535412892d5a9bd2584ab5
! Cache-control: public
! Content-Type: text/html; charset=ANSI_X3.4-1968
! Content-Length: 3348
!
! <html><head>
! <style type="text/css"><!--
! .border1 {
! background: #80a796
! }
! .bkgnd1 {
! background: #f0ffff;
! color: #000000;
! }
! --></style>
! <title>SQLite CVSTrac</title></head>
! <body bgcolor="white" link="#50695f" vlink="#508896">
! <table width="100%" border="0">
! <tr><td valign="top"><a href="/index.html">
! <img src="/sqlite.gif" border="none"></a></td>
! <td width="100%"></td>
! <td valign="bottom">
! <ul>
! <li><a href="http://www.sqlite.org/cvstrac/tktnew">bugs</a></li>
! <li><a href="/changes.html">changes</a></li>
! <li><a href="/contrib">contrib</a></li>
! <li><a href="/download.html#cvs">cvs repository</a></li>
! <li><a href="/docs.html">documentation</a></li>
!
! </ul>
! </td>
! <td width="10"></td>
! <td valign="bottom">
! <ul>
! <li><a href="/download.html">download</a></li>
! <li><a href="/faq.html">faq</a></li>
! <li><a href="/index.
--- 3,105 ----
**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 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 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 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 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 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 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.
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|