Hi everyone, consider the following example:
PRAGMA foreign_keys=true; CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1)); INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1), (0, 2); SELECT * FROM t0; -- returns 0|1 I expect the INSERT to fail, since both the UNIQUE and the FOREIGN KEY constraints are violated. However, the (0, 1) row is inserted, as the result of the SELECT query above demonstrates. When splitting up the INSERT into two INSERTS, no row is inserted, as expected: PRAGMA foreign_keys=true; CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1)); INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1); INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 2); SELECT * FROM t0; -- returns no row I found this bug because a WHERE clause did not fetch the row after a REINDEX: PRAGMA foreign_keys=true; CREATE TABLE t0 (c0 INT UNIQUE, c1 TEXT UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1)); INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1), (0, 2); REINDEX;; SELECT * FROM t0; -- returns 0|1 SELECT * FROM t0 WHERE c1=1; -- returns nothing Best, Manuel _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
