SQLite: Check-in [64154174]

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

Overview

Comment:	Check that a unique index uses the default collation sequences for each column before using it as part of a foreign key constraint operation.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA1:	64154174cf8a53bd9be818db53cb0e586c5d24cb
User & Date:	dan 2009-09-29 15:41:58

Context

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
11:28		Remove an unused variable from fkey.c. check-in: 582bd768 user: dan tags: trunk

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

            for(i=0; i<nCol; i++) aiCol[i] = pFKey->aCol[i].iFrom;
          }
          break;
        }
      }else{
        /* If zKey is non-NULL, then this foreign key was declared to
        ** map to an explicit list of columns in table pParent. Check if this
        ** index matches those columns.  */

        int i, j;
        for(i=0; i<nCol; i++){













          char *zIdxCol = pParent->aCol[pIdx->aiColumn[i]].zName;
          for(j=0; j<nCol; j++){
            if( sqlite3StrICmp(pFKey->aCol[j].zCol, zIdxCol)==0 ){
              if( aiCol ) aiCol[i] = pFKey->aCol[j].iFrom;
              break;
            }
          }
          if( j==nCol ) break;








|
>


>
>
>
>
>
>
>
>
>
>
>
>
>
|

            for(i=0; i<nCol; i++) aiCol[i] = pFKey->aCol[i].iFrom;
          }
          break;
        }
      }else{
        /* If zKey is non-NULL, then this foreign key was declared to
        ** map to an explicit list of columns in table pParent. Check if this
        ** index matches those columns. Also, check that the index uses
        ** the default collation sequences for each column. */
        int i, j;
        for(i=0; i<nCol; i++){
          int iCol = pIdx->aiColumn[i];     /* Index of column in parent tbl */
          char *zDfltColl;                  /* Def. collation for column */
          char *zIdxCol;                    /* Name of indexed column */

          /* If the index uses a collation sequence that is different from
          ** the default collation sequence for the column, this index is
          ** unusable. Bail out early in this case.  */
          zDfltColl = pParent->aCol[iCol].zColl;
          if( !zDfltColl ){
            zDfltColl = "BINARY";
          }
          if( sqlite3StrICmp(pIdx->azColl[i], zDfltColl) ) break;

          zIdxCol = pParent->aCol[iCol].zName;
          for(j=0; j<nCol; j++){
            if( sqlite3StrICmp(pFKey->aCol[j].zCol, zIdxCol)==0 ){
              if( aiCol ) aiCol[i] = pFKey->aCol[j].iFrom;
              break;
            }
          }
          if( j==nCol ) break;

Changes to test/fkey2.test.

} {}
foreach {tn zSql res} $FkeySimpleTests {
  if {$res == "0 {}"} { set res {0 1} }
  do_test fkey2-1.3.$tn { catchsql $zSql } $res
}
execsql { PRAGMA count_changes = 0 }
drop_all_tables














# Special test: When the parent key is an IPK, make sure the affinity of
# the IPK is not applied to the child key value before it is inserted
# into the child table.
do_test fkey2-1.4.1 {
  execsql {
    CREATE TABLE i(i INTEGER PRIMARY KEY);
    CREATE TABLE j(j REFERENCES i);
    INSERT INTO i VALUES(35);
    INSERT INTO j VALUES('35.0');
    SELECT j, typeof(j) FROM j;
  }
} {35.0 text}
do_test fkey2-1.4.2 {
  catchsql { DELETE FROM i }
} {1 {foreign key constraint failed}}

# Same test using a regular primary key with integer affinity.
drop_all_tables
do_test fkey2-1.5.1 {
  execsql {
    CREATE TABLE i(i INT UNIQUE);
    CREATE TABLE j(j REFERENCES i(i));
    INSERT INTO i VALUES('35.0');
    INSERT INTO j VALUES('35.0');
    SELECT j, typeof(j) FROM j;
    SELECT i, typeof(i) FROM i;
  }
} {35.0 text 35 integer}
do_test fkey2-1.5.2 {
  catchsql { DELETE FROM i }
} {1 {foreign key constraint failed}}

# Use a collation sequence on the parent key.
drop_all_tables
do_test fkey2-1.6.1 {
  execsql {
    CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY);
    CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
    INSERT INTO i VALUES('SQLite');
    INSERT INTO j VALUES('sqlite');
  }
  catchsql { DELETE FROM i }
} {1 {foreign key constraint failed}}

