/ Check-in [53902f7d]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 53902f7d4a46aa70ecc5bf180a01ff888d52686a
User & Date: drh 2010-07-29 01:50:39
References
2010-07-29
01:55 Ticket [ce7c133e] Foreign key constraint fails when it should succeed. status still Open with 4 other changes artifact: 6fb11fef 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: 7783b98a 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: 53902f7d user: drh tags: trunk
2010-07-28
19:17
Get SQLITE_OMIT_VIRTUALTABLE working again after being broken by recent changes. check-in: 33b1e862 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

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

Changes to test/fkey2.test.

  1932   1932   } {1 {foreign key constraint failed}}
  1933   1933   do_test fkey2-dd08e5.1.6 {
  1934   1934     catchsql {
  1935   1935       UPDATE tdd08 SET a=a+1;
  1936   1936     }
  1937   1937   } {1 {foreign key constraint failed}}
  1938   1938   
         1939  +#-------------------------------------------------------------------------
         1940  +# Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba
         1941  +# fixed.
         1942  +#
         1943  +do_test fkey2-ce7c13.1.1 {
         1944  +  execsql {
         1945  +    CREATE TABLE tce71(a INTEGER PRIMARY KEY, b);
         1946  +    CREATE UNIQUE INDEX ice71 ON tce71(a,b);
         1947  +    INSERT INTO tce71 VALUES(100,200);
         1948  +    CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b));
         1949  +    INSERT INTO tce72 VALUES(300,100,200);
         1950  +    UPDATE tce71 set b = 200 where a = 100;
         1951  +    SELECT * FROM tce71, tce72;
         1952  +  }
         1953  +} {100 200 300 100 200}
         1954  +do_test fkey2-ce7c13.1.2 {
         1955  +  catchsql {
         1956  +    UPDATE tce71 set b = 201 where a = 100;
         1957  +  }
         1958  +} {1 {foreign key constraint failed}}
         1959  +do_test fkey2-ce7c13.1.3 {
         1960  +  catchsql {
         1961  +    UPDATE tce71 set a = 101 where a = 100;
         1962  +  }
         1963  +} {1 {foreign key constraint failed}}
         1964  +do_test fkey2-ce7c13.1.4 {
         1965  +  execsql {
         1966  +    CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b));
         1967  +    INSERT INTO tce73 VALUES(100,200);
         1968  +    CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
         1969  +    INSERT INTO tce74 VALUES(300,100,200);
         1970  +    UPDATE tce73 set b = 200 where a = 100;
         1971  +    SELECT * FROM tce73, tce74;
         1972  +  }
         1973  +} {100 200 300 100 200}
         1974  +do_test fkey2-ce7c13.1.5 {
         1975  +  catchsql {
         1976  +    UPDATE tce73 set b = 201 where a = 100;
         1977  +  }
         1978  +} {1 {foreign key constraint failed}}
         1979  +do_test fkey2-ce7c13.1.6 {
         1980  +  catchsql {
         1981  +    UPDATE tce73 set a = 101 where a = 100;
         1982  +  }
         1983  +} {1 {foreign key constraint failed}}
  1939   1984   
  1940   1985   finish_test