SQLite

Check-in [27001356ed]
Login

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

Overview
Comment:Fix handling of "DROP TABLE" commands when "PRAGMA defer_foreign_keys=1" is set.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 27001356ed8201529b3f31d4313f2010f1b4e1b1
User & Date: dan 2013-10-12 15:12:43.761
Context
2013-10-12
20:22
Restore the index_list pragma back to its former operation. Create a new PRAGMA stats used to access the table and index widths and heights. (check-in: f0cf8c85dc user: drh tags: trunk)
19:06
In "PRAGMA foreign_key_check", treat missing parent tables as empty (instead of as errors). (Closed-Leaf check-in: 8c13a7fd73 user: dan tags: fkc-missing-parent-tables)
15:12
Fix handling of "DROP TABLE" commands when "PRAGMA defer_foreign_keys=1" is set. (check-in: 27001356ed user: dan tags: trunk)
13:16
Add tests that demonstrate that PRAGMA defer_foreign_keys will reset to off at the conclusion of the next transaction. (check-in: 67e28a11de user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/fkey.c.
︙︙
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673






674
675
676
677

678
679
680
681
682
683
684

      /* Search for a deferred foreign key constraint for which this table
      ** is the child table. If one cannot be found, return without 
      ** generating any VDBE code. If one can be found, then jump over
      ** the entire DELETE if there are no outstanding deferred constraints
      ** when this statement is run.  */
      FKey *p;
      for(p=pTab->pFKey; p; p=p->pNextFrom){
        if( p->isDeferred ) break;
      }
      if( !p ) return;
      iSkip = sqlite3VdbeMakeLabel(v);
      sqlite3VdbeAddOp2(v, OP_FkIfZero, 1, iSkip);
    }

    pParse->disableTriggers = 1;
    sqlite3DeleteFrom(pParse, sqlite3SrcListDup(db, pName, 0), 0);
    pParse->disableTriggers = 0;

    /* If the DELETE has generated immediate foreign key constraint 
    ** violations, halt the VDBE and return an error at this point, before
    ** any modifications to the schema are made. This is because statement
    ** transactions are not able to rollback schema changes.  */






    sqlite3VdbeAddOp2(v, OP_FkIfZero, 0, sqlite3VdbeCurrentAddr(v)+2);
    sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
        OE_Abort, "foreign key constraint failed", P4_STATIC
    );


    if( iSkip ){
      sqlite3VdbeResolveLabel(v, iSkip);
    }
  }
}









|













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








652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691

      /* Search for a deferred foreign key constraint for which this table
      ** is the child table. If one cannot be found, return without 
      ** generating any VDBE code. If one can be found, then jump over
      ** the entire DELETE if there are no outstanding deferred constraints
      ** when this statement is run.  */
      FKey *p;
      for(p=pTab->pFKey; p; p=p->pNextFrom){
        if( p->isDeferred || (db->flags & SQLITE_DeferFKs) ) break;
      }
      if( !p ) return;
      iSkip = sqlite3VdbeMakeLabel(v);
      sqlite3VdbeAddOp2(v, OP_FkIfZero, 1, iSkip);
    }

    pParse->disableTriggers = 1;
    sqlite3DeleteFrom(pParse, sqlite3SrcListDup(db, pName, 0), 0);
    pParse->disableTriggers = 0;

    /* If the DELETE has generated immediate foreign key constraint 
    ** violations, halt the VDBE and return an error at this point, before
    ** any modifications to the schema are made. This is because statement
    ** transactions are not able to rollback schema changes.  
    **
    ** If the SQLITE_DeferFKs flag is set, then this is not required, as
    ** the statement transaction will not be rolled back even if FK
    ** constraints are violated.
    */
    if( (db->flags & SQLITE_DeferFKs)==0 ){
      sqlite3VdbeAddOp2(v, OP_FkIfZero, 0, sqlite3VdbeCurrentAddr(v)+2);
      sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
          OE_Abort, "foreign key constraint failed", P4_STATIC
      );
    }

    if( iSkip ){
      sqlite3VdbeResolveLabel(v, iSkip);
    }
  }
}
︙︙
Changes to test/fkey6.test.
︙︙
117
118
119
120
121
122
123





124













































125


} {0 0 0}
do_test fkey6-1.22 {
  execsql {
    COMMIT;
  }
} {}




















































finish_test









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

>

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

} {0 0 0}
do_test fkey6-1.22 {
  execsql {
    COMMIT;
  }
} {}

do_execsql_test fkey6-2.1 {
  CREATE TABLE p1(a PRIMARY KEY);
  INSERT INTO p1 VALUES('one'), ('two');
  CREATE TABLE c1(x REFERENCES p1);
  INSERT INTO c1 VALUES('two'), ('one');
}

do_execsql_test fkey6-2.2 {
  BEGIN;
    PRAGMA defer_foreign_keys = 1;
    DELETE FROM p1;
  ROLLBACK;
  PRAGMA defer_foreign_keys;
} {0}

do_execsql_test fkey6-2.3 {
  BEGIN;
    PRAGMA defer_foreign_keys = 1;
    DROP TABLE p1;
    PRAGMA vdbe_trace = 0;
  ROLLBACK;
  PRAGMA defer_foreign_keys;
} {0}

do_execsql_test fkey6-2.4 {
  BEGIN;
    PRAGMA defer_foreign_keys = 1;
    DELETE FROM p1;
    DROP TABLE c1;
  COMMIT;
  PRAGMA defer_foreign_keys;
} {0}

do_execsql_test fkey6-2.5 {
  DROP TABLE p1;
  CREATE TABLE p1(a PRIMARY KEY);
  INSERT INTO p1 VALUES('one'), ('two');
  CREATE TABLE c1(x REFERENCES p1);
  INSERT INTO c1 VALUES('two'), ('one');
}

do_execsql_test fkey6-2.6 {
  BEGIN;
    PRAGMA defer_foreign_keys = 1;
    INSERT INTO c1 VALUES('three');
    DROP TABLE c1;
  COMMIT;
  PRAGMA defer_foreign_keys;
} {0}


finish_test