/ Check-in [e408dc90]
Login

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

Overview
Comment:Add PRAGMA foreign_key_check. Name the child and parent tables as part of a "foreign key mismatch" error.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:e408dc9080594dc464b8763dece6b365772c6105
User & Date: drh 2012-12-20 01:15:20
Context
2012-12-21
16:15
Ensure the database size field in the db header of a backup database is set correctly. Fix for [0cfd98ee201]. check-in: ff6857b6 user: dan tags: trunk
2012-12-20
01:15
Add PRAGMA foreign_key_check. Name the child and parent tables as part of a "foreign key mismatch" error. check-in: e408dc90 user: drh tags: trunk
00:32
Remove an unreachable branch operation from the foreign_key_check pragma. Closed-Leaf check-in: b5a8f316 user: drh tags: foreign-key-check
2012-12-19
16:58
Fix the segfault problem of ticket [a7b7803e8d1e869] which involved the use of "AS" named result columns as logical terms of the WHERE clause. check-in: b3c9e8f8 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
...
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
...
271
272
273
274
275
276
277
278


279
280
281
282
283
284
285
...
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
...
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
...
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
...
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
**   Register (x+3):      3.1  (type real)
*/

/*
** A foreign key constraint requires that the key columns in the parent
** table are collectively subject to a UNIQUE or PRIMARY KEY constraint.
** Given that pParent is the parent table for foreign key constraint pFKey, 
** search the schema a unique index on the parent key columns. 
**
** If successful, zero is returned. If the parent key is an INTEGER PRIMARY 
** KEY column, then output variable *ppIdx is set to NULL. Otherwise, *ppIdx 
** is set to point to the unique index. 
** 
** If the parent key consists of a single column (the foreign key constraint
** is not a composite foreign key), output variable *paiCol is set to NULL.
................................................................................
**      consists of a a different number of columns to the child key in 
**      the child table.
**
** then non-zero is returned, and a "foreign key mismatch" error loaded
** into pParse. If an OOM error occurs, non-zero is returned and the
** pParse->db->mallocFailed flag is set.
*/
static int locateFkeyIndex(
  Parse *pParse,                  /* Parse context to store any error in */
  Table *pParent,                 /* Parent table of FK constraint pFKey */
  FKey *pFKey,                    /* Foreign key to find index for */
  Index **ppIdx,                  /* OUT: Unique index on parent table */
  int **paiCol                    /* OUT: Map of index columns in pFKey */
){
  Index *pIdx = 0;                    /* Value to return via *ppIdx */
................................................................................
        if( i==nCol ) break;      /* pIdx is usable */
      }
    }
  }

  if( !pIdx ){
    if( !pParse->disableTriggers ){
      sqlite3ErrorMsg(pParse, "foreign key mismatch");


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

  *ppIdx = pIdx;
  return 0;
................................................................................
    ** 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) ){
      assert( isIgnoreErrors==0 || (regOld!=0 && regNew==0) );
      if( !isIgnoreErrors || db->mallocFailed ) return;
      if( pTo==0 ){
        /* If isIgnoreErrors is true, then a table is being dropped. In this
        ** case SQLite runs a "DELETE FROM xxx" on the table being dropped
        ** before actually dropping it in order to check FK constraints.
        ** If the parent table of an FK constraint on the current table is
................................................................................
    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 );

    /* Create a SrcList structure containing a single table (the table 
    ** the foreign key that refers to this table is attached to). This
................................................................................
    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;
}
................................................................................
    TriggerStep *pStep = 0;        /* First (only) step of trigger program */
    Expr *pWhere = 0;             /* WHERE clause of trigger step */
    ExprList *pList = 0;          /* Changes list if ON UPDATE CASCADE */
    Select *pSelect = 0;          /* If RESTRICT, "SELECT RAISE(...)" */
    int i;                        /* Iterator variable */
    Expr *pWhen = 0;              /* WHEN clause for the trigger */

    if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0;
    assert( aiCol || pFKey->nCol==1 );

    for(i=0; i<pFKey->nCol; i++){
      Token tOld = { "old", 3 };  /* Literal "old" token */
      Token tNew = { "new", 3 };  /* Literal "new" token */
      Token tFromCol;             /* Name of column in child table */
      Token tToCol;               /* Name of column in parent table */







|







 







|







 







|
>
>







 







|







 







|







 







|







 







|







138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
...
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
...
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
...
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
...
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
...
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
...
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
**   Register (x+3):      3.1  (type real)
*/

/*
** A foreign key constraint requires that the key columns in the parent
** table are collectively subject to a UNIQUE or PRIMARY KEY constraint.
** Given that pParent is the parent table for foreign key constraint pFKey, 
** search the schema for a unique index on the parent key columns. 
**
** If successful, zero is returned. If the parent key is an INTEGER PRIMARY 
** KEY column, then output variable *ppIdx is set to NULL. Otherwise, *ppIdx 
** is set to point to the unique index. 
** 
** If the parent key consists of a single column (the foreign key constraint
** is not a composite foreign key), output variable *paiCol is set to NULL.
................................................................................
**      consists of a a different number of columns to the child key in 
**      the child table.
**
** then non-zero is returned, and a "foreign key mismatch" error loaded
** into pParse. If an OOM error occurs, non-zero is returned and the
** pParse->db->mallocFailed flag is set.
*/
int sqlite3FkLocateIndex(
  Parse *pParse,                  /* Parse context to store any error in */
  Table *pParent,                 /* Parent table of FK constraint pFKey */
  FKey *pFKey,                    /* Foreign key to find index for */
  Index **ppIdx,                  /* OUT: Unique index on parent table */
  int **paiCol                    /* OUT: Map of index columns in pFKey */
){
  Index *pIdx = 0;                    /* Value to return via *ppIdx */
................................................................................
        if( i==nCol ) break;      /* pIdx is usable */
      }
    }
  }

  if( !pIdx ){
    if( !pParse->disableTriggers ){
      sqlite3ErrorMsg(pParse,
           "foreign key mismatch - \"%w\" referencing \"%w\"",
           pFKey->pFrom->zName, pFKey->zTo);
    }
    sqlite3DbFree(pParse->db, aiCol);
    return 1;
  }

  *ppIdx = pIdx;
  return 0;
................................................................................
    ** 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 || sqlite3FkLocateIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){
      assert( isIgnoreErrors==0 || (regOld!=0 && regNew==0) );
      if( !isIgnoreErrors || db->mallocFailed ) return;
      if( pTo==0 ){
        /* If isIgnoreErrors is true, then a table is being dropped. In this
        ** case SQLite runs a "DELETE FROM xxx" on the table being dropped
        ** before actually dropping it in order to check FK constraints.
        ** If the parent table of an FK constraint on the current table is
................................................................................
    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( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){
      if( !isIgnoreErrors || db->mallocFailed ) return;
      continue;
    }
    assert( aiCol || pFKey->nCol==1 );

    /* Create a SrcList structure containing a single table (the table 
    ** the foreign key that refers to this table is attached to). This
................................................................................
    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;
      sqlite3FkLocateIndex(pParse, pTab, p, &pIdx, 0);
      if( pIdx ){
        for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
      }
    }
  }
  return mask;
}
................................................................................
    TriggerStep *pStep = 0;        /* First (only) step of trigger program */
    Expr *pWhere = 0;             /* WHERE clause of trigger step */
    ExprList *pList = 0;          /* Changes list if ON UPDATE CASCADE */
    Select *pSelect = 0;          /* If RESTRICT, "SELECT RAISE(...)" */
    int i;                        /* Iterator variable */
    Expr *pWhen = 0;              /* WHEN clause for the trigger */

    if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0;
    assert( aiCol || pFKey->nCol==1 );

    for(i=0; i<pFKey->nCol; i++){
      Token tOld = { "old", 3 };  /* Literal "old" token */
      Token tNew = { "new", 3 };  /* Literal "new" token */
      Token tFromCol;             /* Name of column in child table */
      Token tToCol;               /* Name of column in parent table */

Changes to src/pragma.c.

1107
1108
1109
1110
1111
1112
1113


















































































































1114
1115
1116
1117
1118
1119
1120
            sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 8);
          }
          ++i;
          pFK = pFK->pNextFrom;
        }
      }
    }


















































































































  }else
