SQLite

View Ticket
Login
Ticket Hash: ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba
Title: Foreign key constraint fails when it should succeed.
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Code_Generator Resolution: Fixed
Last Modified: 2010-07-31 20:55:02
Version Found In: 3.7.0
Description:
Consider the following SQL:
  PRAGMA foreign_keys=ON;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
  CREATE UNIQUE INDEX i1 ON t1(a,b);
  INSERT INTO t1 VALUES(100,200);
  CREATE TABLE t2(w INTEGER,x INTEGER,y INTEGER,FOREIGN KEY(x,y) REFERENCES t1(a,b));
  INSERT INTO t2 VALUES(300,100,200);
  UPDATE t1 set b = 200 where a = 100;

The final update fails, even though it should not.

This problem seems to have been introduced by checkin [636f86095eb1f4bdcfb0c9ed846c4c6b3589c10b] on 2010-05-14 19:24:02 which was to fix prior ticket [dd08e5a988d00decc4a543daa8dbbfab9c577ad8]. It appears that the fix to the prior problem was not quite right.

The new problem first appears in the 3.7.0 release.


drh added on 2010-07-29 01:55:02:
The issue was in the handling of multi-column foreign key constraints where one of the columns was an integer primary key. The error has always been in the code. It was simply masked by the prior error of ticket [dd08e5a988d00decc4a543daa8dbbfab9c577ad8]. The fix in check-in [636f86095eb1f4bdcfb0c9ed846c4c6b3589c10b] is correct. That fix did not introduce a new problem; it merely brought an existing problem into view.

The Severity of this ticket is downgraded to Important since one can easily argue that any schema that has a multi-column foreign key constraint where one of the columns is a primary key is a broken schema. Even so, we ought to handle even broken schemas correctly, which should be the case after check-in [53902f7d4a46aa70ecc5bf180a01ff888d52686a].


anonymous added on 2010-07-31 06:43:18:
It seems that foreign key isnt't working as explained on http://www.sqlite.org/foreignkeys.html everything there succeed when it shouldn't, I tested with 3.6.23.1 with the same results. I even downloaded a windows binary of sqlite3.exe 3.7 from here to see if the problem was on my compilation flags but it isn't because the binary provided here also behave the same way (it parses without complain but work as if no foreign key restrictions exist).


anonymous added on 2010-07-31 06:49:02:
Sorry but it was my fault I didn't noticed the "PRAGMA foreign_keys" I was expecting it should be "ON" by default but it seems that is the opposite, maybe for backward compatibility. I'm not sure if this is the best default settings ? PRAGMA foreign_keys=OFF;


anonymous added on 2010-07-31 06:57:00:
Why not have a compiler flag to set the default for "PRAGMA foreign_keys" this way new development can have this pragma "ON" by default ?