Thanks for your quick answer! I was trying to provide a simple example. Here is another example to demonstrate the problem. I am using a lot "surrogate key with propagation", and this is why I have many composite foreign keys.
PRAGMA foreign_keys = ON; CREATE TABLE parent( parentID INTEGER PRIMARY KEY, parentName TEXT ); CREATE TABLE child( childID INTEGER PRIMARY KEY, fatherID INTEGER, motherID INTEGER, childName TEXT, FOREIGN KEY(fatherID) REFERENCES parent(parentID), FOREIGN KEY(motherID) REFERENCES parent(parentID) ); CREATE UNIQUE INDEX i1 ON child(childID, fatherID); CREATE UNIQUE INDEX i2 ON child(childID, motherID); CREATE TABLE fatherChildActivity( fcChildID INTEGER, fcParentID INTEGER, fcPlaysWhat TEXT, FOREIGN KEY(fcChildID, fcParentID) REFERENCES child(childID, fatherID) -- This foreign key make sure that 1) child exist, 2) father exist, -- 3) father is really father of child. ); INSERT INTO parent VALUES(1, 'parent #1'); INSERT INTO parent VALUES(2, 'parent #2'); INSERT INTO child VALUES(1, 1, 2, 'child #1'); INSERT INTO fatherChildActivity VALUES(1, 1, 'Baseball'); DELETE FROM child WHERE childID = 1; A FK between fatherChildActivity and child with another FK between fatherChildActivity and parent does not constraint the parent to be the father of the child. The FOREIGN KEY(fcChildID, fcParentID) REFERENCES child(childID, fatherID is enforced while doing INSERT, but not while doing DELETE! George Somers _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
