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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
27001356ed8201529b3f31d4313f2010 |
| User & Date: | dan 2013-10-12 15:12:43 |
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: f0cf8c85 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: 8c13a7fd 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: 27001356 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: 67e28a11 user: drh tags: trunk | |
Changes
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
|