>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? 2. Enabling Foreign Key Support In order to use foreign key constraints in SQLite, the library must be compiled with neither SQLITE_OMIT_FOREIGN_KEY <https://owa1.ngc.com/exchange/Michael.Black2/Drafts/RE:%20EXTERNAL:Re:%20[sqlite]%20foreign%20key%20the%20true%20error%20-%20sqlite%203.7.0.1-2.EML/compile.html#omit_foreign_key> or SQLITE_OMIT_TRIGGER <https://owa1.ngc.com/exchange/Michael.Black2/Drafts/RE:%20EXTERNAL:Re:%20[sqlite]%20foreign%20key%20the%20true%20error%20-%20sqlite%203.7.0.1-2.EML/compile.html#omit_trigger> defined. If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to version 3.6.19 - foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list <https://owa1.ngc.com/exchange/Michael.Black2/Drafts/RE:%20EXTERNAL:Re:%20[sqlite]%20foreign%20key%20the%20true%20error%20-%20sqlite%203.7.0.1-2.EML/pragma.html#pragma_foreign_key_list> , but foreign key constraints are not enforced. The PRAGMA foreign_keys <https://owa1.ngc.com/exchange/Michael.Black2/Drafts/RE:%20EXTERNAL:Re:%20[sqlite]%20foreign%20key%20the%20true%20error%20-%20sqlite%203.7.0.1-2.EML/pragma.html#pragma_foreign_keys> command is a no-op in this configuration. If OMIT_FOREIGN_KEY is defined, then foreign key definitions cannot even be parsed (attempting to specify a foreign key definition is a syntax error). Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys <https://owa1.ngc.com/exchange/Michael.Black2/Drafts/RE:%20EXTERNAL:Re:%20[sqlite]%20foreign%20key%20the%20true%20error%20-%20sqlite%203.7.0.1-2.EML/pragma.html#pragma_foreign_keys> command. For example: sqlite> PRAGMA foreign_keys = ON; If put a trigger in it works (of course) PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; 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; CREATE TRIGGER trig1 before insert on staff_02 for each row begin select raise(abort,'staff_02 insert violates foreign key') where (select id from staff_01 where id=NEW.id_staff_editor) IS NULL; end; INSERT INTO "staff_02" VALUES(1,1000,'CB'); INSERT INTO "staff_02" VALUES(2,1,'CB'); COMMIT; First insert fails...2nd one works.... Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Oliver Peters Sent: Mon 8/9/2010 8:38 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] foreign key the true error - sqlite 3.7.0.1 Igor Tandetnik <itandet...@...> writes: > > Oliver Peters <oliver....@...> wrote: > > This sql code works in the two cases but AFAIK it shouldn't in the second > > > > PRAGMA foreign_keys = ON; > > > > CREATE TABLE staff_02( > > id INTEGER PRIMARY KEY AUTOINCREMENT, > > id_staff_editor INTEGER NOT NULL, > > code CHAR(2) NOT NULL, > > UNIQUE(code) > > FOREIGN KEY(id_staff_editor) REFERENCES staff_02(id) > > ); > > > > INSERT INTO staff_02(id_staff_editor,code) VALUES(1,'CB'); > > The first record gets inserted with an id of 1, which just happens to match the value of id_staff_editor. See > what this would do: > > INSERT INTO staff_02(id_staff_editor,code) VALUES(1000,'CB'); > So I think I misunderstood the concept of Foreign Keys - I thought at first it is checked if the PK exists and if it is not existing it is rejected to INSERT/UPDATE the FK Could you please explain why it is o.k that this works? PRAGMA foreign_keys = ON; CREATE TABLE a( id INTEGER PRIMARY KEY AUTOINCREMENT, id_staff_editor INTEGER NOT NULL, FOREIGN KEY(id_staff_editor) REFERENCES a(id) ); INSERT INTO a(id_staff_editor) VALUES(1); INSERT INTO a(id_staff_editor) VALUES(2); INSERT INTO a(id_staff_editor) VALUES(3); _______________________________________________ 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