#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */

#ifndef NDEBUG
  if( sqlite3StrICmp(zLeft, "parser_trace")==0 ){
    if( zRight ){
      if( sqlite3GetBoolean(zRight, 0) ){







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







1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
            sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 8);
          }
          ++i;
          pFK = pFK->pNextFrom;
        }
      }
    }
  }else
#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */

#ifndef SQLITE_OMIT_FOREIGN_KEY
  if( sqlite3StrICmp(zLeft, "foreign_key_check")==0 ){
    FKey *pFK;             /* A foreign key constraint */
    Table *pTab;           /* Child table contain "REFERENCES" keyword */
    Table *pParent;        /* Parent table that child points to */
    Index *pIdx;           /* Index in the parent table */
    int i;                 /* Loop counter:  Foreign key number for pTab */
    int j;                 /* Loop counter:  Field of the foreign key */
    HashElem *k;           /* Loop counter:  Next table in schema */
    int x;                 /* result variable */
    int regResult;         /* 3 registers to hold a result row */
    int regKey;            /* Register to hold key for checking the FK */
    int regRow;            /* Registers to hold a row from pTab */
    int addrTop;           /* Top of a loop checking foreign keys */
    int addrOk;            /* Jump here if the key is OK */
    int *aiCols;           /* child to parent column mapping */

    if( sqlite3ReadSchema(pParse) ) goto pragma_out;
    regResult = pParse->nMem+1;
    pParse->nMem += 4;
    regKey = ++pParse->nMem;
    regRow = ++pParse->nMem;
    v = sqlite3GetVdbe(pParse);
    sqlite3VdbeSetNumCols(v, 4);
    sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "table", SQLITE_STATIC);
    sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "rowid", SQLITE_STATIC);
    sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "parent", SQLITE_STATIC);
    sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "fkid", SQLITE_STATIC);
    sqlite3CodeVerifySchema(pParse, iDb);
    k = sqliteHashFirst(&db->aDb[iDb].pSchema->tblHash);
    while( k ){
      if( zRight ){
        pTab = sqlite3LocateTable(pParse, 0, zRight, zDb);
        k = 0;
      }else{
        pTab = (Table*)sqliteHashData(k);
        k = sqliteHashNext(k);
      }
      if( pTab==0 || pTab->pFKey==0 ) continue;
      sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
      if( pTab->nCol+regRow>pParse->nMem ) pParse->nMem = pTab->nCol + regRow;
      sqlite3OpenTable(pParse, 0, iDb, pTab, OP_OpenRead);
      sqlite3VdbeAddOp4(v, OP_String8, 0, regResult, 0, pTab->zName,
                        P4_TRANSIENT);
      for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
        pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
        if( pParent==0 ) break;
        pIdx = 0;
        sqlite3TableLock(pParse, iDb, pParent->tnum, 0, pParent->zName);
        x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, 0);
        if( x==0 ){
          if( pIdx==0 ){
            sqlite3OpenTable(pParse, i, iDb, pParent, OP_OpenRead);
          }else{
            KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
            sqlite3VdbeAddOp3(v, OP_OpenRead, i, pIdx->tnum, iDb);
            sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);
          }
        }else{
          k = 0;
          break;
        }
      }
      if( pFK ) break;
      if( pParse->nTab<i ) pParse->nTab = i;
      addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, 0);
      for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
        pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
        assert( pParent!=0 );
        pIdx = 0;
        aiCols = 0;
        x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols);
        assert( x==0 );
        addrOk = sqlite3VdbeMakeLabel(v);
        if( pIdx==0 ){
          int iKey = pFK->aCol[0].iFrom;
          assert( iKey>=0 && iKey<pTab->nCol );
          if( iKey!=pTab->iPKey ){
            sqlite3VdbeAddOp3(v, OP_Column, 0, iKey, regRow);
            sqlite3ColumnDefault(v, pTab, iKey, regRow);
            sqlite3VdbeAddOp2(v, OP_IsNull, regRow, addrOk);
            sqlite3VdbeAddOp2(v, OP_MustBeInt, regRow,
               sqlite3VdbeCurrentAddr(v)+3);
          }else{
            sqlite3VdbeAddOp2(v, OP_Rowid, 0, regRow);
          }
          sqlite3VdbeAddOp3(v, OP_NotExists, i, 0, regRow);
          sqlite3VdbeAddOp2(v, OP_Goto, 0, addrOk);
          sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
        }else{
          for(j=0; j<pFK->nCol; j++){
            sqlite3ExprCodeGetColumnOfTable(v, pTab, 0,
                            aiCols ? aiCols[j] : pFK->aCol[0].iFrom, regRow+j);
            sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk);
          }
          sqlite3VdbeAddOp3(v, OP_MakeRecord, regRow, pFK->nCol, regKey);
          sqlite3VdbeChangeP4(v, -1,
                   sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT);
          sqlite3VdbeAddOp4Int(v, OP_Found, i, addrOk, regKey, 0);
        }
        sqlite3VdbeAddOp2(v, OP_Rowid, 0, regResult+1);
        sqlite3VdbeAddOp4(v, OP_String8, 0, regResult+2, 0, 
                          pFK->zTo, P4_TRANSIENT);
        sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult+3);
        sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 4);
        sqlite3VdbeResolveLabel(v, addrOk);
        sqlite3DbFree(db, aiCols);
      }
      sqlite3VdbeAddOp2(v, OP_Next, 0, addrTop+1);
      sqlite3VdbeJumpHere(v, addrTop);
    }
  }else
