AryaWu/sqlite

1# 2001 September 152#3# The author disclaims copyright to this source code.  In place of4# 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.tcl18set testprefix fkey719 20ifcapable {!foreignkey} {21  finish_test22  return23}24 25do_execsql_test 1.1 {26  PRAGMA foreign_keys = 1;27 28  CREATE TABLE s1(a PRIMARY KEY, b);29  CREATE TABLE par(a, b REFERENCES s1, c UNIQUE, PRIMARY KEY(a));30 31  CREATE TABLE c1(a, b REFERENCES par);32  CREATE TABLE c2(a, b REFERENCES par);33  CREATE TABLE c3(a, b REFERENCES par(c));34}35 36proc auth {op tbl args} {37  if {$op == "SQLITE_READ"} { set ::tbls($tbl) 1 }38  return "SQLITE_OK"39}40db auth auth41db cache size 042proc do_tblsread_test {tn sql tbllist} {43  array unset ::tbls44  uplevel [list execsql $sql]45  uplevel [list do_test $tn {lsort [array names ::tbls]} $tbllist]46}47 48do_tblsread_test 1.2 { UPDATE par SET b=? WHERE a=? } {par s1}49do_tblsread_test 1.3 { UPDATE par SET a=? WHERE b=? } {c1 c2 par}50do_tblsread_test 1.4 { UPDATE par SET c=? WHERE b=? } {c3 par}51do_tblsread_test 1.5 { UPDATE par SET a=?,b=?,c=? WHERE b=? } {c1 c2 c3 par s1}52 53ifcapable incrblob {54  do_execsql_test 2.0 {55    CREATE TABLE pX(x PRIMARY KEY);56    CREATE TABLE cX(a INTEGER PRIMARY KEY, b REFERENCES pX);57  }58  59  do_catchsql_test 2.1 {60    INSERT INTO cX VALUES(11, zeroblob(40));61  } {1 {FOREIGN KEY constraint failed}}62  63  do_test 2.2 {64    set stmt [sqlite3_prepare_v2 db "INSERT INTO cX VALUES(11, ?)" -1]65    sqlite3_bind_zeroblob $stmt 1 4566    sqlite3_step $stmt67    sqlite3_finalize $stmt68  } {SQLITE_CONSTRAINT}69}70 71ifcapable stat4 {72  do_execsql_test 3.0 {73    CREATE TABLE p4 (id INTEGER NOT NULL PRIMARY KEY);74    INSERT INTO p4 VALUES(1), (2), (3);75 76    CREATE TABLE c4(x INTEGER REFERENCES p4(id) DEFERRABLE INITIALLY DEFERRED);77    CREATE INDEX c4_x ON c4(x);78    INSERT INTO c4 VALUES(1), (2), (3);79 80    ANALYZE;81    INSERT INTO p4(id) VALUES(4);82  }83}84 85 86do_execsql_test 4.0 {87  PRAGMA foreign_keys = true;88  CREATE TABLE parent(89    p PRIMARY KEY90  );91  CREATE TABLE child(92    c UNIQUE REFERENCES parent(p)93  );94}95 96do_catchsql_test 4.1 {97  INSERT OR FAIL INTO child VALUES(123), (123);98} {1 {FOREIGN KEY constraint failed}}99 100do_execsql_test 4.2 {101  SELECT * FROM child;102} {}103 104do_execsql_test 4.3 {105  PRAGMA foreign_key_check;106} {}107 108do_catchsql_test 4.4 {109  INSERT INTO parent VALUES(123);110  INSERT OR FAIL INTO child VALUES(123), (123);111} {1 {UNIQUE constraint failed: child.c}}112 113do_execsql_test 4.5 {114  SELECT * FROM child;115} {123}116 117do_execsql_test 4.6 {118  PRAGMA foreign_key_check;119} {}120 121finish_test122