3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
13 # This file implements tests for foreign keys.
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
20 ifcapable {!foreignkey} {
24 do_execsql_test 1.0 { PRAGMA foreign_keys = 1; }
27 foreach {tn use_stmt sql schema} {
28 1 1 "DELETE FROM p1" {
29 CREATE TABLE p1(a PRIMARY KEY);
30 CREATE TABLE c1(b REFERENCES p1);
33 2.1 0 "DELETE FROM p1" {
34 CREATE TABLE p1(a PRIMARY KEY);
35 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE);
37 2.2 0 "DELETE FROM p1" {
38 CREATE TABLE p1(a PRIMARY KEY);
39 CREATE TABLE c1(b REFERENCES p1 ON DELETE SET NULL);
41 2.3 1 "DELETE FROM p1" {
42 CREATE TABLE p1(a PRIMARY KEY);
43 CREATE TABLE c1(b REFERENCES p1 ON DELETE SET DEFAULT);
46 3 1 "DELETE FROM p1" {
47 CREATE TABLE p1(a PRIMARY KEY);
48 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE);
49 CREATE TRIGGER ct1 AFTER DELETE ON c1 BEGIN
50 INSERT INTO p1 VALUES('x');
54 4 1 "DELETE FROM p1" {
55 CREATE TABLE p1(a PRIMARY KEY);
56 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
57 CREATE TABLE cc1(d REFERENCES c1);
60 5.1 0 "DELETE FROM p1" {
61 CREATE TABLE p1(a PRIMARY KEY);
62 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
63 CREATE TABLE cc1(d REFERENCES c1 ON DELETE CASCADE);
65 5.2 0 "DELETE FROM p1" {
66 CREATE TABLE p1(a PRIMARY KEY);
67 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
68 CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET NULL);
70 5.3 1 "DELETE FROM p1" {
71 CREATE TABLE p1(a PRIMARY KEY);
72 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
73 CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET DEFAULT);
76 6.1 1 "UPDATE p1 SET a = ?" {
77 CREATE TABLE p1(a PRIMARY KEY);
78 CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c);
80 6.2 0 "UPDATE OR IGNORE p1 SET a = ?" {
81 CREATE TABLE p1(a PRIMARY KEY);
82 CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c);
84 6.3 1 "UPDATE OR IGNORE p1 SET a = ?" {
85 CREATE TABLE p1(a PRIMARY KEY);
86 CREATE TABLE c1(b REFERENCES p1 ON UPDATE CASCADE, c);
88 6.4 1 "UPDATE OR IGNORE p1 SET a = ?" {
89 CREATE TABLE p1(a PRIMARY KEY);
90 CREATE TABLE c1(b NOT NULL REFERENCES p1 ON UPDATE SET NULL, c);
97 set stmt [sqlite3_prepare_v2 db $sql -1 dummy]
98 set ret [uses_stmt_journal $stmt]
99 sqlite3_finalize $stmt