000001  # 2012 December 17
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  # This file implements regression tests for SQLite library.
000012  #
000013  # This file tests the PRAGMA defer_foreign_keys and 
000014  # SQLITE_DBSTATUS_DEFERRED_FKS
000015  #
000016  # EVIDENCE-OF: R-18981-16292 When the defer_foreign_keys PRAGMA is on,
000017  # enforcement of all foreign key constraints is delayed until the
000018  # outermost transaction is committed.
000019  #
000020  # EVIDENCE-OF: R-28911-57501 The defer_foreign_keys pragma defaults to
000021  # OFF so that foreign key constraints are only deferred if they are
000022  # created as "DEFERRABLE INITIALLY DEFERRED".
000023  
000024  set testdir [file dirname $argv0]
000025  source $testdir/tester.tcl
000026  set testprefix fkey6
000027  
000028  ifcapable {!foreignkey} {
000029    finish_test
000030    return
000031  }
000032  
000033  do_execsql_test fkey6-1.0 {
000034    PRAGMA defer_foreign_keys;
000035  } {0}
000036  
000037  do_execsql_test fkey6-1.1 {
000038    PRAGMA foreign_keys=ON;
000039    CREATE TABLE t1(x INTEGER PRIMARY KEY);
000040    CREATE TABLE t2(y INTEGER PRIMARY KEY,
000041            z INTEGER REFERENCES t1(x) DEFERRABLE INITIALLY DEFERRED);
000042    CREATE INDEX t2z ON t2(z);
000043    CREATE TABLE t3(u INTEGER PRIMARY KEY, v INTEGER REFERENCES t1(x));
000044    CREATE INDEX t3v ON t3(v);
000045    INSERT INTO t1 VALUES(1),(2),(3),(4),(5);
000046    INSERT INTO t2 VALUES(1,1),(2,2);
000047    INSERT INTO t3 VALUES(3,3),(4,4);
000048  } {}
000049  do_test fkey6-1.2 {
000050    catchsql {DELETE FROM t1 WHERE x=2;}
000051  } {1 {FOREIGN KEY constraint failed}}
000052  do_test fkey6-1.3 {
000053    sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
000054  } {0 0 0}
000055  do_test fkey6-1.4 {
000056    execsql {
000057      BEGIN;
000058      DELETE FROM t1 WHERE x=1;
000059    }
000060  } {}
000061  do_test fkey6-1.5.1 {
000062    sqlite3_db_status db DBSTATUS_DEFERRED_FKS 1
000063  } {0 1 0}
000064  do_test fkey6-1.5.2 {
000065    sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
000066  } {0 1 0}
000067  do_test fkey6-1.6 {
000068    execsql {
000069      ROLLBACK;
000070    }
000071  } {}
000072  do_test fkey6-1.7 {
000073    sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
000074  } {0 0 0}
000075  do_test fkey6-1.8 {
000076    execsql {
000077      PRAGMA defer_foreign_keys=ON;
000078      BEGIN;
000079      DELETE FROM t1 WHERE x=3;
000080    }
000081  } {}
000082  do_test fkey6-1.9 {
000083    sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
000084  } {0 1 0}
000085  
000086  # EVIDENCE-OF: R-21752-26913 The defer_foreign_keys pragma is
000087  # automatically switched off at each COMMIT or ROLLBACK. Hence, the
000088  # defer_foreign_keys pragma must be separately enabled for each
000089  # transaction.
000090  do_execsql_test fkey6-1.10.1 {
000091    PRAGMA defer_foreign_keys;
000092    ROLLBACK;
000093    PRAGMA defer_foreign_keys;
000094    BEGIN;
000095    PRAGMA defer_foreign_keys=ON;
000096    PRAGMA defer_foreign_keys;
000097    COMMIT;
000098    PRAGMA defer_foreign_keys;
000099    BEGIN;
000100  } {1 0 1 0}
000101  do_test fkey6-1.10.2 {
000102    catchsql {DELETE FROM t1 WHERE x=3}
000103  } {1 {FOREIGN KEY constraint failed}}
000104  db eval {ROLLBACK}
000105  
000106  do_test fkey6-1.20 {
000107    execsql {
000108      BEGIN;
000109      DELETE FROM t1 WHERE x=1;
000110    }
000111    sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
000112  } {0 1 0}
000113  do_test fkey6-1.21 {
000114    execsql {
000115      DELETE FROM t2 WHERE y=1;
000116    }
000117    sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
000118  } {0 0 0}
000119  do_test fkey6-1.22 {
000120    execsql {
000121      COMMIT;
000122    }
000123  } {}
000124  
000125  do_execsql_test fkey6-2.1 {
000126    CREATE TABLE p1(a PRIMARY KEY);
000127    INSERT INTO p1 VALUES('one'), ('two');
000128    CREATE TABLE c1(x REFERENCES p1);
000129    INSERT INTO c1 VALUES('two'), ('one');
000130  }
000131  
000132  do_execsql_test fkey6-2.2 {
000133    BEGIN;
000134      PRAGMA defer_foreign_keys = 1;
000135      DELETE FROM p1;
000136    ROLLBACK;
000137    PRAGMA defer_foreign_keys;
000138  } {0}
000139  
000140  do_execsql_test fkey6-2.3 {
000141    BEGIN;
000142      PRAGMA defer_foreign_keys = 1;
000143      DROP TABLE p1;
000144      PRAGMA vdbe_trace = 0;
000145    ROLLBACK;
000146    PRAGMA defer_foreign_keys;
000147  } {0}
000148  
000149  do_execsql_test fkey6-2.4 {
000150    BEGIN;
000151      PRAGMA defer_foreign_keys = 1;
000152      DELETE FROM p1;
000153      DROP TABLE c1;
000154    COMMIT;
000155    PRAGMA defer_foreign_keys;
000156  } {0}
000157  
000158  do_execsql_test fkey6-2.5 {
000159    DROP TABLE p1;
000160    CREATE TABLE p1(a PRIMARY KEY);
000161    INSERT INTO p1 VALUES('one'), ('two');
000162    CREATE TABLE c1(x REFERENCES p1);
000163    INSERT INTO c1 VALUES('two'), ('one');
000164  }
000165  
000166  do_execsql_test fkey6-2.6 {
000167    BEGIN;
000168      PRAGMA defer_foreign_keys = 1;
000169      INSERT INTO c1 VALUES('three');
000170      DROP TABLE c1;
000171    COMMIT;
000172    PRAGMA defer_foreign_keys;
000173  } {0}
000174  
000175  #--------------------------------------------------------------------------
000176  # Test that defer_foreign_keys disables RESTRICT.
000177  #
000178  do_execsql_test 3.1 {
000179    CREATE TABLE p2(a PRIMARY KEY, b);
000180    CREATE TABLE c2(x, y REFERENCES p2 ON DELETE RESTRICT ON UPDATE RESTRICT);
000181    INSERT INTO p2 VALUES(1, 'one');
000182    INSERT INTO p2 VALUES(2, 'two');
000183    INSERT INTO c2 VALUES('i', 1);
000184  }
000185  
000186  do_catchsql_test 3.2.1 {
000187    BEGIN;
000188      UPDATE p2 SET a=a-1;
000189  } {1 {FOREIGN KEY constraint failed}}
000190  do_execsql_test 3.2.2 { COMMIT }
000191  
000192  do_execsql_test 3.2.3 {
000193    BEGIN;
000194      PRAGMA defer_foreign_keys = 1;
000195      UPDATE p2 SET a=a-1;
000196    COMMIT;
000197  }
000198  
000199  do_execsql_test 3.2.4 {
000200    BEGIN;
000201      PRAGMA defer_foreign_keys = 1;
000202      UPDATE p2 SET a=a-1;
000203  }
000204  do_catchsql_test 3.2.5 {
000205    COMMIT;
000206  } {1 {FOREIGN KEY constraint failed}}
000207  do_execsql_test 3.2.6 { ROLLBACK }
000208  
000209  do_execsql_test 3.3.1 {
000210    CREATE TRIGGER p2t AFTER DELETE ON p2 BEGIN
000211      INSERT INTO p2 VALUES(old.a, 'deleted!');
000212    END;
000213  }
000214  do_catchsql_test 3.3.2 {
000215    BEGIN;
000216      DELETE FROM p2 WHERE a=1;
000217  } {1 {FOREIGN KEY constraint failed}}
000218  do_execsql_test 3.3.3 { COMMIT }
000219  
000220  do_execsql_test 3.3.4 {
000221    BEGIN;
000222      PRAGMA defer_foreign_keys = 1;
000223      DELETE FROM p2 WHERE a=1;
000224    COMMIT;
000225    SELECT * FROM p2;
000226  } {0 one 1 deleted!}
000227  
000228  
000229  finish_test