Thanks for the comment Pavel! The example was simple and meant only to show the problem. Sure, the example looks like bad database design!
> your indexes on (childID, fatherID) and (childID, motherID) are useless - According to the documentation, they are required: See http://www.sqlite.org/foreignkeys.html which says " Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index.". The real database has 240 tables. The data access layer is doing CRUD only (Create/Read/Update/Delete). No joins between tables. This is why denormalization is used a lot. Denormalization is mainly there because of "surrogate key with propagation". Since the data access layer is doing CRUD only, no joins, it is useful to propagate parent ID to child tables. Also, "surrogate key with propagation" is used to get rid of some T-SQL constraint from the old MS SQL Express version of the application. So now the application is independent of the RDBMS. It the real application, confusion of the composite FK versus non-composite FK was not possible ... since when there is a composite FK, the "redundant" non-composite FKs are not defined toward individual parent tables's PK. So SQLite could not be confused in the real application. By the way, I ran many speed test. I was please to see that SQLite3 was 2.5 faster than MS SQL Compact. And it is on pair with MS SQL Express (i.e. same speed). But SQLite3 is not restricted to 4Gig database! And it is much much easier to install than MS SQL Express/Server. Cool! I like it very much! Bye bye George Somers -----Message d'origine----- De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] De la part de Pavel Ivanov Envoyé : 17 mai 2010 11:35 À : General Discussion of SQLite Database Objet : Re: [sqlite] Composite Foreign Key not enforced Looks like the same suggestion as DRH already provided applies here: your indexes on (childID, fatherID) and (childID, motherID) are useless. You already have unique childID, adding fatherID or matherID to it doesn't change things. Also any other table can reference only childID, there's no need to reference (childID, fatherID) because from childID you can identify fatherID uniquely - there's no possibility for one childID to have several fatherID. The same argument goes towards having fcParentID in the fatherChildActivity - you can have it there only if you want some denormalization and redundancy... But I can agree that SQLite shouldn't be confused by confusing database schemas and enforce foreign keys correctly in all cases. 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
