SQLite

Check-in [53902f7d4a]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Correct handling of compound foreign key constraints that include the integer primary key as one of the columns. Ticket [ce7c133ea6cc9ccdc1]
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 53902f7d4a46aa70ecc5bf180a01ff888d52686a
User & Date: drh 2010-07-29 01:50:39.000
References
2010-07-29
01:55 Ticket [ce7c133ea6] Foreign key constraint fails when it should succeed. status still Open with 4 other changes (artifact: 6fb11fefa6 user: drh)
Context
2010-07-29
10:07
Change the profile timer units back to nanoseconds and update the sqlite3_profile() documentation. Ticket [c43940c49b74c70a69] (check-in: 7783b98a93 user: drh tags: trunk)
01:50
Correct handling of compound foreign key constraints that include the integer primary key as one of the columns. Ticket [ce7c133ea6cc9ccdc1] (check-in: 53902f7d4a user: drh tags: trunk)
2010-07-28
19:17
Get SQLITE_OMIT_VIRTUALTABLE working again after being broken by recent changes. (check-in: 33b1e862ff user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/fkey.c.
496
497
498
499
500
501
502
503

504
505
506
507
508
509
510
    pLeft = sqlite3Expr(db, TK_REGISTER, 0);
    if( pLeft ){
      /* Set the collation sequence and affinity of the LHS of each TK_EQ
      ** expression to the parent key column defaults.  */
      if( pIdx ){
        Column *pCol;
        iCol = pIdx->aiColumn[i];
        pCol = &pIdx->pTable->aCol[iCol];

        pLeft->iTable = regData+iCol+1;
        pLeft->affinity = pCol->affinity;
        pLeft->pColl = sqlite3LocateCollSeq(pParse, pCol->zColl);
      }else{
        pLeft->iTable = regData;
        pLeft->affinity = SQLITE_AFF_INTEGER;
      }







|
>







496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
    pLeft = sqlite3Expr(db, TK_REGISTER, 0);
    if( pLeft ){
      /* Set the collation sequence and affinity of the LHS of each TK_EQ
      ** expression to the parent key column defaults.  */
      if( pIdx ){
        Column *pCol;
        iCol = pIdx->aiColumn[i];
        pCol = &pTab->aCol[iCol];
        if( pTab->iPKey==iCol ) iCol = -1;
        pLeft->iTable = regData+iCol+1;
        pLeft->affinity = pCol->affinity;
        pLeft->pColl = sqlite3LocateCollSeq(pParse, pCol->zColl);
      }else{
        pLeft->iTable = regData;
        pLeft->affinity = SQLITE_AFF_INTEGER;
      }
Changes to test/fkey2.test.
1932
1933
1934
1935
1936
1937
1938













































1939
1940
} {1 {foreign key constraint failed}}
do_test fkey2-dd08e5.1.6 {
  catchsql {
    UPDATE tdd08 SET a=a+1;
  }
} {1 {foreign key constraint failed}}















































finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
} {1 {foreign key constraint failed}}
do_test fkey2-dd08e5.1.6 {
  catchsql {
    UPDATE tdd08 SET a=a+1;
  }
} {1 {foreign key constraint failed}}

#-------------------------------------------------------------------------
# Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba
# fixed.
#
do_test fkey2-ce7c13.1.1 {
  execsql {
    CREATE TABLE tce71(a INTEGER PRIMARY KEY, b);
    CREATE UNIQUE INDEX ice71 ON tce71(a,b);
    INSERT INTO tce71 VALUES(100,200);
    CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b));
    INSERT INTO tce72 VALUES(300,100,200);
    UPDATE tce71 set b = 200 where a = 100;
    SELECT * FROM tce71, tce72;
  }
} {100 200 300 100 200}
do_test fkey2-ce7c13.1.2 {
  catchsql {
    UPDATE tce71 set b = 201 where a = 100;
  }
} {1 {foreign key constraint failed}}
do_test fkey2-ce7c13.1.3 {
  catchsql {
    UPDATE tce71 set a = 101 where a = 100;
  }
} {1 {foreign key constraint failed}}
do_test fkey2-ce7c13.1.4 {
  execsql {
    CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b));
    INSERT INTO tce73 VALUES(100,200);
    CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
    INSERT INTO tce74 VALUES(300,100,200);
    UPDATE tce73 set b = 200 where a = 100;
    SELECT * FROM tce73, tce74;
  }
} {100 200 300 100 200}
do_test fkey2-ce7c13.1.5 {
  catchsql {
    UPDATE tce73 set b = 201 where a = 100;
  }
} {1 {foreign key constraint failed}}
do_test fkey2-ce7c13.1.6 {
  catchsql {
    UPDATE tce73 set a = 101 where a = 100;
  }
} {1 {foreign key constraint failed}}

finish_test