Hi,
I get weird results (false positive) to PRAGMA foreign_key_check on
WITHOUT ROWID table depending on foreign key field position.
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