/ Check-in [25411f83]
Login

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

Overview
Comment:Added test cases for PRAGMA foreign_key_check. Fixed a bug that appears when the column order of the child and parent differ.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | foreign-key-check
Files: files | file ages | folders
SHA1:25411f83f99803e39feee368703fd20b4ade8eeb
User & Date: drh 2012-12-17 22:32:14
Context
2012-12-20
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-17
22:32
Added test cases for PRAGMA foreign_key_check. Fixed a bug that appears when the column order of the child and parent differ. check-in: 25411f83 user: drh tags: foreign-key-check
20:57
Add to the foreign_key_check pragma an extra output column "parent" that contains the name of the parent table for the constraint that failed. check-in: 97f7f737 user: drh tags: foreign-key-check
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/pragma.c.

  1125   1125       HashElem *k;           /* Loop counter:  Next table in schema */
  1126   1126       int x;                 /* result variable */
  1127   1127       int regResult;         /* 3 registers to hold a result row */
  1128   1128       int regKey;            /* Register to hold key for checking the FK */
  1129   1129       int regRow;            /* Registers to hold a row from pTab */
  1130   1130       int addrTop;           /* Top of a loop checking foreign keys */
  1131   1131       int addrOk;            /* Jump here if the key is OK */
         1132  +    int *aiCols;           /* child to parent column mapping */
  1132   1133   
  1133   1134       if( sqlite3ReadSchema(pParse) ) goto pragma_out;
  1134   1135       regResult = pParse->nMem+1;
  1135   1136       pParse->nMem += 4;
  1136   1137       regKey = ++pParse->nMem;
  1137   1138       regRow = ++pParse->nMem;
  1138   1139       v = sqlite3GetVdbe(pParse);
................................................................................
  1179   1180         if( pFK ) break;
  1180   1181         if( pParse->nTab<i ) pParse->nTab = i;
  1181   1182         addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, 0);
  1182   1183         for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
  1183   1184           pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
  1184   1185           assert( pParent!=0 );
  1185   1186           pIdx = 0;
  1186         -        x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, 0);
         1187  +        aiCols = 0;
         1188  +        x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols);
  1187   1189           assert( x==0 );
  1188   1190           addrOk = sqlite3VdbeMakeLabel(v);
  1189   1191           if( pIdx==0 ){
  1190   1192             int iKey = pFK->aCol[0].iFrom;
  1191   1193             if( iKey>=0 && iKey!=pTab->iPKey ){
  1192   1194               sqlite3VdbeAddOp3(v, OP_Column, 0, iKey, regRow);
  1193   1195               sqlite3ColumnDefault(v, pTab, iKey, regRow);
................................................................................
  1198   1200               sqlite3VdbeAddOp2(v, OP_Rowid, 0, regRow);
  1199   1201             }
  1200   1202             sqlite3VdbeAddOp3(v, OP_NotExists, i, 0, regRow);
  1201   1203             sqlite3VdbeAddOp2(v, OP_Goto, 0, addrOk);
  1202   1204             sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
  1203   1205           }else{
  1204   1206             for(j=0; j<pFK->nCol; j++){
  1205         -            sqlite3ExprCodeGetColumnOfTable(v, pTab, 0, pFK->aCol[j].iFrom,
  1206         -                                            regRow+j);
         1207  +            sqlite3ExprCodeGetColumnOfTable(v, pTab, 0,
         1208  +                            aiCols ? aiCols[j] : pFK->aCol[0].iFrom, regRow+j);
  1207   1209               sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk);
  1208   1210             }
  1209   1211             sqlite3VdbeAddOp3(v, OP_MakeRecord, regRow, pFK->nCol, regKey);
  1210   1212             sqlite3VdbeChangeP4(v, -1,
  1211   1213                      sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT);
  1212   1214             sqlite3VdbeAddOp4Int(v, OP_Found, i, addrOk, regKey, 0);
  1213   1215           }
  1214   1216           sqlite3VdbeAddOp2(v, OP_Rowid, 0, regResult+1);
  1215   1217           sqlite3VdbeAddOp4(v, OP_String8, 0, regResult+2, 0, 
  1216   1218                             pFK->zTo, P4_TRANSIENT);
  1217   1219           sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult+3);
  1218   1220           sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 4);
  1219   1221           sqlite3VdbeResolveLabel(v, addrOk);
         1222  +        sqlite3DbFree(db, aiCols);
  1220   1223         }
  1221   1224         sqlite3VdbeAddOp2(v, OP_Next, 0, addrTop+1);
  1222   1225         sqlite3VdbeJumpHere(v, addrTop);
  1223   1226       }
  1224   1227     }else
  1225   1228   #endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */
  1226   1229   

