/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

  1159   1159     sqlite3 *db = pParse->db;       /* Database handle */
  1160   1160     int action;                     /* One of OE_None, OE_Cascade etc. */
  1161   1161     Trigger *pTrigger;              /* Trigger definition to return */
  1162   1162     int iAction = (pChanges!=0);    /* 1 for UPDATE, 0 for DELETE */
  1163   1163   
  1164   1164     action = pFKey->aAction[iAction];
  1165   1165     pTrigger = pFKey->apTrigger[iAction];
         1166  +  if( (db->flags & SQLITE_DeferFKs) && action==OE_Restrict ){
         1167  +    return 0;
         1168  +  }
  1166   1169   
  1167   1170     if( action!=OE_None && !pTrigger ){
  1168   1171       char const *zFrom;            /* Name of child table */
  1169   1172       int nFrom;                    /* Length in bytes of zFrom */
  1170   1173       Index *pIdx = 0;              /* Parent key index for this FK */
  1171   1174       int *aiCol = 0;               /* child table cols -> parent key cols */
  1172   1175       TriggerStep *pStep = 0;        /* First (only) step of trigger program */

Changes to test/fkey6.test.

    19     19   #
    20     20   # EVIDENCE-OF: R-28911-57501 The defer_foreign_keys pragma defaults to
    21     21   # OFF so that foreign key constraints are only deferred if they are
    22     22   # created as "DEFERRABLE INITIALLY DEFERRED".
    23     23   
    24     24   set testdir [file dirname $argv0]
    25     25   source $testdir/tester.tcl
           26  +set testprefix fkey6
    26     27   
    27     28   ifcapable {!foreignkey} {
    28     29     finish_test
    29     30     return
    30     31   }
    31     32   
    32     33   do_execsql_test fkey6-1.0 {
................................................................................
   166    167     BEGIN;
   167    168       PRAGMA defer_foreign_keys = 1;
   168    169       INSERT INTO c1 VALUES('three');
   169    170       DROP TABLE c1;
   170    171     COMMIT;
   171    172     PRAGMA defer_foreign_keys;
   172    173   } {0}
          174  +
          175  +#--------------------------------------------------------------------------
          176  +# Test that defer_foreign_keys disables RESTRICT.
          177  +#
          178  +do_execsql_test 3.1 {
          179  +  CREATE TABLE p2(a PRIMARY KEY, b);
          180  +  CREATE TABLE c2(x, y REFERENCES p2 ON DELETE RESTRICT ON UPDATE RESTRICT);
          181  +  INSERT INTO p2 VALUES(1, 'one');
          182  +  INSERT INTO p2 VALUES(2, 'two');
          183  +  INSERT INTO c2 VALUES('i', 1);
          184  +}
          185  +
          186  +do_catchsql_test 3.2.1 {
          187  +  BEGIN;
          188  +    UPDATE p2 SET a=a-1;
          189  +} {1 {FOREIGN KEY constraint failed}}
          190  +do_execsql_test 3.2.2 { COMMIT }
          191  +
          192  +do_execsql_test 3.2.3 {
          193  +  BEGIN;
          194  +    PRAGMA defer_foreign_keys = 1;
          195  +    UPDATE p2 SET a=a-1;
          196  +  COMMIT;
          197  +}
          198  +
          199  +do_execsql_test 3.2.4 {
          200  +  BEGIN;
          201  +    PRAGMA defer_foreign_keys = 1;
          202  +    UPDATE p2 SET a=a-1;
          203  +}
          204  +do_catchsql_test 3.2.5 {
          205  +  COMMIT;
          206  +} {1 {FOREIGN KEY constraint failed}}
          207  +do_execsql_test 3.2.6 { ROLLBACK }
          208  +
          209  +do_execsql_test 3.3.1 {
          210  +  CREATE TRIGGER p2t AFTER DELETE ON p2 BEGIN
          211  +    INSERT INTO p2 VALUES(old.a, 'deleted!');
          212  +  END;
          213  +}
          214  +do_catchsql_test 3.3.2 {
          215  +  BEGIN;
          216  +    DELETE FROM p2 WHERE a=1;
          217  +} {1 {FOREIGN KEY constraint failed}}
          218  +do_execsql_test 3.3.3 { COMMIT }
          219  +
          220  +do_execsql_test 3.3.4 {
          221  +  BEGIN;
          222  +    PRAGMA defer_foreign_keys = 1;
          223  +    DELETE FROM p2 WHERE a=1;
          224  +  COMMIT;
          225  +  SELECT * FROM p2;
          226  +} {0 one 1 deleted!}
   173    227   
   174    228   
   175    229   finish_test