/ Check-in [8fea1166]
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 | sessions
Files: files | file ages | folders
SHA1: 8fea1166016c659ece83e0045bc348f478a9ba6a
User & Date: dan 2016-02-25 19:52:11
Context
2016-03-04
16:42
Merge recent enhancements from trunk. Default page size is 4096. Writes to statement journals are avoided. check-in: 456df336 user: drh tags: sessions
2016-02-25
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:54
Merge all the latest changes from trunk. check-in: b8659004 user: drh 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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/session/sessionC.test.

    22     22   
    23     23   #-------------------------------------------------------------------------
    24     24   # Test the outcome of a DELETE operation made as part of applying a
    25     25   # changeset failing with SQLITE_CONSTRAINT. This may happen if an
    26     26   # ON DELETE RESTRICT foreign key action is triggered, or if a trigger
    27     27   # program raises a constraint somehow.
    28     28   #
           29  +# UPDATE: The above is no longer true, as "PRAGMA defer_foreign_keys"
           30  +# now disables "RESTRICT" processing. The test below has been rewritten 
           31  +# to use a trigger instead of a foreign key to test this case.
           32  +#
    29     33   do_execsql_test 1.0 {
    30     34     PRAGMA foreign_keys = 1;
    31     35   
    32     36     CREATE TABLE p(a PRIMARY KEY, b, c);
    33         -  CREATE TABLE c(d PRIMARY KEY, e REFERENCES p ON DELETE RESTRICT);
           37  +  CREATE TABLE c(d PRIMARY KEY, e /* REFERENCES p ON DELETE RESTRICT */);
           38  +
           39  +  CREATE TRIGGER restrict_trig BEFORE DELETE ON p BEGIN
           40  +    SELECT raise(ABORT, 'error!') FROM c WHERE e=old.a;
           41  +  END;
    34     42   
    35     43     INSERT INTO p VALUES('one', 1, 1);
    36     44     INSERT INTO p VALUES('two', 2, 2);
    37     45     INSERT INTO p VALUES('three', 3, 3);
    38     46   
    39     47     INSERT INTO c VALUES(1, 'one');
    40     48     INSERT INTO c VALUES(3, 'three');

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