こんなのあったのか!
SQLiteには外部制約がありません。
一応 FOREIGN KEY REFERENCES を使えるけど、制約は働いてません。
ので、プログラム側で良いようにしてあげないとダメな訳です。
公式ページにはTriggerを使った制約の付け方が乗ってるんですが、実はTrigger使ったことないので何をやってるのかいまいち良く分からなくてみなかったことにしてたんですよね。
ところがこの間別件で公式ページをふらついてると凄いものを発見しました。
なんと、sqlite3 コマンドに自動でTriggerを作ってくれるドットコマンドが知らぬ間にできてたんです!
やってみました。
まず、次のSQL用意して、
CREATE TABLE A (a primary key, b integer, c integer); create table B (a, d integer UNIQUE, PRIMARY KEY(a, d), FOREIGN KEY(a) REFERENCES A); create TABLE C (a, d, FOREIGN KEY(a,d) REFERENCES B(a,d));
明らかに実験用です(w でもややこしいです。
で、
sqlite3 test.db < fkeytest.sql sqlite3 test.db SQLite version 3.6.15 Enter ".help" for instructions Enter SQL statements terminated with a ";"
SQLを食わせてsqlite3シェルに入ります。外部制約を一応付けたけど機能していないテーブルは既にできてます。
そこで.genfkeyコマンドを発行すると、、、
sqlite> .genfkey sql -- Triggers for foreign key mapping: -- -- B(a) REFERENCES A(a) -- on delete RESTRICT -- on update RESTRICT -- CREATE TRIGGER genfkey1_insert_referencing BEFORE INSERT ON "B" WHEN new."a" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "A" WHERE new."a" == "a") BEGIN SELECT RAISE(ABORT, 'constraint failed'); END; CREATE TRIGGER genfkey1_update_referencing BEFORE UPDATE OF a ON "B" WHEN new."a" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "A" WHERE new."a" == "a") BEGIN SELECT RAISE(ABORT, 'constraint failed'); END; CREATE TRIGGER genfkey1_delete_referenced BEFORE DELETE ON "A" WHEN EXISTS (SELECT 1 FROM "B" WHERE old."a" == "a") BEGIN SELECT RAISE(ABORT, 'constraint failed'); END; CREATE TRIGGER genfkey1_update_referenced AFTER UPDATE OF a ON "A" WHEN EXISTS (SELECT 1 FROM "B" WHERE old."a" == "a") BEGIN SELECT RAISE(ABORT, 'constraint failed'); END; -- Triggers for foreign key mapping: -- -- C(a, d) REFERENCES B(a, d) -- on delete RESTRICT -- on update RESTRICT -- CREATE TRIGGER genfkey2_insert_referencing BEFORE INSERT ON "C" WHEN new."a" IS NOT NULL AND new."d" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "B" WHERE new."a" == "a" AND new."d" == "d") BEGIN SELECT RAISE(ABORT, 'constraint failed'); END; CREATE TRIGGER genfkey2_update_referencing BEFORE UPDATE OF a, d ON "C" WHEN new."a" IS NOT NULL AND new."d" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "B" WHERE new."a" == "a" AND new."d" == "d") BEGIN SELECT RAISE(ABORT, 'constraint failed'); END; CREATE TRIGGER genfkey2_delete_referenced BEFORE DELETE ON "B" WHEN EXISTS (SELECT 1 FROM "C" WHERE old."a" == "a" AND old."d" == "d") BEGIN SELECT RAISE(ABORT, 'constraint failed'); END; CREATE TRIGGER genfkey2_update_referenced AFTER UPDATE OF a, d ON "B" WHEN EXISTS (SELECT 1 FROM "C" WHERE old."a" == "a" AND old."d" == "d") BEGIN SELECT RAISE(ABORT, 'constraint failed'); END; sqlite>
外部制約を実現するTriggerを作るSQLを生成してくれます!!
すげーーー!!!ちょーなげーー!!!よくわかんねーーー!!
.genfkey --exec
ってやれば、Trigger生成のSQLを発行してTriggerを作ってくれます。
あ、実際に制約が掛かってるか実験してないや(w