This is the minimal SQL for a test case needed to reproduce the oid -
foreign key bug:
(By changing only 1 character renaming oid to xid in the first line, the
SQL works as intended).
CREATE TABLE tParent(id INTEGER PRIMARY KEY, c1 TEXT, oid INTEGER);
CREATE TABLE tChild( id INTEGER PRIMARY KEY, Data TEXT,
FOREIGN KEY (id) REFERENCES tParent(id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
INSERT INTO tParent (c1) VALUES ('aaa');
INSERT INTO tChild (id, Data) VALUES (last_insert_rowid(), 'FK A');
UPDATE tParent SET id = 5 WHERE id = 1; -- This will fail but shouldn't
-- OR --
DELETE FROM tParent WHERE id = 1; -- This will fail but shouldn't
Note that
- "oid" is never referenced again - it's mere presence in the schema
causes the error.
- This error does NOT happen when either "rowid" or "_rowid_" is used
- only when using "oid".