# Use the parent key collation even if it is default and the child key
# has an explicit value.
drop_all_tables
do_test fkey2-1.6.2 {
  execsql {
    CREATE TABLE i(i TEXT PRIMARY KEY);        -- Colseq is "BINARY"
    CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i));
    INSERT INTO i VALUES('SQLite');
  }
  catchsql { INSERT INTO j VALUES('sqlite') }
} {1 {foreign key constraint failed}}
do_test fkey2-1.6.3 {
  execsql {
    INSERT INTO i VALUES('sqlite');
    INSERT INTO j VALUES('sqlite');
    DELETE FROM i WHERE i = 'SQLite';
  }
  catchsql { DELETE FROM i WHERE i = 'sqlite' }
} {1 {foreign key constraint failed}}
................................................................................
  execsql BEGIN;
  catchsql { UPDATE ab SET a = 5 }
} {1 {constraint failed}}
do_test fkey2-3.1.5 {
  execsql COMMIT;
  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
} {1 b 1 d 1 e}

do_test fkey2-3.2.1 {
  execsql BEGIN;
  catchsql { DELETE FROM ab }
} {1 {foreign key constraint failed}}
do_test fkey2-3.2.2 {
  execsql COMMIT
  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
................................................................................
do_test fkey2-9.1.4 {
  execsql { SELECT * FROM t1 }
} {2 two}
do_test fkey2-9.1.5 {
  catchsql { DELETE FROM t1 }
} {1 {foreign key constraint failed}}































#-------------------------------------------------------------------------
# The following tests, fkey2-10.*, test "foreign key mismatch" and 
# other errors.
#
set tn 1
foreach zSql [list {
  CREATE TABLE p(a PRIMARY KEY, b);
................................................................................
  CREATE TABLE c(x REFERENCES p(c));
} {
  CREATE TABLE c(x REFERENCES v(y));
  CREATE VIEW v AS SELECT x AS y FROM c;
} {
  CREATE TABLE p(a, b, PRIMARY KEY(a, b));
  CREATE TABLE c(x REFERENCES p);




}] {
  drop_all_tables

  do_test fkey2-10.1.$tn {
    execsql $zSql
    catchsql { INSERT INTO c DEFAULT VALUES }
  } {1 {foreign key mismatch}}
................................................................................
      x REFERENCES t1 ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED 
    );
    INSERT INTO t1 VALUES(1, 'one');
    INSERT INTO t1 VALUES(2, 'two');
    INSERT INTO t1 VALUES(3, 'three');
  }
} {}

do_test fkey2-12.1.2 { 
  execsql "BEGIN"
  execsql "INSERT INTO t2 VALUES('two')"
} {}
do_test fkey2-12.1.3 { 
  execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'"
} {}








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




|








|





|









|





|












|







|







 







>







 







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







 







>
>
>
>







 







<

} {}
foreach {tn zSql res} $FkeySimpleTests {
  if {$res == "0 {}"} { set res {0 1} }
  do_test fkey2-1.3.$tn { catchsql $zSql } $res
}
execsql { PRAGMA count_changes = 0 }
drop_all_tables

do_test fkey2-1.4.0 {
  execsql [string map {/D/ {}} $FkeySimpleSchema]
  execsql { PRAGMA count_changes = 1 }
} {}
foreach {tn zSql res} $FkeySimpleTests {
  if {$res == "0 {}"} { set res {0 1} }
  execsql BEGIN
  do_test fkey2-1.4.$tn { catchsql $zSql } $res
  execsql COMMIT
}
execsql { PRAGMA count_changes = 0 }
drop_all_tables

# Special test: When the parent key is an IPK, make sure the affinity of
# the IPK is not applied to the child key value before it is inserted
# into the child table.
do_test fkey2-1.5.1 {
  execsql {
    CREATE TABLE i(i INTEGER PRIMARY KEY);
    CREATE TABLE j(j REFERENCES i);
    INSERT INTO i VALUES(35);
    INSERT INTO j VALUES('35.0');
    SELECT j, typeof(j) FROM j;
  }
} {35.0 text}
do_test fkey2-1.5.2 {
  catchsql { DELETE FROM i }
} {1 {foreign key constraint failed}}

# Same test using a regular primary key with integer affinity.
drop_all_tables
do_test fkey2-1.6.1 {
  execsql {
    CREATE TABLE i(i INT UNIQUE);
    CREATE TABLE j(j REFERENCES i(i));
    INSERT INTO i VALUES('35.0');
    INSERT INTO j VALUES('35.0');
    SELECT j, typeof(j) FROM j;
    SELECT i, typeof(i) FROM i;
  }
} {35.0 text 35 integer}
do_test fkey2-1.6.2 {
  catchsql { DELETE FROM i }
} {1 {foreign key constraint failed}}

