/ Check-in [9a4b7ec2]
Login

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

Overview
Comment:Use the affinity and collation sequence associated with the parent key when finding child table rows to apply a foreign key action to.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9a4b7ec2928307e88783223903c842accaff7ccf
User & Date: dan 2009-09-29 16:38:59
Context
2009-09-30
04:28
Change a couple of comments to use "SQLITE_MUTEX_OMIT" instead of OMIT_MUTEX. check-in: b733e939 user: dan tags: trunk
2009-09-29
16:38
Use the affinity and collation sequence associated with the parent key when finding child table rows to apply a foreign key action to. check-in: 9a4b7ec2 user: dan tags: trunk
15:41
Check that a unique index uses the default collation sequences for each column before using it as part of a foreign key constraint operation. check-in: 64154174 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

   948    948         assert( iFromCol>=0 );
   949    949         tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid";
   950    950         tFromCol.z = pFKey->pFrom->aCol[iFromCol].zName;
   951    951   
   952    952         tToCol.n = sqlite3Strlen30(tToCol.z);
   953    953         tFromCol.n = sqlite3Strlen30(tFromCol.z);
   954    954   
   955         -      /* Create the expression "zFromCol = OLD.zToCol" */
          955  +      /* Create the expression "OLD.zToCol = zFromCol". It is important
          956  +      ** that the "OLD.zToCol" term is on the LHS of the = operator, so
          957  +      ** that the affinity and collation sequence associated with the
          958  +      ** parent table are used for the comparison. */
   956    959         pEq = sqlite3PExpr(pParse, TK_EQ,
   957         -          sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol),
   958    960             sqlite3PExpr(pParse, TK_DOT, 
   959    961               sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
   960    962               sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
   961         -          , 0)
          963  +          , 0),
          964  +          sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol)
   962    965         , 0);
   963    966         pWhere = sqlite3ExprAnd(db, pWhere, pEq);
   964    967   
   965    968         /* For ON UPDATE, construct the next term of the WHEN clause.
   966    969         ** The final WHEN clause will be like this:
   967    970         **
   968    971         **    WHEN NOT(old.col1 IS new.col1 AND ... AND old.colN IS new.colN)

Changes to test/fkey2.test.

   652    652     }
   653    653   } {{} A {} {} B {} 3 A 2 3 B 2}
   654    654   
   655    655   #-------------------------------------------------------------------------
   656    656   # The following tests, fkey2-10.*, test "foreign key mismatch" and 
   657    657   # other errors.
   658    658   #
   659         -set tn 1
          659  +set tn 0
   660    660   foreach zSql [list {
   661    661     CREATE TABLE p(a PRIMARY KEY, b);
   662    662     CREATE TABLE c(x REFERENCES p(c));
   663    663   } {
   664    664     CREATE TABLE c(x REFERENCES v(y));
   665    665     CREATE VIEW v AS SELECT x AS y FROM c;
   666    666   } {
................................................................................
   668    668     CREATE TABLE c(x REFERENCES p);
   669    669   } {
   670    670     CREATE TABLE p(a COLLATE binary, b);
   671    671     CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
   672    672     CREATE TABLE c(x REFERENCES p(a));
   673    673   }] {
   674    674     drop_all_tables
   675         -
   676         -  do_test fkey2-10.1.$tn {
          675  +  do_test fkey2-10.1.[incr tn] {
   677    676       execsql $zSql
   678    677       catchsql { INSERT INTO c DEFAULT VALUES }
   679    678     } {1 {foreign key mismatch}}
   680    679   }
   681    680   
   682    681   # "rowid" cannot be used as part of a child or parent key definition 
   683    682   # unless it happens to be the name of an explicitly declared column.
................................................................................
   765    764   } {1 {foreign key constraint failed}}
   766    765   do_test fkey2-12.1.7 { 
   767    766     execsql {
   768    767       INSERT INTO t1 VALUES(2, 'two');
   769    768       COMMIT;
   770    769     }
   771    770   } {}
          771  +
          772  +drop_all_tables
          773  +do_test fkey2-12.2.1 {
          774  +  execsql {
          775  +    CREATE TABLE t1(x COLLATE NOCASE PRIMARY KEY);
          776  +    CREATE TRIGGER tt1 AFTER DELETE ON t1 
          777  +      WHEN EXISTS ( SELECT 1 FROM t2 WHERE old.x = y )
          778  +    BEGIN
          779  +      INSERT INTO t1 VALUES(old.x);
          780  +    END;
          781  +    CREATE TABLE t2(y REFERENCES t1);
          782  +    INSERT INTO t1 VALUES('A');
          783  +    INSERT INTO t1 VALUES('B');
          784  +    INSERT INTO t2 VALUES('a');
          785  +    INSERT INTO t2 VALUES('b');
          786  +
          787  +    SELECT * FROM t1;
          788  +    SELECT * FROM t2;
          789  +  }
          790  +} {A B a b}
          791  +do_test fkey2-12.2.2 {
          792  +  execsql { DELETE FROM t1 }
          793  +  execsql {
          794  +    SELECT * FROM t1;
          795  +    SELECT * FROM t2;
          796  +  }
          797  +} {A B a b}
          798  +do_test fkey2-12.2.3 {
          799  +  execsql {
          800  +    DROP TABLE t2;
          801  +    CREATE TABLE t2(y REFERENCES t1 ON DELETE RESTRICT);
          802  +    INSERT INTO t2 VALUES('a');
          803  +    INSERT INTO t2 VALUES('b');
          804  +  }
          805  +  catchsql { DELETE FROM t1 }
          806  +} {1 {foreign key constraint failed}}
          807  +do_test fkey2-12.2.4 {
          808  +  execsql {
          809  +    SELECT * FROM t1;
          810  +    SELECT * FROM t2;
          811  +  }
          812  +} {A B a b}
   772    813   
   773    814   #-------------------------------------------------------------------------
   774    815   # The following tests, fkey2-13.*, test that FK processing is performed
   775    816   # when rows are REPLACEd.
   776    817   #
   777    818   drop_all_tables
   778    819   do_test fkey2-13.1.1 {