/ Check-in [5b4d4637]
Login

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

Overview
Comment:Ignore foreign key mismatch errors while compiling DROP TABLE commands.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:5b4d46374a8e808246a1813c12c36c4a75371898
User & Date: dan 2009-09-28 18:52:12
Context
2009-09-29
06:33
Various fixes so that "make test" works with OMIT_FOREIGN_KEY, OMIT_TRIGGER and OMIT_ALTER_TABLE. check-in: e4eb227b user: dan tags: trunk
2009-09-28
18:52
Ignore foreign key mismatch errors while compiling DROP TABLE commands. check-in: 5b4d4637 user: dan tags: trunk
14:49
Fix the DROP TABLE command so that it cannot be used to bypass foreign key constraints (if foreign key support is enabled). check-in: 8353808c user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

255
256
257
258
259
260
261

262

263
264
265
266
267
268
269
...
675
676
677
678
679
680
681

682
683
684
685
686
687
688
...
702
703
704
705
706
707
708



709

710



711
712
713
714
715
716
717
...
755
756
757
758
759
760
761
762
763
764
765



766
767
768
769
770
771
772
...
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
        }
        if( i==nCol ) break;      /* pIdx is usable */
      }
    }
  }

  if( pParse && !pIdx ){

    sqlite3ErrorMsg(pParse, "foreign key mismatch");

    sqlite3DbFree(pParse->db, aiCol);
    return 1;
  }

  *ppIdx = pIdx;
  return 0;
}
................................................................................
  int regNew                      /* New row data is stored here */
){
  sqlite3 *db = pParse->db;       /* Database handle */
  Vdbe *v;                        /* VM to write code to */
  FKey *pFKey;                    /* Used to iterate through FKs */
  int iDb;                        /* Index of database containing pTab */
  const char *zDb;                /* Name of database containing pTab */


  assert( ( pChanges &&  (regOld==0)!=(regNew==0))    /* UPDATE operation */
       || (!pChanges && !regOld &&  regNew)           /* INSERT operation */
       || (!pChanges &&  regOld && !regNew)           /* DELETE operation */
  );

  /* If foreign-keys are disabled, this function is a no-op. */
................................................................................
    int iCol;
    int i;

    /* Find the parent table of this foreign key. Also find a unique index 
    ** on the parent key columns in the parent table. If either of these 
    ** schema items cannot be located, set an error in pParse and return 
    ** early.  */



    pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb);

    if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ) return;



    assert( pFKey->nCol==1 || (aiFree && pIdx) );

    /* If the key does not overlap with the pChanges list, skip this FK. */
    if( pChanges ){
      /* TODO */
    }

................................................................................
    SrcList *pSrc;
    int *aiCol = 0;

    if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){
      assert( regOld==0 && regNew!=0 );
      /* Inserting a single row into a parent table cannot cause an immediate
      ** foreign key violation. So do nothing in this case.  */
      return;
    }

    if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return;



    assert( aiCol || pFKey->nCol==1 );

    /* Check if this update statement has modified any of the child key 
    ** columns for this foreign key constraint. If it has not, there is 
    ** no need to search the child table for rows in violation. This is
    ** just an optimization. Things would work fine without this check.  */
    if( pChanges ){
................................................................................
    FKey *p;
    int i;
    for(p=pTab->pFKey; p; p=p->pNextFrom){
      for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom);
    }
    for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
      Index *pIdx = 0;
      locateFkeyIndex(0, pTab, p, &pIdx, 0);
      if( pIdx ){
        for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
      }
    }
  }
  return mask;
}







>
|
>







 







>







 







>
>
>
|
>
|
>
>
>







 







|


|
>
>
>







 







|







255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
...
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
...
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
...
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
...
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
        }
        if( i==nCol ) break;      /* pIdx is usable */
      }
    }
  }

  if( pParse && !pIdx ){
    if( !pParse->disableTriggers ){
      sqlite3ErrorMsg(pParse, "foreign key mismatch");
    }
    sqlite3DbFree(pParse->db, aiCol);
    return 1;
  }

  *ppIdx = pIdx;
  return 0;
}
................................................................................
  int regNew                      /* New row data is stored here */
){
  sqlite3 *db = pParse->db;       /* Database handle */
  Vdbe *v;                        /* VM to write code to */
  FKey *pFKey;                    /* Used to iterate through FKs */
  int iDb;                        /* Index of database containing pTab */
  const char *zDb;                /* Name of database containing pTab */
  int isIgnoreErrors = pParse->disableTriggers;

  assert( ( pChanges &&  (regOld==0)!=(regNew==0))    /* UPDATE operation */
       || (!pChanges && !regOld &&  regNew)           /* INSERT operation */
       || (!pChanges &&  regOld && !regNew)           /* DELETE operation */
  );

  /* If foreign-keys are disabled, this function is a no-op. */
................................................................................
    int iCol;
    int i;

    /* Find the parent table of this foreign key. Also find a unique index 
    ** on the parent key columns in the parent table. If either of these 
    ** schema items cannot be located, set an error in pParse and return 
    ** early.  */
    if( pParse->disableTriggers ){
      pTo = sqlite3FindTable(db, pFKey->zTo, zDb);
    }else{
      pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb);
    }
    if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){
      if( !isIgnoreErrors || db->mallocFailed ) return;
      continue;
    }
    assert( pFKey->nCol==1 || (aiFree && pIdx) );

    /* If the key does not overlap with the pChanges list, skip this FK. */
    if( pChanges ){
      /* TODO */
    }