#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */

#ifndef NDEBUG
  if( sqlite3StrICmp(zLeft, "parser_trace")==0 ){
    if( zRight ){
      if( sqlite3GetBoolean(zRight, 0) ){

Changes to src/sqliteInt.h.

3206
3207
3208
3209
3210
3211
3212

3213
3214

3215
3216
3217
3218
3219
3220
3221
  #define sqlite3FkCheck(a,b,c,d)
  #define sqlite3FkDropTable(a,b,c)
  #define sqlite3FkOldmask(a,b)      0
  #define sqlite3FkRequired(a,b,c,d) 0
#endif
#ifndef SQLITE_OMIT_FOREIGN_KEY
  void sqlite3FkDelete(sqlite3 *, Table*);

#else
  #define sqlite3FkDelete(a,b)

#endif


/*
** Available fault injectors.  Should be numbered beginning with 0.
*/
#define SQLITE_FAULTINJECTOR_MALLOC     0







>


>







3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
3219
3220
3221
3222
3223
  #define sqlite3FkCheck(a,b,c,d)
  #define sqlite3FkDropTable(a,b,c)
  #define sqlite3FkOldmask(a,b)      0
  #define sqlite3FkRequired(a,b,c,d) 0
#endif
#ifndef SQLITE_OMIT_FOREIGN_KEY
  void sqlite3FkDelete(sqlite3 *, Table*);
  int sqlite3FkLocateIndex(Parse*,Table*,FKey*,Index**,int**);
#else
  #define sqlite3FkDelete(a,b)
  #define sqlite3FkLocateIndex(a,b,c,d,e)
#endif


/*
** Available fault injectors.  Should be numbered beginning with 0.
*/
#define SQLITE_FAULTINJECTOR_MALLOC     0

Changes to test/e_fkey.test.

623
624
625
626
627
628
629
630

631
632
633
634
635
636
637
...
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
...
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
...
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
....
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
....
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
  }
} {}
proc test_efkey_57 {tn isError sql} {
  catchsql { DROP TABLE t1 }
  execsql $sql
  do_test e_fkey-18.$tn {
    catchsql { INSERT INTO t2 VALUES(NULL) }
  } [lindex {{0 {}} {1 {foreign key mismatch}}} $isError]

}
test_efkey_57 2 0 { CREATE TABLE t1(x PRIMARY KEY) }
test_efkey_57 3 0 { CREATE TABLE t1(x UNIQUE) }
test_efkey_57 4 0 { CREATE TABLE t1(x); CREATE UNIQUE INDEX t1i ON t1(x) }
test_efkey_57 5 1 { 
  CREATE TABLE t1(x); 
  CREATE UNIQUE INDEX t1i ON t1(x COLLATE nocase);
................................................................................
    INSERT INTO child1 VALUES('xxx', 1);
    INSERT INTO child2 VALUES('xxx', 2);
    INSERT INTO child3 VALUES(3, 4);
  }
} {}
do_test e_fkey-19.2 {
  catchsql { INSERT INTO child4 VALUES('xxx', 5) }
} {1 {foreign key mismatch}}
do_test e_fkey-19.3 {
  catchsql { INSERT INTO child5 VALUES('xxx', 6) }
} {1 {foreign key mismatch}}
do_test e_fkey-19.4 {
  catchsql { INSERT INTO child6 VALUES(2, 3) }
} {1 {foreign key mismatch}}
do_test e_fkey-19.5 {
  catchsql { INSERT INTO child7 VALUES(3) }
} {1 {foreign key mismatch}}

