• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
1# 2001 September 15
2#
3# The author disclaims copyright to this source code.  In place of
4# 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.tcl
18
19ifcapable {!foreignkey} {
20  finish_test
21  return
22}
23
24# Create a table and some data to work with.
25#
26do_test fkey1-1.0 {
27  execsql {
28    CREATE TABLE t1(
29      a INTEGER PRIMARY KEY,
30      b INTEGER
31           REFERENCES t1 ON DELETE CASCADE
32           REFERENCES t2,
33      c TEXT,
34      FOREIGN KEY (b,c) REFERENCES t2(x,y) ON UPDATE CASCADE
35    );
36  }
37} {}
38do_test fkey1-1.1 {
39  execsql {
40    CREATE TABLE t2(
41      x INTEGER PRIMARY KEY,
42      y TEXT
43    );
44  }
45} {}
46do_test fkey1-1.2 {
47  execsql {
48    CREATE TABLE t3(
49      a INTEGER REFERENCES t2,
50      b INTEGER REFERENCES t1,
51      FOREIGN KEY (a,b) REFERENCES t2(x,y)
52    );
53  }
54} {}
55
56do_test fkey1-2.1 {
57  execsql {
58    CREATE TABLE t4(a integer primary key);
59    CREATE TABLE t5(x references t4);
60    CREATE TABLE t6(x references t4);
61    CREATE TABLE t7(x references t4);
62    CREATE TABLE t8(x references t4);
63    CREATE TABLE t9(x references t4);
64    CREATE TABLE t10(x references t4);
65    DROP TABLE t7;
66    DROP TABLE t9;
67    DROP TABLE t5;
68    DROP TABLE t8;
69    DROP TABLE t6;
70    DROP TABLE t10;
71  }
72} {}
73
74do_test fkey1-3.1 {
75  execsql {
76    CREATE TABLE t5(a PRIMARY KEY, b, c);
77    CREATE TABLE t6(
78      d REFERENCES t5,
79      e REFERENCES t5(c)
80    );
81    PRAGMA foreign_key_list(t6);
82  }
83} [concat                                         \
84  {0 0 t5 e c {NO ACTION} {NO ACTION} NONE}       \
85  {1 0 t5 d {} {NO ACTION} {NO ACTION} NONE}      \
86]
87do_test fkey1-3.2 {
88  execsql {
89    CREATE TABLE t7(d, e, f,
90      FOREIGN KEY (d, e) REFERENCES t5(a, b)
91    );
92    PRAGMA foreign_key_list(t7);
93  }
94} [concat                                   \
95  {0 0 t5 d a {NO ACTION} {NO ACTION} NONE} \
96  {0 1 t5 e b {NO ACTION} {NO ACTION} NONE} \
97]
98do_test fkey1-3.3 {
99  execsql {
100    CREATE TABLE t8(d, e, f,
101      FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL
102    );
103    PRAGMA foreign_key_list(t8);
104  }
105} [concat                        \
106  {0 0 t5 d {} {SET NULL} CASCADE NONE} \
107  {0 1 t5 e {} {SET NULL} CASCADE NONE} \
108]
109do_test fkey1-3.4 {
110  execsql {
111    CREATE TABLE t9(d, e, f,
112      FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET DEFAULT
113    );
114    PRAGMA foreign_key_list(t9);
115  }
116} [concat                        \
117  {0 0 t5 d {} {SET DEFAULT} CASCADE NONE} \
118  {0 1 t5 e {} {SET DEFAULT} CASCADE NONE} \
119]
120
121finish_test
122