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 fkey119 20ifcapable {!foreignkey} {21  finish_test22  return23}24 25# Create a table and some data to work with.26#27do_test fkey1-1.0 {28  execsql {29    CREATE TABLE t1(30      a INTEGER PRIMARY KEY,31      b INTEGER32           REFERENCES t1 ON DELETE CASCADE33           REFERENCES t2,34      c TEXT,35      FOREIGN KEY (b,c) REFERENCES t2(x,y) ON UPDATE CASCADE36    );37  }38} {}39do_test fkey1-1.1 {40  execsql {41    CREATE TABLE t2(42      x INTEGER PRIMARY KEY,43      y TEXT44    );45  }46} {}47do_test fkey1-1.2 {48  execsql {49    CREATE TABLE t3(50      a INTEGER REFERENCES t2,51      b INTEGER REFERENCES t1,52      FOREIGN KEY (a,b) REFERENCES t2(x,y)53    );54  }55} {}56 57do_test fkey1-2.1 {58  execsql {59    CREATE TABLE t4(a integer primary key);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 fkey1-3.1 {76  execsql {77    CREATE TABLE t5(a PRIMARY KEY, b, c);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 fkey1-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 fkey1-3.3 {100  execsql {101    CREATE TABLE t8(d, e, f,102      FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL103    );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 fkey1-3.4 {111  execsql {112    CREATE TABLE t9(d, e, f,113      FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET DEFAULT114    );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 fkey1-3.5 {122  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0123} {0 0 0}124 125# Stress the dequoting logic.  The first test is not so bad.126do_execsql_test fkey1-4.0 {127  PRAGMA foreign_keys=ON;128  CREATE TABLE "xx1"("xx2" TEXT PRIMARY KEY, "xx3" TEXT);129  INSERT INTO "xx1"("xx2","xx3") VALUES('abc','def');130  CREATE TABLE "xx4"("xx5" TEXT REFERENCES "xx1" ON DELETE CASCADE);131  INSERT INTO "xx4"("xx5") VALUES('abc');132  INSERT INTO "xx1"("xx2","xx3") VALUES('uvw','xyz');133  SELECT 1, "xx5" FROM "xx4";134  DELETE FROM "xx1";135  SELECT 2, "xx5" FROM "xx4";136} {1 abc}137 138# This case is identical to the previous except the "xx" in each name139# is changed to a single escaped double-quote character.140do_execsql_test fkey1-4.1 {141  PRAGMA foreign_keys=ON;142  CREATE TABLE """1"("""2" TEXT PRIMARY KEY, """3" TEXT);143  INSERT INTO """1"("""2","""3") VALUES('abc','def');144  CREATE TABLE """4"("""5" TEXT REFERENCES """1" ON DELETE CASCADE);145  INSERT INTO """4"("""5") VALUES('abc');146  INSERT INTO """1"("""2","""3") VALUES('uvw','xyz');147  SELECT 1, """5" FROM """4";148  DELETE FROM """1";149  SELECT 2, """5" FROM """4";150} {1 abc}151do_execsql_test fkey1-4.2 {152  PRAGMA table_info="""1";153} {0 {"2} TEXT 0 {} 1 1 {"3} TEXT 0 {} 0}154 155#-------------------------------------------------------------------------156#157do_execsql_test fkey1-5.1 {158  CREATE TABLE t11(159    x INTEGER PRIMARY KEY, 160    parent REFERENCES t11 ON DELETE CASCADE161  );162  INSERT INTO t11 VALUES (1, NULL), (2, 1), (3, 2);163} {}164 165# The REPLACE part of this statement deletes the row (2, 1). Then the 166# DELETE CASCADE caused by deleting that row removes the (3, 2) row. Which167# would have been the parent of the new row being inserted. Causing an168# FK violation.169#170do_catchsql_test fkey1-5.2 {171  INSERT OR REPLACE INTO t11 VALUES (2, 3);172} {1 {FOREIGN KEY constraint failed}}173 174# Make sure sqlite3_trace() output works with triggers used to implement175# FK constraints176#177ifcapable trace {178  proc sqltrace {txt} {179    global traceoutput180    lappend traceoutput $txt181  }182  do_test fkey1-5.2.1 {183    unset -nocomplain traceoutput184    db trace sqltrace185    catch {db eval {INSERT OR REPLACE INTO t11 VALUES(2,3);}}186    set traceoutput187  } {{INSERT OR REPLACE INTO t11 VALUES(2,3);} {INSERT OR REPLACE INTO t11 VALUES(2,3);} {INSERT OR REPLACE INTO t11 VALUES(2,3);}}188}189 190# A similar test to the above.191do_execsql_test fkey1-5.3 {192  CREATE TABLE Foo (193    Id INTEGER PRIMARY KEY, 194    ParentId INTEGER REFERENCES Foo(Id) ON DELETE CASCADE, C1195  );196  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (1, null, 'A');197  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 1, 'A-2-1');198  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (3, 2, 'A-3-2');199  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (4, 3, 'A-4-3');200}201do_catchsql_test fkey1-5.4 {202  INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 3, 'A-2-3');203} {1 {FOREIGN KEY constraint failed}}204 205#-------------------------------------------------------------------------206# Check that foreign key processing is not fooled by partial indexes207# on the parent table.208#209do_execsql_test 6.0 {210  CREATE TABLE p1(x, y);211  CREATE UNIQUE INDEX p1x ON p1(x) WHERE y<2;212  INSERT INTO p1 VALUES(1, 1);213  CREATE TABLE c1(a REFERENCES p1(x));214}215 216do_catchsql_test 6.1 {217  INSERT INTO c1 VALUES(1);218} {1 {foreign key mismatch - "c1" referencing "p1"}}219 220do_execsql_test 6.2 {221  CREATE UNIQUE INDEX p1x2 ON p1(x);222  INSERT INTO c1 VALUES(1);223} {}224 225# 2021-07-03 https://sqlite.org/forum/forumpost/a6b0c05277226# 2021-07-07 https://sqlite.org/forum/forumpost/79c9e4797d227# Failure to allocate enough registers in the VDBE for a228# PRAGMA foreign_key_check when the foreign key has more229# columns than the table.230#231reset_db232do_execsql_test 7.1 {233  PRAGMA foreign_keys=OFF;234  CREATE TABLE t1(a,b,c,FOREIGN KEY(a,a,a,a,a,a,a,a,a,a,a,a,a,a) REFERENCES t0);235  INSERT INTO t1 VALUES(1,2,3);236  PRAGMA foreign_key_check;237} {t1 1 t0 0}238do_execsql_test 7.2 {239  DROP TABLE t1;240  CREATE TABLE t1(a,b,c AS(1),d, FOREIGN KEY(c,d,b,a,b,d,b,c) REFERENCES t0);241  PRAGMA foreign_key_check;242} {}243 244# 2021-12-31 forum https://sqlite.org/forum/forumpost/24bd1fef7e9323ef245# Memory leak caused by sqlite3NestedParse() running on a corrupt system246# table.  Discovered by Jingzhou Fu.247#248reset_db249do_execsql_test 8.1 {250  PRAGMA writable_schema=ON;251  PRAGMA foreign_keys = ON;252  CREATE TABLE sqlite_stat1 (tbl INTEGER PRIMARY KEY DESC, idx UNIQUE DEFAULT NULL) WITHOUT ROWID;253  PRAGMA writable_schema=OFF;254  CREATE TABLE sqlsim4(stat PRIMARY KEY);;255  CREATE TABLE t1(sqlsim7 REFERENCES sqlite_stat1 ON DELETE CASCADE);256  DROP table "sqlsim4";257} {}258# 2022-01-01 dbsqlfuzz 1c57440219f6f0aedf5e8f72a8ddd75f15aea381259# Follow-up case to the above.  Assertion is not true if the schema260# is corrupt.261reset_db262database_may_be_corrupt263do_execsql_test 8.2 {264  CREATE TABLE t1(a REFERENCES sqlite_stat1 ON DELETE CASCADE);265  CREATE TABLE t2(a TEXT PRIMARY KEY);266  PRAGMA writable_schema=ON;267  CREATE TABLE sqlite_stat1(tbl INTEGER PRIMARY KEY DESC, idx UNIQUE DEFAULT NULL) WITHOUT ROWID;268  UPDATE sqlite_schema SET name='sqlite_autoindex_sqlite_stat1_1' WHERE name='sqlite_autoindex_sqlite_stat1_2';269  PRAGMA writable_schema=RESET;270} {}271do_catchsql_test 8.3 {272  REINDEX;273} {1 {database disk image is malformed}}274 275# 2023-04-13 https://bugs.chromium.org/p/chromium/issues/detail?id=1405220276# Avoid double-de-quoting of table names when processing foreign keys.277#278reset_db279do_execsql_test 9.1 {280  PRAGMA foreign_keys = ON;281  CREATE TABLE """1"("""2", """3" PRIMARY KEY);282  CREATE TABLE """4"("""5" REFERENCES """1" ON DELETE RESTRICT);283  DELETE FROM """1";284}285 286finish_test287