AryaWu/sqlite

1# 2009 September 152#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.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19ifcapable {!foreignkey||!trigger} {20  finish_test21  return22}23 24set testprefix fkey325 26# Create a table and some data to work with.27#28do_test fkey3-1.1 {29  execsql {30    PRAGMA foreign_keys=ON;31    CREATE TABLE t1(x INTEGER PRIMARY KEY);32    INSERT INTO t1 VALUES(100);33    INSERT INTO t1 VALUES(101);34    CREATE TABLE t2(y INTEGER REFERENCES t1 (x));35    INSERT INTO t2 VALUES(100);36    INSERT INTO t2 VALUES(101);37    SELECT 1, x FROM t1;38    SELECT 2, y FROM t2;39  }40} {1 100 1 101 2 100 2 101}41 42do_test fkey3-1.2 {43  catchsql {44    DELETE FROM t1 WHERE x=100;45  }46} {1 {FOREIGN KEY constraint failed}}47 48do_test fkey3-1.3 {49  catchsql {50    DROP TABLE t1;51  }52} {1 {FOREIGN KEY constraint failed}}53 54do_test fkey3-1.4 {55  execsql {56    DROP TABLE t2;57  }58} {}59 60do_test fkey3-1.5 {61  execsql {62    DROP TABLE t1;63  }64} {}65 66do_test fkey3-2.1 {67  execsql {68    PRAGMA foreign_keys=ON;69    CREATE TABLE t1(x INTEGER PRIMARY KEY);70    INSERT INTO t1 VALUES(100);71    INSERT INTO t1 VALUES(101);72    CREATE TABLE t2(y INTEGER PRIMARY KEY REFERENCES t1 (x) ON UPDATE SET NULL);73  }74  execsql {75    INSERT INTO t2 VALUES(100);76    INSERT INTO t2 VALUES(101);77    SELECT 1, x FROM t1;78    SELECT 2, y FROM t2;79  }80} {1 100 1 101 2 100 2 101}81 82 83#-------------------------------------------------------------------------84# The following tests - fkey-3.* - test some edge cases to do with 85# inserting rows into tables that have foreign keys where the parent86# table is the same as the child table. Especially cases where the87# new row being inserted matches itself.88#89do_execsql_test 3.1.1 {90  CREATE TABLE t3(a, b, c, d, 91    UNIQUE(a, b),92    FOREIGN KEY(c, d) REFERENCES t3(a, b)93  );94  INSERT INTO t3 VALUES(1, 2, 1, 2);95} {}96do_catchsql_test 3.1.2 {97  INSERT INTO t3 VALUES(NULL, 2, 5, 2);98} {1 {FOREIGN KEY constraint failed}}99do_catchsql_test 3.1.3 {100  INSERT INTO t3 VALUES(NULL, 3, 5, 2);101} {1 {FOREIGN KEY constraint failed}}102 103do_execsql_test 3.2.1 {104  CREATE TABLE t4(a UNIQUE, b REFERENCES t4(a));105}106do_catchsql_test 3.2.2 {107  INSERT INTO t4 VALUES(NULL, 1);108} {1 {FOREIGN KEY constraint failed}}109 110do_execsql_test 3.3.1 {111  CREATE TABLE t5(a INTEGER PRIMARY KEY, b REFERENCES t5(a));112  INSERT INTO t5 VALUES(NULL, 1);113} {}114do_catchsql_test 3.3.2 {115  INSERT INTO t5 VALUES(NULL, 3);116} {1 {FOREIGN KEY constraint failed}}117 118do_execsql_test 3.4.1 {119  CREATE TABLE t6(a INTEGER PRIMARY KEY, b, c, d,120    FOREIGN KEY(c, d) REFERENCES t6(a, b)121  );122  CREATE UNIQUE INDEX t6i ON t6(b, a);123}124do_execsql_test 3.4.2  { INSERT INTO t6 VALUES(NULL, 'a', 1, 'a'); } {}125do_execsql_test 3.4.3  { INSERT INTO t6 VALUES(2, 'a', 2, 'a');    } {}126do_execsql_test 3.4.4  { INSERT INTO t6 VALUES(NULL, 'a', 1, 'a'); } {}127do_execsql_test 3.4.5  { INSERT INTO t6 VALUES(5, 'a', 2, 'a'); } {}128do_catchsql_test 3.4.6 { 129  INSERT INTO t6 VALUES(NULL, 'a', 65, 'a');    130} {1 {FOREIGN KEY constraint failed}}131 132do_execsql_test 3.4.7 {133  INSERT INTO t6 VALUES(100, 'one', 100, 'one');134  DELETE FROM t6 WHERE a = 100;135}136do_execsql_test 3.4.8 {137  INSERT INTO t6 VALUES(100, 'one', 100, 'one');138  UPDATE t6 SET c = 1, d = 'a' WHERE a = 100;139  DELETE FROM t6 WHERE a = 100;140}141 142do_execsql_test 3.5.1 {143  CREATE TABLE t7(a, b, c, d INTEGER PRIMARY KEY,144    FOREIGN KEY(c, d) REFERENCES t7(a, b)145  );146  CREATE UNIQUE INDEX t7i ON t7(a, b);147}148do_execsql_test 3.5.2  { INSERT INTO t7 VALUES('x', 1, 'x', NULL) } {}149do_execsql_test 3.5.3  { INSERT INTO t7 VALUES('x', 2, 'x', 2) } {}150do_catchsql_test 3.5.4  { 151  INSERT INTO t7 VALUES('x', 450, 'x', NULL);152} {1 {FOREIGN KEY constraint failed}}153do_catchsql_test 3.5.5  { 154  INSERT INTO t7 VALUES('x', 450, 'x', 451);155} {1 {FOREIGN KEY constraint failed}}156 157 158do_execsql_test 3.6.1 {159  CREATE TABLE t8(a, b, c, d, e, FOREIGN KEY(c, d) REFERENCES t8(a, b));160  CREATE UNIQUE INDEX t8i1 ON t8(a, b);161  CREATE UNIQUE INDEX t8i2 ON t8(c);162  INSERT INTO t8 VALUES(1, 1, 1, 1, 1);163}164do_catchsql_test 3.6.2 { 165  UPDATE t8 SET d = 2; 166} {1 {FOREIGN KEY constraint failed}}167do_execsql_test 3.6.3 { UPDATE t8 SET d = 1; }168do_execsql_test 3.6.4 { UPDATE t8 SET e = 2; }169 170do_catchsql_test 3.6.5 {171  CREATE TABLE TestTable (172    id INTEGER PRIMARY KEY,173    name text,174    source_id integer not null,175    parent_id integer,176 177    foreign key(source_id, parent_id) references TestTable(source_id, id)178  );179  CREATE UNIQUE INDEX testindex on TestTable(source_id, id);180  PRAGMA foreign_keys=1;181  INSERT INTO TestTable VALUES (1, 'parent', 1, null);182  INSERT INTO TestTable VALUES (2, 'child', 1, 1);183  UPDATE TestTable SET parent_id=1000 where id=2;184} {1 {FOREIGN KEY constraint failed}}185 186finish_test187