On 04/17/2017 10:42 PM, Bernard Schurdevin wrote:
Hi,
I get weird results (false positive) to PRAGMA foreign_key_check on
WITHOUT ROWID table depending on foreign key field position.
Thanks for reporting this. Should be fixed here:
http://www.sqlite.org/src/info/690870bd7b2e607b
Dan.
Kind regards.
=====================================================================================
-- tested with Window CLI, versions 3.8.5, 3.9.2, 3.14.1, 3.18.0
PRAGMA foreign_keys=ON;
-- bad case
CREATE TABLE masters_bad (id INTEGER PRIMARY KEY AUTOINCREMENT,
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_bad (line INTEGER NOT NULL,
quantity INTEGER NOT NULL, master INTEGER NOT NULL REFERENCES
masters_bad(id), PRIMARY KEY(master, line)) WITHOUT ROWID;
BEGIN;
INSERT INTO masters_bad (reference) VALUES ('this is a ref');
INSERT INTO details_bad (master, line, quantity) VALUES
(last_insert_rowid(), 1, 999);
COMMIT;
-- ok case 1 (fk in front of fields)
CREATE TABLE masters_ok1 (id INTEGER PRIMARY KEY AUTOINCREMENT,
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_ok1 (master INTEGER NOT NULL
REFERENCES masters_ok1(id), line INTEGER NOT NULL, quantity INTEGER
NOT NULL, PRIMARY KEY(master,line)) WITHOUT ROWID;
BEGIN;
INSERT INTO masters_ok1 (reference) VALUES ('this is a ref');
INSERT INTO details_ok1 (master, line, quantity) VALUES
(last_insert_rowid(), 1, 999);
COMMIT;
-- ok case 2 (no more quantity field)
CREATE TABLE masters_ok2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_ok2 (line INTEGER NOT NULL, master
INTEGER NOT NULL REFERENCES masters_ok2(id), PRIMARY KEY(master,
line)) WITHOUT ROWID;
BEGIN;
INSERT INTO masters_ok2 (reference) VALUES ('this is a ref');
INSERT INTO details_ok2 (master, line) VALUES (last_insert_rowid(), 1);
COMMIT;
-- ok case 3 fields order changed
CREATE TABLE masters_ok3 (id INTEGER PRIMARY KEY AUTOINCREMENT,
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_ok3 (line INTEGER NOT NULL, master
INTEGER NOT NULL REFERENCES masters_ok3(id), quantity INTEGER NOT
NULL, PRIMARY KEY(master, line)) WITHOUT ROWID;
BEGIN;
INSERT INTO masters_ok3 (reference) VALUES ('this is a ref');
INSERT INTO details_ok3 (master, line, quantity) VALUES
(last_insert_rowid(), 1, 999);
COMMIT;
-- checking
PRAGMA foreign_key_check;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
