Re: [sqlite] Composite Foreign Key not enforced

[George Somers]

> Fixed.  See http://www.sqlite.org/src/info/dd08e5a988 and
http://www.sqlite.org/src/info/636f86095e

Gee!  That was super FAST!

Thanks a lot!
George Somers



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Composite Foreign Key not enforced

[George Somers]

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

Re: [sqlite] Composite Foreign Key not enforced

[Nicolas Williams]

On Fri, May 14, 2010 at 03:01:39PM -0400, George Somers wrote:

[edited for brevity]

> CREATE TABLE parent(parentID INTEGER PRIMARY KEY, ...);
> 
> CREATE TABLE child(childID INTEGER PRIMARY KEY, fatherID INTEGER, ...);
> 
> 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.
> );

Why do you need fatherChildActivity to make sure both, child and father
exist when the existence of the child implies the existence of the
father?  Or can you have father-less child rows as long as the have no
relations in fatherChildActivity?

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Composite Foreign Key not enforced

[Richard Hipp]

On Mon, May 17, 2010 at 11:34 AM, Pavel Ivanov  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 
> 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,
> >   fatherIDINTEGER,
> >   motherIDINTEGER,
> >   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

Re: [sqlite] Composite Foreign Key not enforced

[Pavel Ivanov]

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  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

Re: [sqlite] Composite Foreign Key not enforced

[George Somers]

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,   
   fatherIDINTEGER,   
   motherIDINTEGER,   
   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

Re: [sqlite] Composite Foreign Key not enforced

[Richard Hipp]

On Thu, May 13, 2010 at 5:50 PM, George Somers wrote:

> Hello,
>
> It seems that composite FK are not enforced on SQLite 3.6.23.1.
> The following script shows that the DELETE FROM table "artist" will work,
> even though
> there is a composite FK from table "track" toward  table "artist".
>
> PRAGMA foreign_keys = ON;
>
> CREATE TABLE artist(
>  artistidINTEGER PRIMARY KEY,
>  bandid  INTEGER
> );
> CREATE UNIQUE INDEX i1 ON artist(artistid, bandid);
>

The artistid column is already unique because it is the primary key.  This
index is pointless.  And it seems to be confusing SQLite in some way as
well.  Your work-around is to drop the above index.


> INSERT INTO artist VALUES(1, 10);
>
> CREATE TABLE track(
>  trackid INTEGER PRIMARY KEY,
>  trackartist INTEGER,
>  trackband   INTEGER,
>  --   FOREIGN KEY(trackartist) REFERENCES artist(artistid)  -- This FK will
> be enforced, as it should!
>  FOREIGN KEY(trackartist, trackband) REFERENCES artist(artistid, bandid)
>  -- This FK won't be enforced!
> );
> CREATE INDEX i2 ON track(trackartist, trackband);
> INSERT INTO track VALUES(100, 1, 10);
>
> -- This will execute, because SQLite will not check for composite FK,
> -- even when the parent table (artist) has the required indexes.
> -- Note here that even the child table (track) has an index (which is not
> -- required, but just speed up lookup) ... just to further demonstrate the
> problem.
> DELETE FROM artist WHERE artistid = 1;
>
> SELECT * FROM artist;
>
>
> Is there something I am doing wrong?
>
> Thanks!
> George Somers
>
> ___
> 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

[sqlite] Composite Foreign Key not enforced

[George Somers]

Hello,

It seems that composite FK are not enforced on SQLite 3.6.23.1.
The following script shows that the DELETE FROM table "artist" will work, even 
though
there is a composite FK from table "track" toward  table "artist".

PRAGMA foreign_keys = ON;

CREATE TABLE artist(
  artistidINTEGER PRIMARY KEY,
  bandid  INTEGER
);
CREATE UNIQUE INDEX i1 ON artist(artistid, bandid);
INSERT INTO artist VALUES(1, 10);

CREATE TABLE track(
  trackid INTEGER PRIMARY KEY,
  trackartist INTEGER,
  trackband   INTEGER,
  --   FOREIGN KEY(trackartist) REFERENCES artist(artistid)  -- This FK will be 
enforced, as it should!
  FOREIGN KEY(trackartist, trackband) REFERENCES artist(artistid, bandid)  -- 
This FK won't be enforced!
);
CREATE INDEX i2 ON track(trackartist, trackband);
INSERT INTO track VALUES(100, 1, 10);

-- This will execute, because SQLite will not check for composite FK,
-- even when the parent table (artist) has the required indexes.
-- Note here that even the child table (track) has an index (which is not
-- required, but just speed up lookup) ... just to further demonstrate the 
problem.
DELETE FROM artist WHERE artistid = 1;

SELECT * FROM artist;


Is there something I am doing wrong?

Thanks!
George Somers

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users