IIRC, there was a bug in enforcement compound foreign keys where one of columns is primary key and it was fixed. So if you take the latest SQLite version I guess you won't see this problem.
Pavel On Fri, Jun 10, 2011 at 11:12 AM, Max B <m...@alleged.net> wrote: > Hello, > > Please consider this (working) schema and this test data, on sqlite 3.7.4: > > CREATE TABLE TestTable ( > id integer primary key, > name varchar, > source_id integer not null, > parent_id integer, > foreign key(parent_id) references TestTable(id) > ); > > PRAGMA foreign_keys=1; > INSERT INTO TestTable VALUES (1, 'Parent', 1, null); > INSERT INTO TestTable VALUES (2, 'Child', 1, 1); > INSERT INTO TestTable VALUES (3, 'Bad', 1, 5); > > This schema is supposed to represent some object which can have an internal > parent/child hierarchy, enforced using the self-referential foreign key. The > first two inserts should work, and the third should fail with a foreign key > error, which it does. > > Next, I wanted to enforce that the source_id between the parent and child are > also identical: that is parents and children must come from the same source. > > So, I tweaked it to use a composite foreign key, and created the required > UNIQUE > index: > > CREATE TABLE TestTable ( > id integer primary key, > name varchar, > source_id integer not null, > parent_id integer, > foreign key(source_id, parent_id) references TestTable(source_id, id) > ); > > CREATE UNIQUE INDEX testindex on TestTable(source_id, id); > > PRAGMA foreign_keys=1; > INSERT INTO TestTable VALUES (1, 'Parent', 1, null); > INSERT INTO TestTable VALUES (2, 'Child', 1, 1); > INSERT INTO TestTable VALUES (3, 'Bad', 1, 5); > > In this situation, the third insert succeeds, in spite of it's parent id not > existing. sqlite fails to enforce the constraint. > > If I tweak the schema *slightly*, only replacing 'integer primary key' with > 'integer unique', I do get the expected behaviour: > > CREATE TABLE TestTable ( > id INTEGER UNIQUE, > name VARCHAR, > source_id INTEGER NOT NULL, > parent_id INTEGER, > FOREIGN KEY(source_id, parent_id) REFERENCES TestTable(source_id, id) > ); > > CREATE UNIQUE INDEX testindex ON TestTable(source_id, id); > > In this case, the third insert fails as expected. However, this is not > substantively different then the second schema, but one enforces the FK > properly > and the other doesn't. I looked at the opcodes for the second, but couldn't > figure out the logic error. > > If it matters, I'm doing this testing on Ubuntu 11.04, with sqlite 3.7.4. > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
