/ Check-in [82470d1c]
Login

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

Overview
Comment:Disable the RESTRICT foreign key action if "PRAGMA defer_foreign_keys" is set.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | disable-restrict
Files: files | file ages | folders
SHA1: 82470d1c3a41221c78bcdd402b2219c4c21c43af
User & Date: dan 2016-01-21 17:25:56
Context
2016-02-25
19:52
Disable the RESTRICT foreign key action if "PRAGMA defer_foreign_keys" is set. check-in: 8fea1166 user: dan tags: sessions
2016-01-21
17:25
Disable the RESTRICT foreign key action if "PRAGMA defer_foreign_keys" is set. Closed-Leaf check-in: 82470d1c user: dan tags: disable-restrict
2016-01-20
16:02
Merge the LIKE-operator bug fix from trunk. check-in: 36cb3d6e user: drh tags: sessions
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

1159
1160
1161
1162
1163
1164
1165



1166
1167
1168
1169
1170
1171
1172
  sqlite3 *db = pParse->db;       /* Database handle */
  int action;                     /* One of OE_None, OE_Cascade etc. */
  Trigger *pTrigger;              /* Trigger definition to return */
  int iAction = (pChanges!=0);    /* 1 for UPDATE, 0 for DELETE */

  action = pFKey->aAction[iAction];
  pTrigger = pFKey->apTrigger[iAction];




  if( action!=OE_None && !pTrigger ){
    u8 enableLookaside;           /* Copy of db->lookaside.bEnabled */
    char const *zFrom;            /* Name of child table */
    int nFrom;                    /* Length in bytes of zFrom */
    Index *pIdx = 0;              /* Parent key index for this FK */
    int *aiCol = 0;               /* child table cols -> parent key cols */







>
>
>







1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
  sqlite3 *db = pParse->db;       /* Database handle */
  int action;                     /* One of OE_None, OE_Cascade etc. */
  Trigger *pTrigger;              /* Trigger definition to return */
  int iAction = (pChanges!=0);    /* 1 for UPDATE, 0 for DELETE */

  action = pFKey->aAction[iAction];
  pTrigger = pFKey->apTrigger[iAction];
  if( (db->flags & SQLITE_DeferFKs) && action==OE_Restrict ){
    return 0;
  }

  if( action!=OE_None && !pTrigger ){
    u8 enableLookaside;           /* Copy of db->lookaside.bEnabled */
    char const *zFrom;            /* Name of child table */
    int nFrom;                    /* Length in bytes of zFrom */
    Index *pIdx = 0;              /* Parent key index for this FK */
    int *aiCol = 0;               /* child table cols -> parent key cols */

Changes to test/fkey6.test.

19
20
21
22
23
24
25

26
27
28
29
30
31
32
...
166
167
168
169
170
171
172
173


174



















































175
#
# EVIDENCE-OF: R-28911-57501 The defer_foreign_keys pragma defaults to
# OFF so that foreign key constraints are only deferred if they are
# created as "DEFERRABLE INITIALLY DEFERRED".

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


ifcapable {!foreignkey} {
  finish_test
  return
}

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























































finish_test







>







 








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

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
...
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
#
# EVIDENCE-OF: R-28911-57501 The defer_foreign_keys pragma defaults to
# OFF so that foreign key constraints are only deferred if they are
# created as "DEFERRABLE INITIALLY DEFERRED".

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

ifcapable {!foreignkey} {
  finish_test
  return
}

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

#--------------------------------------------------------------------------
# Test that defer_foreign_keys disables RESTRICT.
#
do_execsql_test 3.1 {
  CREATE TABLE p2(a PRIMARY KEY, b);
  CREATE TABLE c2(x, y REFERENCES p2 ON DELETE RESTRICT ON UPDATE RESTRICT);
  INSERT INTO p2 VALUES(1, 'one');
  INSERT INTO p2 VALUES(2, 'two');
  INSERT INTO c2 VALUES('i', 1);
}

do_catchsql_test 3.2.1 {
  BEGIN;
    UPDATE p2 SET a=a-1;
} {1 {FOREIGN KEY constraint failed}}
do_execsql_test 3.2.2 { COMMIT }

do_execsql_test 3.2.3 {
  BEGIN;
    PRAGMA defer_foreign_keys = 1;
    UPDATE p2 SET a=a-1;
  COMMIT;
}

do_execsql_test 3.2.4 {
  BEGIN;
    PRAGMA defer_foreign_keys = 1;
    UPDATE p2 SET a=a-1;
}
do_catchsql_test 3.2.5 {
  COMMIT;
} {1 {FOREIGN KEY constraint failed}}
do_execsql_test 3.2.6 { ROLLBACK }

do_execsql_test 3.3.1 {
  CREATE TRIGGER p2t AFTER DELETE ON p2 BEGIN
    INSERT INTO p2 VALUES(old.a, 'deleted!');
  END;
}
do_catchsql_test 3.3.2 {
  BEGIN;
    DELETE FROM p2 WHERE a=1;
} {1 {FOREIGN KEY constraint failed}}
do_execsql_test 3.3.3 { COMMIT }

do_execsql_test 3.3.4 {
  BEGIN;
    PRAGMA defer_foreign_keys = 1;
    DELETE FROM p2 WHERE a=1;
  COMMIT;
  SELECT * FROM p2;
} {0 one 1 deleted!}


finish_test