#-------------------------------------------------------------------------
# Test errors in the database schema that are detected while preparing
# DML statements. The error text for these messages always matches 
# either "foreign key mismatch" or "no such table*" (using [string match]).
#
# EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key
................................................................................
    CREATE TABLE p7(a, b, PRIMARY KEY(a, b));
    CREATE TABLE c7(c, d REFERENCES p7);
  }
} {}

foreach {tn tbl ptbl err} {
  2 c1 {} "no such table: main.nosuchtable"
  3 c2 p2 "foreign key mismatch"
  4 c3 p3 "foreign key mismatch"
  5 c4 p4 "foreign key mismatch"
  6 c5 p5 "foreign key mismatch"
  7 c6 p6 "foreign key mismatch"
  8 c7 p7 "foreign key mismatch"
} {
  do_test e_fkey-20.$tn.1 {
    catchsql "INSERT INTO $tbl VALUES('a', 'b')"
  } [list 1 $err]
  do_test e_fkey-20.$tn.2 {
    catchsql "UPDATE $tbl SET c = ?, d = ?"
  } [list 1 $err]
................................................................................
  execsql {
    INSERT INTO parent2 VALUES('I', 'II');
    INSERT INTO child8 VALUES('I', 'II');
  }
} {}
do_test e_fkey-21.3 {
  catchsql { INSERT INTO child9 VALUES('I') }
} {1 {foreign key mismatch}}
do_test e_fkey-21.4 {
  catchsql { INSERT INTO child9 VALUES('II') }
} {1 {foreign key mismatch}}
do_test e_fkey-21.5 {
  catchsql { INSERT INTO child9 VALUES(NULL) }
} {1 {foreign key mismatch}}
do_test e_fkey-21.6 {
  catchsql { INSERT INTO child10 VALUES('I', 'II', 'III') }
} {1 {foreign key mismatch}}
do_test e_fkey-21.7 {
  catchsql { INSERT INTO child10 VALUES(1, 2, 3) }
} {1 {foreign key mismatch}}
do_test e_fkey-21.8 {
  catchsql { INSERT INTO child10 VALUES(NULL, NULL, NULL) }
} {1 {foreign key mismatch}}

#-------------------------------------------------------------------------
# Test errors that are reported when creating the child table. 
# Specifically:
#
#   * different number of child and parent key columns, and
#   * child columns that do not exist.
................................................................................
do_test e_fkey-28.8 {
  drop_all_tables
  execsql {
    CREATE TABLE p(x PRIMARY KEY);
    CREATE TABLE c(a, b, FOREIGN KEY(a,b) REFERENCES p);
  }
  catchsql {DELETE FROM p}
} {1 {foreign key mismatch}}
do_test e_fkey-28.9 {
  drop_all_tables
  execsql {
    CREATE TABLE p(x, y, PRIMARY KEY(x,y));
    CREATE TABLE c(a REFERENCES p);
  }
  catchsql {DELETE FROM p}
} {1 {foreign key mismatch}}


#-------------------------------------------------------------------------
# EVIDENCE-OF: R-24676-09859
#
# Test the example schema in the "Composite Foreign Key Constraints" 
# section.
................................................................................
      SELECT * FROM c3;
    ROLLBACK;
  }
} {{} 2}
do_test e_fkey-60.4 {
  execsql { CREATE TABLE nosuchtable(x PRIMARY KEY) }
  catchsql { DELETE FROM p }
} {1 {foreign key mismatch}}
do_test e_fkey-60.5 {
  execsql { DROP TABLE c1 }
  catchsql { DELETE FROM p }
} {1 {foreign key mismatch}}
do_test e_fkey-60.6 {
  execsql { DROP TABLE c2 }
  execsql { DELETE FROM p }
} {}

#-------------------------------------------------------------------------
# Test that the special behaviours of ALTER and DROP TABLE are only







|
>







 







|


|


|


|







 







|
|
|
|
|
|







 







|


|


|


|


|


|







 







|







|







 







|



|







623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
...
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
...
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
...
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
....
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
....
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
  }
} {}
proc test_efkey_57 {tn isError sql} {
  catchsql { DROP TABLE t1 }
  execsql $sql
  do_test e_fkey-18.$tn {
    catchsql { INSERT INTO t2 VALUES(NULL) }
  } [lindex {{0 {}} {/1 {foreign key mismatch - ".*" referencing ".*"}/}} \
     $isError]
}
test_efkey_57 2 0 { CREATE TABLE t1(x PRIMARY KEY) }
test_efkey_57 3 0 { CREATE TABLE t1(x UNIQUE) }
test_efkey_57 4 0 { CREATE TABLE t1(x); CREATE UNIQUE INDEX t1i ON t1(x) }
test_efkey_57 5 1 { 
  CREATE TABLE t1(x); 
  CREATE UNIQUE INDEX t1i ON t1(x COLLATE nocase);
................................................................................
    INSERT INTO child1 VALUES('xxx', 1);
    INSERT INTO child2 VALUES('xxx', 2);
    INSERT INTO child3 VALUES(3, 4);
  }
} {}
do_test e_fkey-19.2 {
  catchsql { INSERT INTO child4 VALUES('xxx', 5) }
} {1 {foreign key mismatch - "child4" referencing "parent"}}
do_test e_fkey-19.3 {
  catchsql { INSERT INTO child5 VALUES('xxx', 6) }
} {1 {foreign key mismatch - "child5" referencing "parent"}}
do_test e_fkey-19.4 {
  catchsql { INSERT INTO child6 VALUES(2, 3) }
} {1 {foreign key mismatch - "child6" referencing "parent"}}
do_test e_fkey-19.5 {
  catchsql { INSERT INTO child7 VALUES(3) }
} {1 {foreign key mismatch - "child7" referencing "parent"}}

