I found the (apparent) problem. This works and throws the constraint violation: PRAGMA foreign_keys=OFF; CREATE TABLE staff_01( id INTEGER PRIMARY KEY AUTOINCREMENT, id_staff_editor INTEGER NOT NULL, code CHAR(2) NOT NULL, FOREIGN KEY(id_staff_editor) REFERENCES staff(id) ); INSERT INTO "staff_01" VALUES(1,1,'CB'); CREATE TABLE staff_02( id INTEGER PRIMARY KEY AUTOINCREMENT, id_staff_editor INTEGER NOT NULL, code CHAR(2) NOT NULL, FOREIGN KEY(id_staff_editor) REFERENCES staff_01(id) ); pragma foreign_keys=ON; pragma foreign_keys; INSERT INTO "staff_02" VALUES(1,1000,'CB'); INSERT INTO "staff_02" VALUES(2,1,'CB'); sqlite> pragma foreign_keys=ON; sqlite> pragma foreign_keys; 1 sqlite> INSERT INTO "staff_02" VALUES(1,1000,'CB'); Error: foreign key constraint failed sqlite> INSERT INTO "staff_02" VALUES(2,1,'CB'); sqlite>
But put it inside a transaction (like from .dump or the examples that Oliver gave) and it doesn't retain the change in the foreign_keys setting. Is this the desired behavior? This would mean you couldn't import data and force the constraint inside a transaction. You have to set the foreign_keys value BEFORE the BEGIN. That kind of makes sense. SQLite version 3.7.0.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> begin transaction; sqlite> pragma foreign_keys; 0 sqlite> pragma foreign_keys=ON; sqlite> pragma foreign_keys; 0 Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Simon Davies Sent: Mon 8/9/2010 9:13 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] foreign key the true error - sqlite 3.7.0.1 On 9 August 2010 14:56, Black, Michael (IS) <michael.bla...@ngc.com> wrote: > From http://www.sqlite.org/foreignkeys.html > > > > Although I believe I compiled my shell WITHOUT these defined but I still > don't get enforcement of the foreign key constraint. > > It appears that foreign keys are fully constrained by default. Is there any > way in the shell to find out if it's enabled? > Shell compiled with SQLITE_OMIT_FOREIGN_KEYS defined SQLite version 3.6.23.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> sqlite> pragma foreign_keys; sqlite> pragma foreign_keys=on; sqlite> pragma foreign_keys; sqlite> pragma foreign_keys=off; sqlite> pragma foreign_keys; sqlite> Shell compiled without SQLITE_OMIT_FOREIGN_KEYS defined SQLite version 3.6.23.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> sqlite> pragma foreign_keys; 0 sqlite> pragma foreign_keys=on; sqlite> pragma foreign_keys; 1 sqlite> pragma foreign_keys=off; sqlite> pragma foreign_keys; 0 . . . > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > Regards, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
