Hi,
I found a bug in the .genfkey functionality:
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.
--- sqlite-3.6.18.orig/shell.c 2009-09-11 17:48:18.000000000 +0200
+++ sqlite-3.6.18.fix/shell.c 2009-10-08 12:39:20.000000000 +0200
@@ -876,8 +876,8 @@
", '/ref/', dq(to_tbl)"
", '/key_notnull/', sj('new.' || dq(from_col) || ' IS NOT NULL', ' AND ')"
- ", '/fkey_list/', sj(to_col, ', ')"
- ", '/rkey_list/', sj(from_col, ', ')"
+ ", '/fkey_list/', sj(dq(to_col), ', ')"
+ ", '/rkey_list/', sj(dq(from_col), ', ')"
", '/cond1/', sj(multireplace('new./from/ == /to/'"
", '/from/', dq(from_col)"
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
