# 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for foreign keys. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix fkey8 ifcapable {!foreignkey} { finish_test return } do_execsql_test 1.0 { PRAGMA foreign_keys = 1; } foreach {tn use_stmt sql schema} { 1 1 "DELETE FROM p1" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1); } 2.1 0 "DELETE FROM p1" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE); } 2.2 0 "DELETE FROM p1" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON DELETE SET NULL); } 2.3 1 "DELETE FROM p1" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON DELETE SET DEFAULT); } 3 1 "DELETE FROM p1" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE); CREATE TRIGGER ct1 AFTER DELETE ON c1 BEGIN INSERT INTO p1 VALUES('x'); END; } 4 1 "DELETE FROM p1" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); CREATE TABLE cc1(d REFERENCES c1); } 5.1 0 "DELETE FROM p1" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); CREATE TABLE cc1(d REFERENCES c1 ON DELETE CASCADE); } 5.2 0 "DELETE FROM p1" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET NULL); } 5.3 1 "DELETE FROM p1" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET DEFAULT); } 6.1 1 "UPDATE p1 SET a = ?" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c); } 6.2 0 "UPDATE OR IGNORE p1 SET a = ?" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c); } 6.3 1 "UPDATE OR IGNORE p1 SET a = ?" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON UPDATE CASCADE, c); } 6.4 1 "UPDATE OR IGNORE p1 SET a = ?" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b NOT NULL REFERENCES p1 ON UPDATE SET NULL, c); } } { drop_all_tables do_test 1.$tn { execsql $schema set stmt [sqlite3_prepare_v2 db $sql -1 dummy] set ret [uses_stmt_journal $stmt] sqlite3_finalize $stmt set ret } $use_stmt } finish_test