/ Check-in [9ffff484]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 | trunk
Files: files | file ages | folders
SHA1: 9ffff484bb4b67c29dda6180d7d766ad13988fe1
User & Date: dan 2016-02-25 20:17:55
Context
2016-02-25
21:19
Change the "PRAGMA stats" command to report size values in LogEst units. The eliminates the need for sqlite3LogEstToInt() unless non-standard compile-time options are used, so leave it out except in those cases. check-in: 832c237f user: drh tags: trunk
20:17
Disable the RESTRICT foreign key action if "PRAGMA defer_foreign_keys" is set. check-in: 9ffff484 user: dan tags: trunk
19:52
Disable the RESTRICT foreign key action if "PRAGMA defer_foreign_keys" is set. check-in: 8fea1166 user: dan tags: sessions
18:40
Query planner improvements: Take the LIMIT into account when estimated the cost of an ORDER BY. Avoid automatic indexes on query loops that are expected to run less than twice. See [9e2b2681] for a later enhancement. check-in: bf46179d user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes 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 ){
    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 */
    TriggerStep *pStep = 0;        /* First (only) step of trigger program */







>
>
>







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 ){
    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 */
    TriggerStep *pStep = 0;        /* First (only) step of trigger program */

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