summaryrefslogtreecommitdiff
path: root/test/fkey5.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/fkey5.test')
-rw-r--r--test/fkey5.test310
1 files changed, 310 insertions, 0 deletions
diff --git a/test/fkey5.test b/test/fkey5.test
new file mode 100644
index 0000000..40a1a5e
--- /dev/null
+++ b/test/fkey5.test
@@ -0,0 +1,310 @@
+# 2012 December 17
+#
+# 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 tests the PRAGMA foreign_key_check command.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+ifcapable {!foreignkey} {
+ finish_test
+ return
+}
+
+do_test fkey5-1.1 {
+ db eval {
+ CREATE TABLE p1(a INTEGER PRIMARY KEY); INSERT INTO p1 VALUES(88),(89);
+ CREATE TABLE p2(a INT PRIMARY KEY); INSERT INTO p2 VALUES(77),(78);
+ CREATE TABLE p3(a TEXT PRIMARY KEY);
+ INSERT INTO p3 VALUES(66),(67),('alpha'),('BRAVO');
+ CREATE TABLE p4(a TEXT PRIMARY KEY COLLATE nocase);
+ INSERT INTO p4 VALUES('alpha'),('BRAVO'),('55'),('Delta'),('ECHO');
+ CREATE TABLE p5(a INTEGER PRIMARY KEY, b, c, UNIQUE(b,c));
+ INSERT INTO p5 VALUES(1,'Alpha','abc'),(2,'beta','def');
+ CREATE TABLE p6(a INTEGER PRIMARY KEY, b TEXT COLLATE nocase,
+ c TEXT COLLATE rtrim, UNIQUE(b,c));
+ INSERT INTO p6 VALUES(1,'Alpha','abc '),(2,'bETA','def ');
+
+ CREATE TABLE c1(x INTEGER PRIMARY KEY references p1);
+ CREATE TABLE c2(x INTEGER PRIMARY KEY references p2);
+ CREATE TABLE c3(x INTEGER PRIMARY KEY references p3);
+ CREATE TABLE c4(x INTEGER PRIMARY KEY references p4);
+ CREATE TABLE c5(x INT references p1);
+ CREATE TABLE c6(x INT references p2);
+ CREATE TABLE c7(x INT references p3);
+ CREATE TABLE c8(x INT references p4);
+ CREATE TABLE c9(x TEXT UNIQUE references p1);
+ CREATE TABLE c10(x TEXT UNIQUE references p2);
+ CREATE TABLE c11(x TEXT UNIQUE references p3);
+ CREATE TABLE c12(x TEXT UNIQUE references p4);
+ CREATE TABLE c13(x TEXT COLLATE nocase references p3);
+ CREATE TABLE c14(x TEXT COLLATE nocase references p4);
+ CREATE TABLE c15(x, y, FOREIGN KEY(x,y) REFERENCES p5(b,c));
+ CREATE TABLE c16(x, y, FOREIGN KEY(x,y) REFERENCES p5(c,b));
+ CREATE TABLE c17(x, y, FOREIGN KEY(x,y) REFERENCES p6(b,c));
+ CREATE TABLE c18(x, y, FOREIGN KEY(x,y) REFERENCES p6(c,b));
+ CREATE TABLE c19(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
+ FOREIGN KEY(x,y) REFERENCES p5(b,c));
+ CREATE TABLE c20(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
+ FOREIGN KEY(x,y) REFERENCES p5(c,b));
+ CREATE TABLE c21(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
+ FOREIGN KEY(x,y) REFERENCES p6(b,c));
+ CREATE TABLE c22(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
+ FOREIGN KEY(x,y) REFERENCES p6(c,b));
+
+ PRAGMA foreign_key_check;
+ }
+} {}
+do_test fkey5-1.2 {
+ db eval {
+ INSERT INTO c1 VALUES(90),(87),(88);
+ PRAGMA foreign_key_check;
+ }
+} {c1 87 p1 0 c1 90 p1 0}
+do_test fkey5-1.3 {
+ db eval {
+ PRAGMA foreign_key_check(c1);
+ }
+} {c1 87 p1 0 c1 90 p1 0}
+do_test fkey5-1.4 {
+ db eval {
+ PRAGMA foreign_key_check(c2);
+ }
+} {}
+
+do_test fkey5-2.0 {
+ db eval {
+ INSERT INTO c5 SELECT x FROM c1;
+ DELETE FROM c1;
+ PRAGMA foreign_key_check;
+ }
+} {c5 1 p1 0 c5 3 p1 0}
+do_test fkey5-2.1 {
+ db eval {
+ PRAGMA foreign_key_check(c5);
+ }
+} {c5 1 p1 0 c5 3 p1 0}
+do_test fkey5-2.2 {
+ db eval {
+ PRAGMA foreign_key_check(c1);
+ }
+} {}
+
+do_test fkey5-3.0 {
+ db eval {
+ INSERT INTO c9 SELECT x FROM c5;
+ DELETE FROM c5;
+ PRAGMA foreign_key_check;
+ }
+} {c9 1 p1 0 c9 3 p1 0}
+do_test fkey5-3.1 {
+ db eval {
+ PRAGMA foreign_key_check(c9);
+ }
+} {c9 1 p1 0 c9 3 p1 0}
+do_test fkey5-3.2 {
+ db eval {
+ PRAGMA foreign_key_check(c5);
+ }
+} {}
+
+do_test fkey5-4.0 {
+ db eval {
+ DELETE FROM c9;
+ INSERT INTO c2 VALUES(79),(77),(76);
+ PRAGMA foreign_key_check;
+ }
+} {c2 76 p2 0 c2 79 p2 0}
+do_test fkey5-4.1 {
+ db eval {
+ PRAGMA foreign_key_check(c2);
+ }
+} {c2 76 p2 0 c2 79 p2 0}
+do_test fkey5-4.2 {
+ db eval {
+ INSERT INTO c6 SELECT x FROM c2;
+ DELETE FROM c2;
+ PRAGMA foreign_key_check;
+ }
+} {c6 1 p2 0 c6 3 p2 0}
+do_test fkey5-4.3 {
+ db eval {
+ PRAGMA foreign_key_check(c6);
+ }
+} {c6 1 p2 0 c6 3 p2 0}
+do_test fkey5-4.4 {
+ db eval {
+ INSERT INTO c10 SELECT x FROM c6;
+ DELETE FROM c6;
+ PRAGMA foreign_key_check;
+ }
+} {c10 1 p2 0 c10 3 p2 0}
+do_test fkey5-4.5 {
+ db eval {
+ PRAGMA foreign_key_check(c10);
+ }
+} {c10 1 p2 0 c10 3 p2 0}
+
+do_test fkey5-5.0 {
+ db eval {
+ DELETE FROM c10;
+ INSERT INTO c3 VALUES(68),(67),(65);
+ PRAGMA foreign_key_check;
+ }
+} {c3 65 p3 0 c3 68 p3 0}
+do_test fkey5-5.1 {
+ db eval {
+ PRAGMA foreign_key_check(c3);
+ }
+} {c3 65 p3 0 c3 68 p3 0}
+do_test fkey5-5.2 {
+ db eval {
+ INSERT INTO c7 SELECT x FROM c3;
+ INSERT INTO c7 VALUES('Alpha'),('alpha'),('foxtrot');
+ DELETE FROM c3;
+ PRAGMA foreign_key_check;
+ }
+} {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
+do_test fkey5-5.3 {
+ db eval {
+ PRAGMA foreign_key_check(c7);
+ }
+} {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
+do_test fkey5-5.4 {
+ db eval {
+ INSERT INTO c11 SELECT x FROM c7;
+ DELETE FROM c7;
+ PRAGMA foreign_key_check;
+ }
+} {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
+do_test fkey5-5.5 {
+ db eval {
+ PRAGMA foreign_key_check(c11);
+ }
+} {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
+
+do_test fkey5-6.0 {
+ db eval {
+ DELETE FROM c11;
+ INSERT INTO c4 VALUES(54),(55),(56);
+ PRAGMA foreign_key_check;
+ }
+} {c4 54 p4 0 c4 56 p4 0}
+do_test fkey5-6.1 {
+ db eval {
+ PRAGMA foreign_key_check(c4);
+ }
+} {c4 54 p4 0 c4 56 p4 0}
+do_test fkey5-6.2 {
+ db eval {
+ INSERT INTO c8 SELECT x FROM c4;
+ INSERT INTO c8 VALUES('Alpha'),('ALPHA'),('foxtrot');
+ DELETE FROM c4;
+ PRAGMA foreign_key_check;
+ }
+} {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
+do_test fkey5-6.3 {
+ db eval {
+ PRAGMA foreign_key_check(c8);
+ }
+} {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
+do_test fkey5-6.4 {
+ db eval {
+ INSERT INTO c12 SELECT x FROM c8;
+ DELETE FROM c8;
+ PRAGMA foreign_key_check;
+ }
+} {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
+do_test fkey5-6.5 {
+ db eval {
+ PRAGMA foreign_key_check(c12);
+ }
+} {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
+
+do_test fkey5-7.1 {
+ db eval {
+ INSERT OR IGNORE INTO c13 SELECT * FROM c12;
+ INSERT OR IGNORE INTO C14 SELECT * FROM c12;
+ DELETE FROM c12;
+ PRAGMA foreign_key_check;
+ }
+} {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0 c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0}
+do_test fkey5-7.2 {
+ db eval {
+ PRAGMA foreign_key_check(c14);
+ }
+} {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0}
+do_test fkey5-7.3 {
+ db eval {
+ PRAGMA foreign_key_check(c13);
+ }
+} {c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0}
+
+do_test fkey5-8.0 {
+ db eval {
+ DELETE FROM c13;
+ DELETE FROM c14;
+ INSERT INTO c19 VALUES('alpha','abc');
+ PRAGMA foreign_key_check(c19);
+ }
+} {c19 1 p5 0}
+do_test fkey5-8.1 {
+ db eval {
+ DELETE FROM c19;
+ INSERT INTO c19 VALUES('Alpha','abc');
+ PRAGMA foreign_key_check(c19);
+ }
+} {}
+do_test fkey5-8.2 {
+ db eval {
+ INSERT INTO c20 VALUES('Alpha','abc');
+ PRAGMA foreign_key_check(c20);
+ }
+} {c20 1 p5 0}
+do_test fkey5-8.3 {
+ db eval {
+ DELETE FROM c20;
+ INSERT INTO c20 VALUES('abc','Alpha');
+ PRAGMA foreign_key_check(c20);
+ }
+} {}
+do_test fkey5-8.4 {
+ db eval {
+ INSERT INTO c21 VALUES('alpha','abc ');
+ PRAGMA foreign_key_check(c21);
+ }
+} {}
+do_test fkey5-8.5 {
+ db eval {
+ DELETE FROM c21;
+ INSERT INTO c19 VALUES('Alpha','abc');
+ PRAGMA foreign_key_check(c21);
+ }
+} {}
+do_test fkey5-8.6 {
+ db eval {
+ INSERT INTO c22 VALUES('Alpha','abc');
+ PRAGMA foreign_key_check(c22);
+ }
+} {c22 1 p6 0}
+do_test fkey5-8.7 {
+ db eval {
+ DELETE FROM c22;
+ INSERT INTO c22 VALUES('abc ','ALPHA');
+ PRAGMA foreign_key_check(c22);
+ }
+} {}
+
+
+
+finish_test
generated by cgit v1.2.3 (git 2.25.1) at 2024-05-09 18:38:57 +0000