Added test/fkey5.test.

            1  +# 2012 December 17
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +# This file tests the PRAGMA foreign_key_check command.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +ifcapable {!foreignkey} {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +do_test fkey5-1.1 {
           25  +  db eval {
           26  +    CREATE TABLE p1(a INTEGER PRIMARY KEY); INSERT INTO p1 VALUES(88),(89);
           27  +    CREATE TABLE p2(a INT PRIMARY KEY); INSERT INTO p2 VALUES(77),(78);
           28  +    CREATE TABLE p3(a TEXT PRIMARY KEY);
           29  +    INSERT INTO p3 VALUES(66),(67),('alpha'),('BRAVO');
           30  +    CREATE TABLE p4(a TEXT PRIMARY KEY COLLATE nocase);
           31  +    INSERT INTO p4 VALUES('alpha'),('BRAVO'),('55'),('Delta'),('ECHO');
           32  +    CREATE TABLE p5(a INTEGER PRIMARY KEY, b, c, UNIQUE(b,c));
           33  +    INSERT INTO p5 VALUES(1,'Alpha','abc'),(2,'beta','def');
           34  +    CREATE TABLE p6(a INTEGER PRIMARY KEY, b TEXT COLLATE nocase,
           35  +                    c TEXT COLLATE rtrim, UNIQUE(b,c));
           36  +    INSERT INTO p6 VALUES(1,'Alpha','abc '),(2,'bETA','def    ');
           37  +
           38  +    CREATE TABLE c1(x INTEGER PRIMARY KEY references p1);
           39  +    CREATE TABLE c2(x INTEGER PRIMARY KEY references p2);
           40  +    CREATE TABLE c3(x INTEGER PRIMARY KEY references p3);
           41  +    CREATE TABLE c4(x INTEGER PRIMARY KEY references p4);
           42  +    CREATE TABLE c5(x INT references p1);
           43  +    CREATE TABLE c6(x INT references p2);
           44  +    CREATE TABLE c7(x INT references p3);
           45  +    CREATE TABLE c8(x INT references p4);
           46  +    CREATE TABLE c9(x TEXT UNIQUE references p1);
           47  +    CREATE TABLE c10(x TEXT UNIQUE references p2);
           48  +    CREATE TABLE c11(x TEXT UNIQUE references p3);
           49  +    CREATE TABLE c12(x TEXT UNIQUE references p4);
           50  +    CREATE TABLE c13(x TEXT COLLATE nocase references p3);
           51  +    CREATE TABLE c14(x TEXT COLLATE nocase references p4);
           52  +    CREATE TABLE c15(x, y, FOREIGN KEY(x,y) REFERENCES p5(b,c));
           53  +    CREATE TABLE c16(x, y, FOREIGN KEY(x,y) REFERENCES p5(c,b));
           54  +    CREATE TABLE c17(x, y, FOREIGN KEY(x,y) REFERENCES p6(b,c));
           55  +    CREATE TABLE c18(x, y, FOREIGN KEY(x,y) REFERENCES p6(c,b));
           56  +    CREATE TABLE c19(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
           57  +                     FOREIGN KEY(x,y) REFERENCES p5(b,c));
           58  +    CREATE TABLE c20(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
           59  +                     FOREIGN KEY(x,y) REFERENCES p5(c,b));
           60  +    CREATE TABLE c21(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
           61  +                     FOREIGN KEY(x,y) REFERENCES p6(b,c));
           62  +    CREATE TABLE c22(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
           63  +                     FOREIGN KEY(x,y) REFERENCES p6(c,b));
           64  +
           65  +    PRAGMA foreign_key_check;
           66  +  }
           67  +} {}    
           68  +do_test fkey5-1.2 {
           69  +  db eval {
           70  +    INSERT INTO c1 VALUES(90),(87),(88);
           71  +    PRAGMA foreign_key_check;
           72  +  }
           73  +} {c1 87 p1 0 c1 90 p1 0}
           74  +do_test fkey5-1.3 {
           75  +  db eval {
           76  +    PRAGMA foreign_key_check(c1);
           77  +  }
           78  +} {c1 87 p1 0 c1 90 p1 0}
           79  +do_test fkey5-1.4 {
           80  +  db eval {
           81  +    PRAGMA foreign_key_check(c2);
           82  +  }
           83  +} {}
           84  +
           85  +do_test fkey5-2.0 {
           86  +  db eval {
           87  +    INSERT INTO c5 SELECT x FROM c1;
           88  +    DELETE FROM c1;
           89  +    PRAGMA foreign_key_check;
           90  +  }
           91  +} {c5 1 p1 0 c5 3 p1 0}
           92  +do_test fkey5-2.1 {
           93  +  db eval {
           94  +    PRAGMA foreign_key_check(c5);
           95  +  }
           96  +} {c5 1 p1 0 c5 3 p1 0}
           97  +do_test fkey5-2.2 {
           98  +  db eval {
           99  +    PRAGMA foreign_key_check(c1);
          100  +  }
          101  +} {}
          102  +
          103  +do_test fkey5-3.0 {
          104  +  db eval {
          105  +    INSERT INTO c9 SELECT x FROM c5;
          106  +    DELETE FROM c5;
          107  +    PRAGMA foreign_key_check;
          108  +  }
          109  +} {c9 1 p1 0 c9 3 p1 0}
          110  +do_test fkey5-3.1 {
          111  +  db eval {
          112  +    PRAGMA foreign_key_check(c9);
          113  +  }
          114  +} {c9 1 p1 0 c9 3 p1 0}
          115  +do_test fkey5-3.2 {
          116  +  db eval {
          117  +    PRAGMA foreign_key_check(c5);
          118  +  }
          119  +} {}
          120  +
          121  +do_test fkey5-4.0 {
          122  +  db eval {
          123  +    DELETE FROM c9;
          124  +    INSERT INTO c2 VALUES(79),(77),(76);
          125  +    PRAGMA foreign_key_check;
          126  +  }
          127  +} {c2 76 p2 0 c2 79 p2 0}
          128  +do_test fkey5-4.1 {
          129  +  db eval {
          130  +    PRAGMA foreign_key_check(c2);
          131  +  }
          132  +} {c2 76 p2 0 c2 79 p2 0}
          133  +do_test fkey5-4.2 {
          134  +  db eval {
          135  +    INSERT INTO c6 SELECT x FROM c2;
          136  +    DELETE FROM c2;
          137  +    PRAGMA foreign_key_check;
          138  +  }
          139  +} {c6 1 p2 0 c6 3 p2 0}
          140  +do_test fkey5-4.3 {
          141  +  db eval {
          142  +    PRAGMA foreign_key_check(c6);
          143  +  }
          144  +} {c6 1 p2 0 c6 3 p2 0}
          145  +do_test fkey5-4.4 {
          146  +  db eval {
          147  +    INSERT INTO c10 SELECT x FROM c6;
          148  +    DELETE FROM c6;
          149  +    PRAGMA foreign_key_check;
          150  +  }
          151  +} {c10 1 p2 0 c10 3 p2 0}
          152  +do_test fkey5-4.5 {
          153  +  db eval {
          154  +    PRAGMA foreign_key_check(c10);
          155  +  }
          156  +} {c10 1 p2 0 c10 3 p2 0}
          157  +
          158  +do_test fkey5-5.0 {
          159  +  db eval {
          160  +    DELETE FROM c10;
          161  +    INSERT INTO c3 VALUES(68),(67),(65);
          162  +    PRAGMA foreign_key_check;
          163  +  }
          164  +} {c3 65 p3 0 c3 68 p3 0}
          165  +do_test fkey5-5.1 {
          166  +  db eval {
          167  +    PRAGMA foreign_key_check(c3);
          168  +  }
          169  +} {c3 65 p3 0 c3 68 p3 0}
          170  +do_test fkey5-5.2 {
          171  +  db eval {
          172  +    INSERT INTO c7 SELECT x FROM c3;
          173  +    INSERT INTO c7 VALUES('Alpha'),('alpha'),('foxtrot');
          174  +    DELETE FROM c3;
          175  +    PRAGMA foreign_key_check;
          176  +  }
          177  +} {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
          178  +do_test fkey5-5.3 {
          179  +  db eval {
          180  +    PRAGMA foreign_key_check(c7);
          181  +  }
          182  +} {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
          183  +do_test fkey5-5.4 {
          184  +  db eval {
          185  +    INSERT INTO c11 SELECT x FROM c7;
          186  +    DELETE FROM c7;
          187  +    PRAGMA foreign_key_check;
          188  +  }
          189  +} {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
          190  +do_test fkey5-5.5 {
          191  +  db eval {
          192  +    PRAGMA foreign_key_check(c11);
          193  +  }
          194  +} {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
          195  +
          196  +do_test fkey5-6.0 {
          197  +  db eval {
          198  +    DELETE FROM c11;
          199  +    INSERT INTO c4 VALUES(54),(55),(56);
          200  +    PRAGMA foreign_key_check;
          201  +  }
          202  +} {c4 54 p4 0 c4 56 p4 0}
          203  +do_test fkey5-6.1 {
          204  +  db eval {
          205  +    PRAGMA foreign_key_check(c4);
          206  +  }
          207  +} {c4 54 p4 0 c4 56 p4 0}
          208  +do_test fkey5-6.2 {
          209  +  db eval {
          210  +    INSERT INTO c8 SELECT x FROM c4;
          211  +    INSERT INTO c8 VALUES('Alpha'),('ALPHA'),('foxtrot');
          212  +    DELETE FROM c4;
          213  +    PRAGMA foreign_key_check;
          214  +  }
          215  +} {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
          216  +do_test fkey5-6.3 {
          217  +  db eval {
          218  +    PRAGMA foreign_key_check(c8);
          219  +  }
          220  +} {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
          221  +do_test fkey5-6.4 {
          222  +  db eval {
          223  +    INSERT INTO c12 SELECT x FROM c8;
          224  +    DELETE FROM c8;
          225  +    PRAGMA foreign_key_check;
          226  +  }
          227  +} {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
          228  +do_test fkey5-6.5 {
          229  +  db eval {
          230  +    PRAGMA foreign_key_check(c12);
          231  +  }
          232  +} {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
          233  +
          234  +do_test fkey5-7.1 {
          235  +  db eval {
          236  +    INSERT OR IGNORE INTO c13 SELECT * FROM c12;
          237  +    INSERT OR IGNORE INTO C14 SELECT * FROM c12;
          238  +    DELETE FROM c12;
          239  +    PRAGMA foreign_key_check;
          240  +  }
          241  +} {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}
          242  +do_test fkey5-7.2 {
          243  +  db eval {
          244  +    PRAGMA foreign_key_check(c14);
          245  +  }
          246  +} {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0}
          247  +do_test fkey5-7.3 {
          248  +  db eval {
          249  +    PRAGMA foreign_key_check(c13);
          250  +  }
          251  +} {c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0}
          252  +
          253  +do_test fkey5-8.0 {
          254  +  db eval {
          255  +    DELETE FROM c13;
          256  +    DELETE FROM c14;
          257  +    INSERT INTO c19 VALUES('alpha','abc');
          258  +    PRAGMA foreign_key_check(c19);
          259  +  }
          260  +} {c19 1 p5 0}
          261  +do_test fkey5-8.1 {
          262  +  db eval {
          263  +    DELETE FROM c19;
          264  +    INSERT INTO c19 VALUES('Alpha','abc');
          265  +    PRAGMA foreign_key_check(c19);
          266  +  }
          267  +} {}
          268  +do_test fkey5-8.2 {
          269  +  db eval {
          270  +    INSERT INTO c20 VALUES('Alpha','abc');
          271  +    PRAGMA foreign_key_check(c20);
          272  +  }
          273  +} {c20 1 p5 0}
          274  +do_test fkey5-8.3 {
          275  +  db eval {
          276  +    DELETE FROM c20;
          277  +    INSERT INTO c20 VALUES('abc','Alpha');
          278  +    PRAGMA foreign_key_check(c20);
          279  +  }
          280  +} {}
          281  +do_test fkey5-8.4 {
          282  +  db eval {
          283  +    INSERT INTO c21 VALUES('alpha','abc    ');
          284  +    PRAGMA foreign_key_check(c21);
          285  +  }
          286  +} {}
          287  +do_test fkey5-8.5 {
          288  +  db eval {
          289  +    DELETE FROM c21;
          290  +    INSERT INTO c19 VALUES('Alpha','abc');
          291  +    PRAGMA foreign_key_check(c21);
          292  +  }
          293  +} {}
          294  +do_test fkey5-8.6 {
          295  +  db eval {
          296  +    INSERT INTO c22 VALUES('Alpha','abc');
          297  +    PRAGMA foreign_key_check(c22);
          298  +  }
          299  +} {c22 1 p6 0}
          300  +do_test fkey5-8.7 {
          301  +  db eval {
          302  +    DELETE FROM c22;
          303  +    INSERT INTO c22 VALUES('abc  ','ALPHA');
          304  +    PRAGMA foreign_key_check(c22);
          305  +  }
          306  +} {}
          307  +
          308  +
          309  +
          310  +finish_test