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

Reply via email to