#-------------------------------------------------------------------------
# Test errors in the database schema that are detected while preparing
# DML statements. The error text for these messages always matches 
# either "foreign key mismatch" or "no such table*" (using [string match]).
#
# EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key
................................................................................
    CREATE TABLE p7(a, b, PRIMARY KEY(a, b));
    CREATE TABLE c7(c, d REFERENCES p7);
  }
} {}

foreach {tn tbl ptbl err} {
  2 c1 {} "no such table: main.nosuchtable"
  3 c2 p2 "foreign key mismatch - \"c2\" referencing \"p2\""
  4 c3 p3 "foreign key mismatch - \"c3\" referencing \"p3\""
  5 c4 p4 "foreign key mismatch - \"c4\" referencing \"p4\""
  6 c5 p5 "foreign key mismatch - \"c5\" referencing \"p5\""
  7 c6 p6 "foreign key mismatch - \"c6\" referencing \"p6\""
  8 c7 p7 "foreign key mismatch - \"c7\" referencing \"p7\""
} {
  do_test e_fkey-20.$tn.1 {
    catchsql "INSERT INTO $tbl VALUES('a', 'b')"
  } [list 1 $err]
  do_test e_fkey-20.$tn.2 {
    catchsql "UPDATE $tbl SET c = ?, d = ?"
  } [list 1 $err]
................................................................................
  execsql {
    INSERT INTO parent2 VALUES('I', 'II');
    INSERT INTO child8 VALUES('I', 'II');
  }
} {}
do_test e_fkey-21.3 {
  catchsql { INSERT INTO child9 VALUES('I') }
} {1 {foreign key mismatch - "child9" referencing "parent2"}}
do_test e_fkey-21.4 {
  catchsql { INSERT INTO child9 VALUES('II') }
} {1 {foreign key mismatch - "child9" referencing "parent2"}}
do_test e_fkey-21.5 {
  catchsql { INSERT INTO child9 VALUES(NULL) }
} {1 {foreign key mismatch - "child9" referencing "parent2"}}
do_test e_fkey-21.6 {
  catchsql { INSERT INTO child10 VALUES('I', 'II', 'III') }
} {1 {foreign key mismatch - "child10" referencing "parent2"}}
do_test e_fkey-21.7 {
  catchsql { INSERT INTO child10 VALUES(1, 2, 3) }
} {1 {foreign key mismatch - "child10" referencing "parent2"}}
do_test e_fkey-21.8 {
  catchsql { INSERT INTO child10 VALUES(NULL, NULL, NULL) }
} {1 {foreign key mismatch - "child10" referencing "parent2"}}

#-------------------------------------------------------------------------
# Test errors that are reported when creating the child table. 
# Specifically:
#
#   * different number of child and parent key columns, and
#   * child columns that do not exist.
................................................................................
do_test e_fkey-28.8 {
  drop_all_tables
  execsql {
    CREATE TABLE p(x PRIMARY KEY);
    CREATE TABLE c(a, b, FOREIGN KEY(a,b) REFERENCES p);
  }
  catchsql {DELETE FROM p}
} {1 {foreign key mismatch - "c" referencing "p"}}
do_test e_fkey-28.9 {
  drop_all_tables
  execsql {
    CREATE TABLE p(x, y, PRIMARY KEY(x,y));
    CREATE TABLE c(a REFERENCES p);
  }
  catchsql {DELETE FROM p}
} {1 {foreign key mismatch - "c" referencing "p"}}


#-------------------------------------------------------------------------
# EVIDENCE-OF: R-24676-09859
#
# Test the example schema in the "Composite Foreign Key Constraints" 
# section.
................................................................................
      SELECT * FROM c3;
    ROLLBACK;
  }
} {{} 2}
do_test e_fkey-60.4 {
  execsql { CREATE TABLE nosuchtable(x PRIMARY KEY) }
  catchsql { DELETE FROM p }
} {1 {foreign key mismatch - "c2" referencing "p"}}
do_test e_fkey-60.5 {
  execsql { DROP TABLE c1 }
  catchsql { DELETE FROM p }
} {1 {foreign key mismatch - "c2" referencing "p"}}
do_test e_fkey-60.6 {
  execsql { DROP TABLE c2 }
  execsql { DELETE FROM p }
} {}

#-------------------------------------------------------------------------
# Test that the special behaviours of ALTER and DROP TABLE are only

Changes to test/fkey2.test.

135
136
137
138
139
140
141
142

143
144
145
146
147
148
149






150
151
152
153
154
155
156
157






158
159
160
161
162
163
164
165
166
167






168
169
170
171
172
173
174
...
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
....
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
  4.13 "UPDATE t7 SET b = 1"              {0 {}}
  4.14 "INSERT INTO t8 VALUES('a', 'b')"  {1 {foreign key constraint failed}}
  4.15 "UPDATE t7 SET b = 5"              {1 {foreign key constraint failed}}
  4.16 "UPDATE t7 SET rowid = 5"          {1 {foreign key constraint failed}}
  4.17 "UPDATE t7 SET a = 10"             {0 {}}

  5.1  "INSERT INTO t9 VALUES(1, 3)"      {1 {no such table: main.nosuchtable}}
  5.2  "INSERT INTO t10 VALUES(1, 3)"     {1 {foreign key mismatch}}

}

do_test fkey2-1.1.0 {
  execsql [string map {/D/ {}} $FkeySimpleSchema]
} {}
foreach {tn zSql res} $FkeySimpleTests {
  do_test fkey2-1.1.$tn { catchsql $zSql } $res






}
drop_all_tables

do_test fkey2-1.2.0 {
  execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema]
} {}
foreach {tn zSql res} $FkeySimpleTests {
  do_test fkey2-1.2.$tn { catchsql $zSql } $res






}
drop_all_tables

