On Mon, May 17, 2010 at 11:34 AM, Pavel Ivanov <paiva...@gmail.com> wrote:
> > But I can agree that SQLite shouldn't be confused by confusing > database schemas and enforce foreign keys correctly in all cases. > > Fixed. See http://www.sqlite.org/src/info/dd08e5a988 and http://www.sqlite.org/src/info/636f86095e > > Pavel > > On Fri, May 14, 2010 at 3:01 PM, George Somers <george.som...@2020.net> > wrote: > > 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 > > > _______________________________________________ > 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