# Use a collation sequence on the parent key.
drop_all_tables
do_test fkey2-1.7.1 {
  execsql {
    CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY);
    CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
    INSERT INTO i VALUES('SQLite');
    INSERT INTO j VALUES('sqlite');
  }
  catchsql { DELETE FROM i }
} {1 {foreign key constraint failed}}

# Use the parent key collation even if it is default and the child key
# has an explicit value.
drop_all_tables
do_test fkey2-1.7.2 {
  execsql {
    CREATE TABLE i(i TEXT PRIMARY KEY);        -- Colseq is "BINARY"
    CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i));
    INSERT INTO i VALUES('SQLite');
  }
  catchsql { INSERT INTO j VALUES('sqlite') }
} {1 {foreign key constraint failed}}
do_test fkey2-1.7.3 {
  execsql {
    INSERT INTO i VALUES('sqlite');
    INSERT INTO j VALUES('sqlite');
    DELETE FROM i WHERE i = 'SQLite';
  }
  catchsql { DELETE FROM i WHERE i = 'sqlite' }
} {1 {foreign key constraint failed}}
................................................................................
  execsql BEGIN;
  catchsql { UPDATE ab SET a = 5 }
} {1 {constraint failed}}
do_test fkey2-3.1.5 {
  execsql COMMIT;
  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
} {1 b 1 d 1 e}

do_test fkey2-3.2.1 {
  execsql BEGIN;
  catchsql { DELETE FROM ab }
} {1 {foreign key constraint failed}}
do_test fkey2-3.2.2 {
  execsql COMMIT
  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
................................................................................
do_test fkey2-9.1.4 {
  execsql { SELECT * FROM t1 }
} {2 two}
do_test fkey2-9.1.5 {
  catchsql { DELETE FROM t1 }
} {1 {foreign key constraint failed}}

do_test fkey2-9.2.1 {
  execsql {
    CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
    CREATE TABLE cc(d DEFAULT 3, e DEFAULT 1, f DEFAULT 2,
        FOREIGN KEY(f, d) REFERENCES pp 
        ON UPDATE SET DEFAULT 
        ON DELETE SET NULL
    );
    INSERT INTO pp VALUES(1, 2, 3);
    INSERT INTO pp VALUES(4, 5, 6);
    INSERT INTO pp VALUES(7, 8, 9);
  }
} {}
do_test fkey2-9.2.2 {
  execsql {
    INSERT INTO cc VALUES(6, 'A', 5);
    INSERT INTO cc VALUES(6, 'B', 5);
    INSERT INTO cc VALUES(9, 'A', 8);
    INSERT INTO cc VALUES(9, 'B', 8);
    UPDATE pp SET b = 1 WHERE a = 7;
    SELECT * FROM cc;
  }
} {6 A 5 6 B 5 3 A 2 3 B 2}
do_test fkey2-9.2.3 {
  execsql {
    DELETE FROM pp WHERE a = 4;
    SELECT * FROM cc;
  }
} {{} A {} {} B {} 3 A 2 3 B 2}

#-------------------------------------------------------------------------
# The following tests, fkey2-10.*, test "foreign key mismatch" and 
# other errors.
#
set tn 1
foreach zSql [list {
  CREATE TABLE p(a PRIMARY KEY, b);
................................................................................
  CREATE TABLE c(x REFERENCES p(c));
} {
  CREATE TABLE c(x REFERENCES v(y));
  CREATE VIEW v AS SELECT x AS y FROM c;
} {
  CREATE TABLE p(a, b, PRIMARY KEY(a, b));
  CREATE TABLE c(x REFERENCES p);
} {
  CREATE TABLE p(a COLLATE binary, b);
  CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
  CREATE TABLE c(x REFERENCES p(a));
}] {
  drop_all_tables

  do_test fkey2-10.1.$tn {
    execsql $zSql
    catchsql { INSERT INTO c DEFAULT VALUES }
  } {1 {foreign key mismatch}}
................................................................................
      x REFERENCES t1 ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED 
    );
    INSERT INTO t1 VALUES(1, 'one');
    INSERT INTO t1 VALUES(2, 'two');
    INSERT INTO t1 VALUES(3, 'three');
  }
} {}

do_test fkey2-12.1.2 { 
  execsql "BEGIN"
  execsql "INSERT INTO t2 VALUES('two')"
} {}
do_test fkey2-12.1.3 { 
  execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'"
} {}