AryaWu/sqlite

1# 2011 August 222#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library.12#13# This file implements tests for foreign keys. Specifically, it tests14# that ticket b1d3a2e531 has been fixed.15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20ifcapable {!foreignkey||!trigger} {21  finish_test22  return23}24set testprefix tkt-b1d3a2e53125 26do_execsql_test 1.0 { PRAGMA foreign_keys = ON }27 28do_execsql_test 1.1 {29  CREATE TABLE pp(x PRIMARY KEY);30  CREATE TABLE cc(y REFERENCES pp DEFERRABLE INITIALLY DEFERRED);31  INSERT INTO pp VALUES('abc');32  INSERT INTO cc VALUES('abc');33}34do_execsql_test 1.2 {35  BEGIN;36    DROP TABLE pp;37    DROP TABLE cc;38  COMMIT;39}40do_execsql_test 1.3 {41  CREATE TABLE pp(x PRIMARY KEY);42  CREATE TABLE cc(y REFERENCES pp DEFERRABLE INITIALLY DEFERRED);43  INSERT INTO pp VALUES('abc');44  INSERT INTO cc VALUES('abc');45}46do_execsql_test 1.4 {47  BEGIN;48    DROP TABLE cc;49    DROP TABLE pp;50  COMMIT;51}52 53do_execsql_test 2.1 {54  CREATE TABLE pp(x PRIMARY KEY);55  CREATE TABLE cc(56    y INTEGER PRIMARY KEY REFERENCES pp DEFERRABLE INITIALLY DEFERRED57  );58  INSERT INTO pp VALUES(5);59  INSERT INTO cc VALUES(5);60}61do_execsql_test 2.2 {62  BEGIN;63    DROP TABLE pp;64    DROP TABLE cc;65  COMMIT;66}67do_execsql_test 2.3 {68  CREATE TABLE pp(x PRIMARY KEY);69  CREATE TABLE cc(70    y INTEGER PRIMARY KEY REFERENCES pp DEFERRABLE INITIALLY DEFERRED71  );72  INSERT INTO pp VALUES(5);73  INSERT INTO cc VALUES(5);74}75do_execsql_test 2.4 {76  BEGIN;77    DROP TABLE cc;78    DROP TABLE pp;79  COMMIT;80}81 82do_execsql_test 3.1 {83  CREATE TABLE pp1(x PRIMARY KEY);84  CREATE TABLE cc1(y REFERENCES pp1 DEFERRABLE INITIALLY DEFERRED);85 86  CREATE TABLE pp2(x PRIMARY KEY);87  CREATE TABLE cc2(y REFERENCES pp1 DEFERRABLE INITIALLY DEFERRED);88 89  INSERT INTO pp1 VALUES(2200);90  INSERT INTO cc1 VALUES(NULL);91 92  INSERT INTO pp2 VALUES(2200);93  INSERT INTO cc2 VALUES(2200);94}95do_catchsql_test 3.2 {96  BEGIN;97    DELETE FROM pp2;98    DROP TABLE pp1;99    DROP TABLE cc1;100  COMMIT;101} {1 {FOREIGN KEY constraint failed}}102do_catchsql_test 3.3 {103    DROP TABLE cc2;104  COMMIT;105} {0 {}}106 107 108 109finish_test110