do_test fkey2-1.3.0 {
  execsql [string map {/D/ {}} $FkeySimpleSchema]
  execsql { PRAGMA count_changes = 1 }
} {}
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 }
................................................................................
  CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
  CREATE TABLE c(x REFERENCES p(a));
}] {
  drop_all_tables
  do_test fkey2-10.1.[incr tn] {
    execsql $zSql
    catchsql { INSERT INTO c DEFAULT VALUES }
  } {1 {foreign key mismatch}}
}

# "rowid" cannot be used as part of a child or parent key definition 
# unless it happens to be the name of an explicitly declared column.
#
do_test fkey2-10.2.1 {
  drop_all_tables
................................................................................
  drop_all_tables
  catchsql {
    CREATE TABLE t1(a, b);
    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
    INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1);
    INSERT INTO t2 VALUES(1, 1);
  }
} {1 {foreign key mismatch}}
do_test fkey2-10.2.2 {
  drop_all_tables
  catchsql {
    CREATE TABLE t1(rowid PRIMARY KEY, b);
    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
    INSERT INTO t1(rowid, b) VALUES(1, 1);
    INSERT INTO t2 VALUES(1, 1);
................................................................................
} {}
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 }
} {}
do_test fkey-2.14.3.10 {
  execsql {
    CREATE TABLE cc(a, b, 
      FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED







|
>






|
>
>
>
>
>
>








>
>
>
>
>
>










>
>
>
>
>
>







 







|







 







|







 







|







135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
...
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
...
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
....
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
  4.13 "UPDATE t7 SET b = 1"              {0 {}}
  4.14 "INSERT INTO t8 VALUES('a', 'b')"  {1 {foreign key constraint failed}}
  4.15 "UPDATE t7 SET b = 5"              {1 {foreign key constraint failed}}
  4.16 "UPDATE t7 SET rowid = 5"          {1 {foreign key constraint failed}}
  4.17 "UPDATE t7 SET a = 10"             {0 {}}

  5.1  "INSERT INTO t9 VALUES(1, 3)"      {1 {no such table: main.nosuchtable}}
  5.2  "INSERT INTO t10 VALUES(1, 3)"  
                            {1 {foreign key mismatch - "t10" referencing "t9"}}
}

do_test fkey2-1.1.0 {
  execsql [string map {/D/ {}} $FkeySimpleSchema]
} {}
foreach {tn zSql res} $FkeySimpleTests {
  do_test fkey2-1.1.$tn.1 { catchsql $zSql } $res
  do_test fkey2-1.1.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
  do_test fkey2-1.1.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
  do_test fkey2-1.1.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
  do_test fkey2-1.1.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
  do_test fkey2-1.1.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
  do_test fkey2-1.1.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
}
drop_all_tables

do_test fkey2-1.2.0 {
  execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema]
} {}
foreach {tn zSql res} $FkeySimpleTests {
  do_test fkey2-1.2.$tn { catchsql $zSql } $res
  do_test fkey2-1.2.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
  do_test fkey2-1.2.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
  do_test fkey2-1.2.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
  do_test fkey2-1.2.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
  do_test fkey2-1.2.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
  do_test fkey2-1.2.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
}
drop_all_tables

do_test fkey2-1.3.0 {
  execsql [string map {/D/ {}} $FkeySimpleSchema]
  execsql { PRAGMA count_changes = 1 }
} {}
foreach {tn zSql res} $FkeySimpleTests {
  if {$res == "0 {}"} { set res {0 1} }
  do_test fkey2-1.3.$tn { catchsql $zSql } $res
  do_test fkey2-1.3.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
  do_test fkey2-1.3.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
  do_test fkey2-1.3.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
  do_test fkey2-1.3.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
  do_test fkey2-1.3.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
  do_test fkey2-1.3.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
}
execsql { PRAGMA count_changes = 0 }
drop_all_tables

do_test fkey2-1.4.0 {
  execsql [string map {/D/ {}} $FkeySimpleSchema]
  execsql { PRAGMA count_changes = 1 }
................................................................................
  CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
  CREATE TABLE c(x REFERENCES p(a));
}] {
  drop_all_tables
  do_test fkey2-10.1.[incr tn] {
    execsql $zSql
    catchsql { INSERT INTO c DEFAULT VALUES }
  } {/1 {foreign key mismatch - "c" referencing "."}/}
}

