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 18set testprefix fkey8 19 20ifcapable {!foreignkey} { 21 finish_test 22 return 23} 24do_execsql_test 1.0 { PRAGMA foreign_keys = 1; } 25 26 27foreach {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); 31 } 32 33 2.1 0 "DELETE FROM p1" { 34 CREATE TABLE p1(a PRIMARY KEY); 35 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE); 36 } 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); 40 } 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); 44 } 45 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'); 51 END; 52 } 53 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); 58 } 59 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); 64 } 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); 69 } 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); 74 } 75 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); 79 } 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); 83 } 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); 87 } 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); 91 } 92 93} { 94 drop_all_tables 95 do_test 1.$tn { 96 execsql $schema 97 set stmt [sqlite3_prepare_v2 db $sql -1 dummy] 98 set ret [uses_stmt_journal $stmt] 99 sqlite3_finalize $stmt 100 set ret 101 } $use_stmt 102} 103 104 105finish_test 106