................................................................................
    SrcList *pSrc;
    int *aiCol = 0;

    if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){
      assert( regOld==0 && regNew!=0 );
      /* Inserting a single row into a parent table cannot cause an immediate
      ** foreign key violation. So do nothing in this case.  */
      continue;
    }

    if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){
      if( !isIgnoreErrors || db->mallocFailed ) return;
      continue;
    }
    assert( aiCol || pFKey->nCol==1 );

    /* Check if this update statement has modified any of the child key 
    ** columns for this foreign key constraint. If it has not, there is 
    ** no need to search the child table for rows in violation. This is
    ** just an optimization. Things would work fine without this check.  */
    if( pChanges ){
................................................................................
    FKey *p;
    int i;
    for(p=pTab->pFKey; p; p=p->pNextFrom){
      for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom);
    }
    for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
      Index *pIdx = 0;
      locateFkeyIndex(pParse, pTab, p, &pIdx, 0);
      if( pIdx ){
        for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
      }
    }
  }
  return mask;
}

Changes to test/fkey2.test.

83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
...
871
872
873
874
875
876
877






















































878
879
880
881
882
883
884
    execsql "DROP TABLE $t"
  }
  execsql { PRAGMA foreign_keys = ON }
}

execsql { PRAGMA foreign_keys = on }

if 0 {
execsql {
  CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1);
  INSERT INTO t1 VALUES('aaa', 'aaa');
}
puts XXX
explain { UPDATE t1 SET a = 'bbb' }
execsql { PRAGMA vdbe_trace = 1 }
execsql { UPDATE t1 SET a = 'bbb' }
exit
}

set FkeySimpleSchema {
  PRAGMA foreign_keys = on;
  CREATE TABLE t1(a PRIMARY KEY, b);
  CREATE TABLE t2(c REFERENCES t1(a) /D/ , d);

  CREATE TABLE t3(a PRIMARY KEY, b);
  CREATE TABLE t4(c REFERENCES t3 /D/, d);
................................................................................
} {1 {foreign key constraint failed}}
do_test fkey2-14.2.2.6 {
  catchsql { UPDATE t4 SET b = 1 }
} {0 {}}
do_test fkey2-14.2.2.7 {
  execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
} {}























































#-------------------------------------------------------------------------
# The following tests, fkey2-15.*, test that unnecessary FK related scans 
# and lookups are avoided when the constraint counters are zero.
#
drop_all_tables
proc execsqlS {zSql} {







<
<
<
<
<
<
<
<
<
<
<
<







 







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







83
84
85
86
87
88
89












90
91
92
93
94
95
96
...
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
    execsql "DROP TABLE $t"
  }
  execsql { PRAGMA foreign_keys = ON }
}

execsql { PRAGMA foreign_keys = on }













set FkeySimpleSchema {
  PRAGMA foreign_keys = on;
  CREATE TABLE t1(a PRIMARY KEY, b);
  CREATE TABLE t2(c REFERENCES t1(a) /D/ , d);

  CREATE TABLE t3(a PRIMARY KEY, b);
  CREATE TABLE t4(c REFERENCES t3 /D/, d);
................................................................................
} {1 {foreign key constraint failed}}
do_test fkey2-14.2.2.6 {
  catchsql { UPDATE t4 SET b = 1 }
} {0 {}}
do_test fkey2-14.2.2.7 {
  execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
} {}

do_test fkey-2.14.3.1 {
  drop_all_tables
  execsql {
    CREATE TABLE t1(a, b REFERENCES nosuchtable);
    DROP TABLE t1;
  }
} {}
do_test fkey-2.14.3.2 {
  execsql {
    CREATE TABLE t1(a PRIMARY KEY, b);
    INSERT INTO t1 VALUES('a', 1);
    CREATE TABLE t2(x REFERENCES t1);
    INSERT INTO t2 VALUES('a');
  }
} {}
do_test fkey-2.14.3.3 {
  catchsql { DROP TABLE t1 }
} {1 {foreign key constraint failed}}
do_test fkey-2.14.3.4 {
  execsql {
    DELETE FROM t2;
    DROP TABLE t1;
  }
} {}
do_test fkey-2.14.3.4 {
  catchsql { INSERT INTO t2 VALUES('x') }
} {1 {no such table: main.t1}}
do_test fkey-2.14.3.5 {
  execsql {
    CREATE TABLE t1(x PRIMARY KEY);
    INSERT INTO t1 VALUES('x');
  }
  execsql { INSERT INTO t2 VALUES('x') }
} {}
do_test fkey-2.14.3.6 {
  catchsql { DROP TABLE t1 }
} {1 {foreign key constraint failed}}
do_test fkey-2.14.3.7 {
  execsql {
    DROP TABLE t2;
    DROP TABLE t1;
  }
} {}
do_test fkey-2.14.3.8 {
  execsql {
    CREATE TABLE pp(x, y, PRIMARY KEY(x, y));
    CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
  }
  catchsql { INSERT INTO cc VALUES(1, 2) }
} {1 {foreign key mismatch}}
do_test fkey-2.14.3.9 {
  execsql { DROP TABLE cc }
} {}

#-------------------------------------------------------------------------
# The following tests, fkey2-15.*, test that unnecessary FK related scans 
# and lookups are avoided when the constraint counters are zero.
#
drop_all_tables
proc execsqlS {zSql} {