# "rowid" cannot be used as part of a child or parent key definition 
# unless it happens to be the name of an explicitly declared column.
#
do_test fkey2-10.2.1 {
  drop_all_tables
................................................................................
  drop_all_tables
  catchsql {
    CREATE TABLE t1(a, b);
    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
    INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1);
    INSERT INTO t2 VALUES(1, 1);
  }
} {1 {foreign key mismatch - "t2" referencing "t1"}}
do_test fkey2-10.2.2 {
  drop_all_tables
  catchsql {
    CREATE TABLE t1(rowid PRIMARY KEY, b);
    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
    INSERT INTO t1(rowid, b) VALUES(1, 1);
    INSERT INTO t2 VALUES(1, 1);
................................................................................
} {}
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 - "cc" referencing "pp"}}
do_test fkey-2.14.3.9 {
  execsql { DROP TABLE cc }
} {}
do_test fkey-2.14.3.10 {
  execsql {
    CREATE TABLE cc(a, b, 
      FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED

Added test/fkey5.test.













































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
# 2012 December 17
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file tests the PRAGMA foreign_key_check command.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {!foreignkey} {
  finish_test
  return
}

do_test fkey5-1.1 {
  db eval {
    CREATE TABLE p1(a INTEGER PRIMARY KEY); INSERT INTO p1 VALUES(88),(89);
    CREATE TABLE p2(a INT PRIMARY KEY); INSERT INTO p2 VALUES(77),(78);
    CREATE TABLE p3(a TEXT PRIMARY KEY);
    INSERT INTO p3 VALUES(66),(67),('alpha'),('BRAVO');
    CREATE TABLE p4(a TEXT PRIMARY KEY COLLATE nocase);
    INSERT INTO p4 VALUES('alpha'),('BRAVO'),('55'),('Delta'),('ECHO');
    CREATE TABLE p5(a INTEGER PRIMARY KEY, b, c, UNIQUE(b,c));
    INSERT INTO p5 VALUES(1,'Alpha','abc'),(2,'beta','def');
    CREATE TABLE p6(a INTEGER PRIMARY KEY, b TEXT COLLATE nocase,
                    c TEXT COLLATE rtrim, UNIQUE(b,c));
    INSERT INTO p6 VALUES(1,'Alpha','abc '),(2,'bETA','def    ');

    CREATE TABLE c1(x INTEGER PRIMARY KEY references p1);
    CREATE TABLE c2(x INTEGER PRIMARY KEY references p2);
    CREATE TABLE c3(x INTEGER PRIMARY KEY references p3);
    CREATE TABLE c4(x INTEGER PRIMARY KEY references p4);
    CREATE TABLE c5(x INT references p1);
    CREATE TABLE c6(x INT references p2);
    CREATE TABLE c7(x INT references p3);
    CREATE TABLE c8(x INT references p4);
    CREATE TABLE c9(x TEXT UNIQUE references p1);
    CREATE TABLE c10(x TEXT UNIQUE references p2);
    CREATE TABLE c11(x TEXT UNIQUE references p3);
    CREATE TABLE c12(x TEXT UNIQUE references p4);
    CREATE TABLE c13(x TEXT COLLATE nocase references p3);
    CREATE TABLE c14(x TEXT COLLATE nocase references p4);
    CREATE TABLE c15(x, y, FOREIGN KEY(x,y) REFERENCES p5(b,c));
    CREATE TABLE c16(x, y, FOREIGN KEY(x,y) REFERENCES p5(c,b));
    CREATE TABLE c17(x, y, FOREIGN KEY(x,y) REFERENCES p6(b,c));
    CREATE TABLE c18(x, y, FOREIGN KEY(x,y) REFERENCES p6(c,b));
    CREATE TABLE c19(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
                     FOREIGN KEY(x,y) REFERENCES p5(b,c));
    CREATE TABLE c20(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
                     FOREIGN KEY(x,y) REFERENCES p5(c,b));
    CREATE TABLE c21(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
                     FOREIGN KEY(x,y) REFERENCES p6(b,c));
    CREATE TABLE c22(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
                     FOREIGN KEY(x,y) REFERENCES p6(c,b));

    PRAGMA foreign_key_check;
  }
} {}    
do_test fkey5-1.2 {
  db eval {
    INSERT INTO c1 VALUES(90),(87),(88);
    PRAGMA foreign_key_check;
  }
} {c1 87 p1 0 c1 90 p1 0}
do_test fkey5-1.3 {
  db eval {
    PRAGMA foreign_key_check(c1);
  }
} {c1 87 p1 0 c1 90 p1 0}
do_test fkey5-1.4 {
  db eval {
    PRAGMA foreign_key_check(c2);
  }
} {}

do_test fkey5-2.0 {
  db eval {
    INSERT INTO c5 SELECT x FROM c1;
    DELETE FROM c1;
    PRAGMA foreign_key_check;
  }
} {c5 1 p1 0 c5 3 p1 0}
do_test fkey5-2.1 {
  db eval {
    PRAGMA foreign_key_check(c5);
  }
} {c5 1 p1 0 c5 3 p1 0}
do_test fkey5-2.2 {
  db eval {
    PRAGMA foreign_key_check(c1);
  }
} {}

do_test fkey5-3.0 {
  db eval {
    INSERT INTO c9 SELECT x FROM c5;
    DELETE FROM c5;
    PRAGMA foreign_key_check;
  }
} {c9 1 p1 0 c9 3 p1 0}
do_test fkey5-3.1 {
  db eval {
    PRAGMA foreign_key_check(c9);
  }
} {c9 1 p1 0 c9 3 p1 0}
do_test fkey5-3.2 {
  db eval {
    PRAGMA foreign_key_check(c5);
  }
} {}

do_test fkey5-4.0 {
  db eval {
    DELETE FROM c9;
    INSERT INTO c2 VALUES(79),(77),(76);
    PRAGMA foreign_key_check;
  }
} {c2 76 p2 0 c2 79 p2 0}
do_test fkey5-4.1 {
  db eval {
    PRAGMA foreign_key_check(c2);
  }
} {c2 76 p2 0 c2 79 p2 0}
do_test fkey5-4.2 {
  db eval {
    INSERT INTO c6 SELECT x FROM c2;
    DELETE FROM c2;
    PRAGMA foreign_key_check;
  }
} {c6 1 p2 0 c6 3 p2 0}
do_test fkey5-4.3 {
  db eval {
    PRAGMA foreign_key_check(c6);
  }
} {c6 1 p2 0 c6 3 p2 0}
do_test fkey5-4.4 {
  db eval {
    INSERT INTO c10 SELECT x FROM c6;
    DELETE FROM c6;
    PRAGMA foreign_key_check;
  }
} {c10 1 p2 0 c10 3 p2 0}
do_test fkey5-4.5 {
  db eval {
    PRAGMA foreign_key_check(c10);
  }
} {c10 1 p2 0 c10 3 p2 0}

do_test fkey5-5.0 {
  db eval {
    DELETE FROM c10;
    INSERT INTO c3 VALUES(68),(67),(65);
    PRAGMA foreign_key_check;
  }
} {c3 65 p3 0 c3 68 p3 0}
do_test fkey5-5.1 {
  db eval {
    PRAGMA foreign_key_check(c3);
  }
} {c3 65 p3 0 c3 68 p3 0}
do_test fkey5-5.2 {
  db eval {
    INSERT INTO c7 SELECT x FROM c3;
    INSERT INTO c7 VALUES('Alpha'),('alpha'),('foxtrot');
    DELETE FROM c3;
    PRAGMA foreign_key_check;
  }
} {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
do_test fkey5-5.3 {
  db eval {
    PRAGMA foreign_key_check(c7);
  }
} {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
do_test fkey5-5.4 {
  db eval {
    INSERT INTO c11 SELECT x FROM c7;
    DELETE FROM c7;
    PRAGMA foreign_key_check;
  }
} {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
do_test fkey5-5.5 {
  db eval {
    PRAGMA foreign_key_check(c11);
  }
} {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}

do_test fkey5-6.0 {
  db eval {
    DELETE FROM c11;
    INSERT INTO c4 VALUES(54),(55),(56);
    PRAGMA foreign_key_check;
  }
} {c4 54 p4 0 c4 56 p4 0}
do_test fkey5-6.1 {
  db eval {
    PRAGMA foreign_key_check(c4);
  }
} {c4 54 p4 0 c4 56 p4 0}
do_test fkey5-6.2 {
  db eval {
    INSERT INTO c8 SELECT x FROM c4;
    INSERT INTO c8 VALUES('Alpha'),('ALPHA'),('foxtrot');
    DELETE FROM c4;
    PRAGMA foreign_key_check;
  }
} {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
do_test fkey5-6.3 {
  db eval {
    PRAGMA foreign_key_check(c8);
  }
} {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
do_test fkey5-6.4 {
  db eval {
    INSERT INTO c12 SELECT x FROM c8;
    DELETE FROM c8;
    PRAGMA foreign_key_check;
  }
} {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
do_test fkey5-6.5 {
  db eval {
    PRAGMA foreign_key_check(c12);
  }
} {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}

do_test fkey5-7.1 {
  db eval {
    INSERT OR IGNORE INTO c13 SELECT * FROM c12;
    INSERT OR IGNORE INTO C14 SELECT * FROM c12;
    DELETE FROM c12;
    PRAGMA foreign_key_check;
  }
} {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0 c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0}
do_test fkey5-7.2 {
  db eval {
    PRAGMA foreign_key_check(c14);
  }
} {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0}
do_test fkey5-7.3 {
  db eval {
    PRAGMA foreign_key_check(c13);
  }
} {c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0}

do_test fkey5-8.0 {
  db eval {
    DELETE FROM c13;
    DELETE FROM c14;
    INSERT INTO c19 VALUES('alpha','abc');
    PRAGMA foreign_key_check(c19);
  }
} {c19 1 p5 0}
do_test fkey5-8.1 {
  db eval {
    DELETE FROM c19;
    INSERT INTO c19 VALUES('Alpha','abc');
    PRAGMA foreign_key_check(c19);
  }
} {}
do_test fkey5-8.2 {
  db eval {
    INSERT INTO c20 VALUES('Alpha','abc');
    PRAGMA foreign_key_check(c20);
  }
} {c20 1 p5 0}
do_test fkey5-8.3 {
  db eval {
    DELETE FROM c20;
    INSERT INTO c20 VALUES('abc','Alpha');
    PRAGMA foreign_key_check(c20);
  }
} {}
do_test fkey5-8.4 {
  db eval {
    INSERT INTO c21 VALUES('alpha','abc    ');
    PRAGMA foreign_key_check(c21);
  }
} {}
do_test fkey5-8.5 {
  db eval {
    DELETE FROM c21;
    INSERT INTO c19 VALUES('Alpha','abc');
    PRAGMA foreign_key_check(c21);
  }
} {}
do_test fkey5-8.6 {
  db eval {
    INSERT INTO c22 VALUES('Alpha','abc');
    PRAGMA foreign_key_check(c22);
  }
} {c22 1 p6 0}
do_test fkey5-8.7 {
  db eval {
    DELETE FROM c22;
    INSERT INTO c22 VALUES('abc  ','ALPHA');
    PRAGMA foreign_key_check(c22);
  }
} {}



finish_test

Changes to test/fkey_malloc.test.

25
26
27
28
29
30
31

32
33
34
35
36
37
38
...
124
125
126
127
128
129
130
131
132
  CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
  CREATE TABLE t2(x REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE);
} -sqlbody {
  INSERT INTO t1 VALUES('aaa', 1);
  INSERT INTO t2 VALUES('aaa');
  UPDATE t1 SET a = 'bbb';
  DELETE FROM t1;

}

do_malloc_test fkey_malloc-2 -sqlprep {
  PRAGMA foreign_keys = 1;
  CREATE TABLE t1(a, b, UNIQUE(a, b));
} -sqlbody {
  CREATE TABLE t2(x, y, 
................................................................................
  CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x);
} -sqlbody {
  DROP TABLE y;
  DROP TABLE x;
}

finish_test









>







 







<
<
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
...
125
126
127
128
129
130
131


  CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
  CREATE TABLE t2(x REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE);
} -sqlbody {
  INSERT INTO t1 VALUES('aaa', 1);
  INSERT INTO t2 VALUES('aaa');
  UPDATE t1 SET a = 'bbb';
  DELETE FROM t1;
  PRAGMA foreign_key_check;
}

do_malloc_test fkey_malloc-2 -sqlprep {
  PRAGMA foreign_keys = 1;
  CREATE TABLE t1(a, b, UNIQUE(a, b));
} -sqlbody {
  CREATE TABLE t2(x, y, 
................................................................................
  CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x);
} -sqlbody {
  DROP TABLE y;
  DROP TABLE x;
}

finish_test