xref: /sqlite-3.40.0/test/without_rowid2.test (revision 63f0eedf) 
1# 2013-11-02
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#
12# This file implements regression tests for SQLite library.  The
13# focus of this file is testing WITHOUT ROWID tables, and especially
14# FOREIGN KEY constraints.
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20ifcapable {!foreignkey} {
21  finish_test
22  return
23}
24
25# Create a table and some data to work with.
26#
27do_test without_rowid2-1.0 {
28  execsql {
29    CREATE TABLE t1(
30      a INT PRIMARY KEY,
31      b INT
32           REFERENCES t1 ON DELETE CASCADE
33           REFERENCES t2,
34      c TEXT,
35      FOREIGN KEY (b,c) REFERENCES t2(x,y) ON UPDATE CASCADE
36    ) WITHOUT rowid;
37  }
38} {}
39do_test without_rowid2-1.1 {
40  execsql {
41    CREATE TABLE t2(
42      x INT PRIMARY KEY,
43      y TEXT
44    ) WITHOUT rowid;
45  }
46} {}
47do_test without_rowid2-1.2 {
48  execsql {
49    CREATE TABLE t3(
50      a INT REFERENCES t2,
51      b INT REFERENCES t1,
52      FOREIGN KEY (a,b) REFERENCES t2(x,y)
53    );
54  }
55} {}
56
57do_test without_rowid2-2.1 {
58  execsql {
59    CREATE TABLE t4(a int primary key) WITHOUT rowid;
60    CREATE TABLE t5(x references t4);
61    CREATE TABLE t6(x references t4);
62    CREATE TABLE t7(x references t4);
63    CREATE TABLE t8(x references t4);
64    CREATE TABLE t9(x references t4);
65    CREATE TABLE t10(x references t4);
66    DROP TABLE t7;
67    DROP TABLE t9;
68    DROP TABLE t5;
69    DROP TABLE t8;
70    DROP TABLE t6;
71    DROP TABLE t10;
72  }
73} {}
74
75do_test without_rowid2-3.1 {
76  execsql {
77    CREATE TABLE t5(a PRIMARY KEY, b, c) WITHOUT rowid;
78    CREATE TABLE t6(
79      d REFERENCES t5,
80      e REFERENCES t5(c)
81    );
82    PRAGMA foreign_key_list(t6);
83  }
84} [concat                                         \
85  {0 0 t5 e c {NO ACTION} {NO ACTION} NONE}       \
86  {1 0 t5 d {} {NO ACTION} {NO ACTION} NONE}      \
87]
88do_test without_rowid2-3.2 {
89  execsql {
90    CREATE TABLE t7(d, e, f,
91      FOREIGN KEY (d, e) REFERENCES t5(a, b)
92    );
93    PRAGMA foreign_key_list(t7);
94  }
95} [concat                                   \
96  {0 0 t5 d a {NO ACTION} {NO ACTION} NONE} \
97  {0 1 t5 e b {NO ACTION} {NO ACTION} NONE} \
98]
99do_test without_rowid2-3.3 {
100  execsql {
101    CREATE TABLE t8(d, e, f,
102      FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL
103    );
104    PRAGMA foreign_key_list(t8);
105  }
106} [concat                        \
107  {0 0 t5 d {} {SET NULL} CASCADE NONE} \
108  {0 1 t5 e {} {SET NULL} CASCADE NONE} \
109]
110do_test without_rowid2-3.4 {
111  execsql {
112    CREATE TABLE t9(d, e, f,
113      FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET DEFAULT
114    );
115    PRAGMA foreign_key_list(t9);
116  }
117} [concat                        \
118  {0 0 t5 d {} {SET DEFAULT} CASCADE NONE} \
119  {0 1 t5 e {} {SET DEFAULT} CASCADE NONE} \
120]
121do_test without_rowid2-3.5 {
122  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
123} {0 0 0}
124
125finish_test
126