Two separate problems, both now fixed on trunk. On 5/6/19, Manuel Rigger <rigger.man...@gmail.com> wrote: > 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 >
-- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
