Hello, It seems that composite FK are not enforced on SQLite 3.6.23.1. The following script shows that the DELETE FROM table "artist" will work, even though there is a composite FK from table "track" toward table "artist".
PRAGMA foreign_keys = ON; CREATE TABLE artist( artistid INTEGER PRIMARY KEY, bandid INTEGER ); CREATE UNIQUE INDEX i1 ON artist(artistid, bandid); INSERT INTO artist VALUES(1, 10); CREATE TABLE track( trackid INTEGER PRIMARY KEY, trackartist INTEGER, trackband INTEGER, -- FOREIGN KEY(trackartist) REFERENCES artist(artistid) -- This FK will be enforced, as it should! FOREIGN KEY(trackartist, trackband) REFERENCES artist(artistid, bandid) -- This FK won't be enforced! ); CREATE INDEX i2 ON track(trackartist, trackband); INSERT INTO track VALUES(100, 1, 10); -- This will execute, because SQLite will not check for composite FK, -- even when the parent table (artist) has the required indexes. -- Note here that even the child table (track) has an index (which is not -- required, but just speed up lookup) ... just to further demonstrate the problem. DELETE FROM artist WHERE artistid = 1; SELECT * FROM artist; Is there something I am doing wrong? Thanks! George Somers _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
