[sql] Import SQLite 3.17.0.

third_party/sqlite/src/test/fkey6.test

-# 2013-07-11
+# 2012 December 17
 #
 # The author disclaims copyright to this source code.  In place of
 # a legal notice, here is a blessing:
 #
 #    May you do good and not evil.
 #    May you find forgiveness for yourself and forgive others.
 #    May you share freely, never taking more than you give.
 #
 #***********************************************************************
 # This file implements regression tests for SQLite library.
 #
 # This file tests the PRAGMA defer_foreign_keys and 
 # SQLITE_DBSTATUS_DEFERRED_FKS
 #
 # EVIDENCE-OF: R-18981-16292 When the defer_foreign_keys PRAGMA is on,
 # enforcement of all foreign key constraints is delayed until the
 # outermost transaction is committed.
 #
 # 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 {
   PRAGMA defer_foreign_keys;
 } {0}
 
@@ skipping 128 matching lines @@
 
 do_execsql_test fkey6-2.6 {
   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