On Oct 8, 2009, at 6:59 AM, Thomas Henlich wrote: > Hi, > > I found a bug in the .genfkey functionality:
Thank you for the bug report. Did you know that the next release of SQLite will contain native support for foreign key constraints? The ".genfkey" functionality will become obsolete. SQLite version 3.6.19 is currently scheduled for Oct 21 (but that date subject to change in either direction). See http://www.sqlite.org/draft/index.html for a preview (also subject to change) of the website after release. Beginning with the next release, SQLite will support foreign key constraints, both immediate and deferred, with cascading deletes and updates. Enforcement of foreign key constraints is turned off by default (for backwards compatibility) and must be enabled separately for each connection using "PRAGMA foreign_keys=ON". The code on the tip of the source tree is stable and working if you want to download a copy and try it. The implementation does not support the MATCH clause nor the ability to toggle individual constraints between IMMEDIATE and DEFERRED. All other aspects of foreign key constraints are supported. > > If a foreign key constraint has or references columns which need to > be quoted, > the .genfkey command will generate invalid triggers which fail to > enforce that particular constraint. Example: > > CREATE TABLE parent("a.1", PRIMARY KEY("a.1")); > CREATE TABLE child("b.2", FOREIGN KEY("b.2") REFERENCES parent("a. > 1")); > .genfkey --exec > INSERT INTO parent VALUES(1); > INSERT INTO child VALUES(1); > UPDATE parent SET "a.1"=0; > UPDATE child SET "b.2"=7; > SELECT * FROM parent; > SELECT * FROM child; > > Actual result: > 0 > 7 > > Expected result: > SQL error near line 6: constraint failed > SQL error near line 7: constraint failed > 1 > 1 > > I suggest the fix attached to this mail. > > Regards, > Thomas. > <shell.c.quotes-patch>_______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
