On Thu, May 13, 2010 at 5:50 PM, George Somers <george.som...@2020.net>wrote:
> 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); > The artistid column is already unique because it is the primary key. This index is pointless. And it seems to be confusing SQLite in some way as well. Your work-around is to drop the above index. > 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 > -- --------------------- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
