AryaWu/sqlite
0
1# 2009 October 72#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#12# This file implements tests to verify the "testable statements" in the13# foreignkeys.in document.14#15# The tests in this file are arranged to mirror the structure of 16# foreignkey.in, with one exception: The statements in section 2, which 17# deals with enabling/disabling foreign key support, is tested first,18# before section 1. This is because some statements in section 2 deal19# with builds that do not include complete foreign key support (because20# either SQLITE_OMIT_TRIGGER or SQLITE_OMIT_FOREIGN_KEY was defined21# at build time).22#23 24set testdir [file dirname $argv0]25source $testdir/tester.tcl26 27proc eqp {sql {db db}} { 28 uplevel [subst -nocommands {29 set eqpres [list]30 $db eval "$sql" {31 lappend eqpres [set detail]32 }33 set eqpres34 }]35}36 37proc do_detail_test {tn sql res} {38 set normalres [list {*}$res]39 uplevel [subst -nocommands {40 do_test $tn {41 eqp { $sql }42 } {$normalres}43 }]44}45 46###########################################################################47### SECTION 2: Enabling Foreign Key Support48###########################################################################49 50#-------------------------------------------------------------------------51# EVIDENCE-OF: R-37672-59189 In order to use foreign key constraints in52# SQLite, the library must be compiled with neither53# SQLITE_OMIT_FOREIGN_KEY nor SQLITE_OMIT_TRIGGER defined.54#55ifcapable trigger&&foreignkey {56 do_test e_fkey-1 {57 execsql {58 PRAGMA foreign_keys = ON;59 CREATE TABLE p(i PRIMARY KEY);60 CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);61 INSERT INTO p VALUES('hello');62 INSERT INTO c VALUES('hello');63 UPDATE p SET i = 'world';64 SELECT * FROM c;65 }66 } {world}67}68 69#-------------------------------------------------------------------------70# Test the effects of defining OMIT_TRIGGER but not OMIT_FOREIGN_KEY.71#72# EVIDENCE-OF: R-10109-20452 If SQLITE_OMIT_TRIGGER is defined but73# SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to74# version 3.6.19 (2009-10-14) - foreign key definitions are parsed and75# may be queried using PRAGMA foreign_key_list, but foreign key76# constraints are not enforced.77#78# Specifically, test that "PRAGMA foreign_keys" is a no-op in this case.79# When using the pragma to query the current setting, 0 rows are returned.80#81# EVIDENCE-OF: R-22567-44039 The PRAGMA foreign_keys command is a no-op82# in this configuration.83#84# EVIDENCE-OF: R-41784-13339 Tip: If the command "PRAGMA foreign_keys"85# returns no data instead of a single row containing "0" or "1", then86# the version of SQLite you are using does not support foreign keys87# (either because it is older than 3.6.19 or because it was compiled88# with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).89#90reset_db91ifcapable !trigger&&foreignkey {92 do_test e_fkey-2.1 {93 execsql {94 PRAGMA foreign_keys = ON;95 CREATE TABLE p(i PRIMARY KEY);96 CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);97 INSERT INTO p VALUES('hello');98 INSERT INTO c VALUES('hello');99 UPDATE p SET i = 'world';100 SELECT * FROM c;101 }102 } {hello}103 do_test e_fkey-2.2 {104 execsql { PRAGMA foreign_key_list(c) }105 } {0 0 p j {} CASCADE {NO ACTION} NONE}106 do_test e_fkey-2.3 {107 execsql { PRAGMA foreign_keys }108 } {}109}110 111 112#-------------------------------------------------------------------------113# Test the effects of defining OMIT_FOREIGN_KEY.114#115# EVIDENCE-OF: R-58428-36660 If OMIT_FOREIGN_KEY is defined, then116# foreign key definitions cannot even be parsed (attempting to specify a117# foreign key definition is a syntax error).118#119# Specifically, test that foreign key constraints cannot even be parsed 120# in such a build.121#122reset_db123ifcapable !foreignkey {124 do_test e_fkey-3.1 {125 execsql { CREATE TABLE p(i PRIMARY KEY) }126 catchsql { CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE) }127 } {1 {near "ON": syntax error}}128 do_test e_fkey-3.2 {129 # This is allowed, as in this build, "REFERENCES" is not a keyword.130 # The declared datatype of column j is "REFERENCES p".131 execsql { CREATE TABLE c(j REFERENCES p) }132 } {}133 do_test e_fkey-3.3 {134 execsql { PRAGMA table_info(c) }135 } {0 j {REFERENCES p} 0 {} 0}136 do_test e_fkey-3.4 {137 execsql { PRAGMA foreign_key_list(c) }138 } {}139 do_test e_fkey-3.5 {140 execsql { PRAGMA foreign_keys }141 } {}142}143 144ifcapable !foreignkey||!trigger { finish_test ; return }145reset_db146 147 148#-------------------------------------------------------------------------149# EVIDENCE-OF: R-07280-60510 Assuming the library is compiled with150# foreign key constraints enabled, it must still be enabled by the151# application at runtime, using the PRAGMA foreign_keys command.152#153# This also tests that foreign key constraints are disabled by default.154#155# EVIDENCE-OF: R-44261-39702 Foreign key constraints are disabled by156# default (for backwards compatibility), so must be enabled separately157# for each database connection.158#159drop_all_tables160do_test e_fkey-4.1 {161 execsql {162 CREATE TABLE p(i PRIMARY KEY);163 CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);164 INSERT INTO p VALUES('hello');165 INSERT INTO c VALUES('hello');166 UPDATE p SET i = 'world';167 SELECT * FROM c;168 } 169} {hello}170do_test e_fkey-4.2 {171 execsql {172 DELETE FROM c;173 DELETE FROM p;174 PRAGMA foreign_keys = ON;175 INSERT INTO p VALUES('hello');176 INSERT INTO c VALUES('hello');177 UPDATE p SET i = 'world';178 SELECT * FROM c;179 } 180} {world}181 182#-------------------------------------------------------------------------183# EVIDENCE-OF: R-08013-37737 The application can also use a PRAGMA184# foreign_keys statement to determine if foreign keys are currently185# enabled.186 187#188# This also tests the example code in section 2 of foreignkeys.in.189#190# EVIDENCE-OF: R-11255-19907191# 192reset_db193do_test e_fkey-5.1 {194 execsql { PRAGMA foreign_keys }195} {0}196do_test e_fkey-5.2 {197 execsql { 198 PRAGMA foreign_keys = ON;199 PRAGMA foreign_keys;200 }201} {1}202do_test e_fkey-5.3 {203 execsql { 204 PRAGMA foreign_keys = OFF;205 PRAGMA foreign_keys;206 }207} {0}208 209#-------------------------------------------------------------------------210# Test that it is not possible to enable or disable foreign key support211# while not in auto-commit mode.212#213# EVIDENCE-OF: R-46649-58537 It is not possible to enable or disable214# foreign key constraints in the middle of a multi-statement transaction215# (when SQLite is not in autocommit mode). Attempting to do so does not216# return an error; it simply has no effect.217#218reset_db219do_test e_fkey-6.1 {220 execsql {221 PRAGMA foreign_keys = ON;222 CREATE TABLE t1(a UNIQUE, b);223 CREATE TABLE t2(c, d REFERENCES t1(a));224 INSERT INTO t1 VALUES(1, 2);225 INSERT INTO t2 VALUES(2, 1);226 BEGIN;227 PRAGMA foreign_keys = OFF;228 }229 catchsql {230 DELETE FROM t1231 }232} {1 {FOREIGN KEY constraint failed}}233do_test e_fkey-6.2 {234 execsql { PRAGMA foreign_keys }235} {1}236do_test e_fkey-6.3 {237 execsql {238 COMMIT;239 PRAGMA foreign_keys = OFF;240 BEGIN;241 PRAGMA foreign_keys = ON;242 DELETE FROM t1;243 PRAGMA foreign_keys;244 }245} {0}246do_test e_fkey-6.4 {247 execsql COMMIT248} {}249 250###########################################################################251### SECTION 1: Introduction to Foreign Key Constraints252###########################################################################253execsql "PRAGMA foreign_keys = ON"254 255#-------------------------------------------------------------------------256# Verify that the syntax in the first example in section 1 is valid.257#258# EVIDENCE-OF: R-04042-24825 To do so, a foreign key definition may be259# added by modifying the declaration of the track table to the260# following: CREATE TABLE track( trackid INTEGER, trackname TEXT,261# trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES262# artist(artistid) );263#264do_test e_fkey-7.1 {265 execsql {266 CREATE TABLE artist(267 artistid INTEGER PRIMARY KEY, 268 artistname TEXT269 );270 CREATE TABLE track(271 trackid INTEGER, 272 trackname TEXT, 273 trackartist INTEGER,274 FOREIGN KEY(trackartist) REFERENCES artist(artistid)275 );276 }277} {}278 279#-------------------------------------------------------------------------280# EVIDENCE-OF: R-61362-32087 Attempting to insert a row into the track281# table that does not correspond to any row in the artist table will282# fail,283#284do_test e_fkey-8.1 {285 catchsql { INSERT INTO track VALUES(1, 'track 1', 1) }286} {1 {FOREIGN KEY constraint failed}}287do_test e_fkey-8.2 {288 execsql { INSERT INTO artist VALUES(2, 'artist 1') }289 catchsql { INSERT INTO track VALUES(1, 'track 1', 1) }290} {1 {FOREIGN KEY constraint failed}}291do_test e_fkey-8.2 {292 execsql { INSERT INTO track VALUES(1, 'track 1', 2) }293} {}294 295#-------------------------------------------------------------------------296# Attempting to delete a row from the 'artist' table while there are 297# dependent rows in the track table also fails.298#299# EVIDENCE-OF: R-24401-52400 as will attempting to delete a row from the300# artist table when there exist dependent rows in the track table301#302do_test e_fkey-9.1 {303 catchsql { DELETE FROM artist WHERE artistid = 2 }304} {1 {FOREIGN KEY constraint failed}}305do_test e_fkey-9.2 {306 execsql { 307 DELETE FROM track WHERE trackartist = 2;308 DELETE FROM artist WHERE artistid = 2;309 }310} {}311 312#-------------------------------------------------------------------------313# If the foreign key column (trackartist) in table 'track' is set to NULL,314# there is no requirement for a matching row in the 'artist' table.315#316# EVIDENCE-OF: R-23980-48859 There is one exception: if the foreign key317# column in the track table is NULL, then no corresponding entry in the318# artist table is required.319#320do_test e_fkey-10.1 {321 execsql {322 INSERT INTO track VALUES(1, 'track 1', NULL);323 INSERT INTO track VALUES(2, 'track 2', NULL);324 }325} {}326do_test e_fkey-10.2 {327 execsql { SELECT * FROM artist }328} {}329do_test e_fkey-10.3 {330 # Setting the trackid to a non-NULL value fails, of course.331 catchsql { UPDATE track SET trackartist = 5 WHERE trackid = 1 }332} {1 {FOREIGN KEY constraint failed}}333do_test e_fkey-10.4 {334 execsql {335 INSERT INTO artist VALUES(5, 'artist 5');336 UPDATE track SET trackartist = 5 WHERE trackid = 1;337 }338 catchsql { DELETE FROM artist WHERE artistid = 5}339} {1 {FOREIGN KEY constraint failed}}340do_test e_fkey-10.5 {341 execsql { 342 UPDATE track SET trackartist = NULL WHERE trackid = 1;343 DELETE FROM artist WHERE artistid = 5;344 }345} {}346 347#-------------------------------------------------------------------------348# Test that the following is true fo all rows in the track table:349#350# trackartist IS NULL OR 351# EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)352#353# EVIDENCE-OF: R-52486-21352 Expressed in SQL, this means that for every354# row in the track table, the following expression evaluates to true:355# trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE356# artistid=trackartist)357 358# This procedure executes a test case to check that statement 359# R-52486-21352 is true after executing the SQL statement passed.360# as the second argument.361proc test_r52486_21352 {tn sql} {362 set res [catchsql $sql]363 set results {364 {0 {}} 365 {1 {UNIQUE constraint failed: artist.artistid}} 366 {1 {FOREIGN KEY constraint failed}}367 }368 if {[lsearch $results $res]<0} {369 error $res370 }371 372 do_test e_fkey-11.$tn {373 execsql {374 SELECT count(*) FROM track WHERE NOT (375 trackartist IS NULL OR 376 EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)377 )378 }379 } {0}380}381 382# Execute a series of random INSERT, UPDATE and DELETE operations383# (some of which may fail due to FK or PK constraint violations) on 384# the two tables in the example schema. Test that R-52486-21352385# is true after executing each operation.386#387set Template {388 {INSERT INTO track VALUES($t, 'track $t', $a)}389 {DELETE FROM track WHERE trackid = $t}390 {UPDATE track SET trackartist = $a WHERE trackid = $t}391 {INSERT INTO artist VALUES($a, 'artist $a')}392 {DELETE FROM artist WHERE artistid = $a}393 {UPDATE artist SET artistid = $a2 WHERE artistid = $a}394}395for {set i 0} {$i < 500} {incr i} {396 set a [expr int(rand()*10)]397 set a2 [expr int(rand()*10)]398 set t [expr int(rand()*50)]399 set sql [subst [lindex $Template [expr int(rand()*6)]]]400 401 test_r52486_21352 $i $sql402}403 404#-------------------------------------------------------------------------405# Check that a NOT NULL constraint can be added to the example schema406# to prohibit NULL child keys from being inserted.407#408# EVIDENCE-OF: R-42412-59321 Tip: If the application requires a stricter409# relationship between artist and track, where NULL values are not410# permitted in the trackartist column, simply add the appropriate "NOT411# NULL" constraint to the schema.412#413drop_all_tables414do_test e_fkey-12.1 {415 execsql {416 CREATE TABLE artist(417 artistid INTEGER PRIMARY KEY, 418 artistname TEXT419 );420 CREATE TABLE track(421 trackid INTEGER, 422 trackname TEXT, 423 trackartist INTEGER NOT NULL,424 FOREIGN KEY(trackartist) REFERENCES artist(artistid)425 );426 }427} {}428do_test e_fkey-12.2 {429 catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) }430} {1 {NOT NULL constraint failed: track.trackartist}}431 432#-------------------------------------------------------------------------433# EVIDENCE-OF: R-16127-35442434#435# Test an example from foreignkeys.html.436#437drop_all_tables438do_test e_fkey-13.1 {439 execsql {440 CREATE TABLE artist(441 artistid INTEGER PRIMARY KEY, 442 artistname TEXT443 );444 CREATE TABLE track(445 trackid INTEGER, 446 trackname TEXT, 447 trackartist INTEGER,448 FOREIGN KEY(trackartist) REFERENCES artist(artistid)449 );450 INSERT INTO artist VALUES(1, 'Dean Martin');451 INSERT INTO artist VALUES(2, 'Frank Sinatra');452 INSERT INTO track VALUES(11, 'That''s Amore', 1);453 INSERT INTO track VALUES(12, 'Christmas Blues', 1);454 INSERT INTO track VALUES(13, 'My Way', 2);455 }456} {}457do_test e_fkey-13.2 {458 catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', 3) }459} {1 {FOREIGN KEY constraint failed}}460do_test e_fkey-13.3 {461 execsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) }462} {}463do_test e_fkey-13.4 {464 catchsql { 465 UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';466 }467} {1 {FOREIGN KEY constraint failed}}468do_test e_fkey-13.5 {469 execsql {470 INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');471 UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';472 INSERT INTO track VALUES(15, 'Boogie Woogie', 3);473 }474} {}475 476#-------------------------------------------------------------------------477# EVIDENCE-OF: R-15958-50233478#479# Test the second example from the first section of foreignkeys.html.480#481do_test e_fkey-14.1 {482 catchsql {483 DELETE FROM artist WHERE artistname = 'Frank Sinatra';484 }485} {1 {FOREIGN KEY constraint failed}}486do_test e_fkey-14.2 {487 execsql {488 DELETE FROM track WHERE trackname = 'My Way';489 DELETE FROM artist WHERE artistname = 'Frank Sinatra';490 }491} {}492do_test e_fkey-14.3 {493 catchsql {494 UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';495 }496} {1 {FOREIGN KEY constraint failed}}497do_test e_fkey-14.4 {498 execsql {499 DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');500 UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';501 }502} {}503 504 505#-------------------------------------------------------------------------506# EVIDENCE-OF: R-56032-24923 The foreign key constraint is satisfied if507# for each row in the child table either one or more of the child key508# columns are NULL, or there exists a row in the parent table for which509# each parent key column contains a value equal to the value in its510# associated child key column.511#512# Test also that the usual comparison rules are used when testing if there 513# is a matching row in the parent table of a foreign key constraint.514#515# EVIDENCE-OF: R-57765-12380 In the above paragraph, the term "equal"516# means equal when values are compared using the rules specified here.517#518drop_all_tables519do_test e_fkey-15.1 {520 execsql {521 CREATE TABLE par(p PRIMARY KEY);522 CREATE TABLE chi(c REFERENCES par);523 524 INSERT INTO par VALUES(1);525 INSERT INTO par VALUES('1');526 INSERT INTO par VALUES(X'31');527 SELECT typeof(p) FROM par;528 }529} {integer text blob}530 531proc test_efkey_45 {tn isError sql} {532 do_test e_fkey-15.$tn.1 "533 catchsql {$sql}534 " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]535 536 do_test e_fkey-15.$tn.2 {537 execsql {538 SELECT * FROM chi WHERE c IS NOT NULL AND c NOT IN (SELECT p FROM par)539 }540 } {}541}542 543test_efkey_45 1 0 "INSERT INTO chi VALUES(1)"544test_efkey_45 2 1 "INSERT INTO chi VALUES('1.0')"545test_efkey_45 3 0 "INSERT INTO chi VALUES('1')"546test_efkey_45 4 1 "DELETE FROM par WHERE p = '1'"547test_efkey_45 5 0 "DELETE FROM chi WHERE c = '1'"548test_efkey_45 6 0 "DELETE FROM par WHERE p = '1'"549test_efkey_45 7 1 "INSERT INTO chi VALUES('1')"550test_efkey_45 8 0 "INSERT INTO chi VALUES(X'31')"551test_efkey_45 9 1 "INSERT INTO chi VALUES(X'32')"552 553#-------------------------------------------------------------------------554# Specifically, test that when comparing child and parent key values the555# default collation sequence of the parent key column is used.556#557# EVIDENCE-OF: R-15796-47513 When comparing text values, the collating558# sequence associated with the parent key column is always used.559#560drop_all_tables561do_test e_fkey-16.1 {562 execsql {563 CREATE TABLE t1(a COLLATE nocase PRIMARY KEY);564 CREATE TABLE t2(b REFERENCES t1);565 }566} {}567do_test e_fkey-16.2 {568 execsql {569 INSERT INTO t1 VALUES('oNe');570 INSERT INTO t2 VALUES('one');571 INSERT INTO t2 VALUES('ONE');572 UPDATE t2 SET b = 'OnE';573 UPDATE t1 SET a = 'ONE';574 }575} {}576do_test e_fkey-16.3 {577 catchsql { UPDATE t2 SET b = 'two' WHERE rowid = 1 }578} {1 {FOREIGN KEY constraint failed}}579do_test e_fkey-16.4 {580 catchsql { DELETE FROM t1 WHERE rowid = 1 }581} {1 {FOREIGN KEY constraint failed}}582 583#-------------------------------------------------------------------------584# Specifically, test that when comparing child and parent key values the585# affinity of the parent key column is applied to the child key value586# before the comparison takes place.587#588# EVIDENCE-OF: R-04240-13860 When comparing values, if the parent key589# column has an affinity, then that affinity is applied to the child key590# value before the comparison is performed.591#592drop_all_tables593do_test e_fkey-17.1 {594 execsql {595 CREATE TABLE t1(a NUMERIC PRIMARY KEY);596 CREATE TABLE t2(b TEXT REFERENCES t1);597 }598} {}599do_test e_fkey-17.2 {600 execsql {601 INSERT INTO t1 VALUES(1);602 INSERT INTO t1 VALUES(2);603 INSERT INTO t1 VALUES('three');604 INSERT INTO t2 VALUES('2.0');605 SELECT b, typeof(b) FROM t2;606 }607} {2.0 text}608do_test e_fkey-17.3 {609 execsql { SELECT typeof(a) FROM t1 }610} {integer integer text}611do_test e_fkey-17.4 {612 catchsql { DELETE FROM t1 WHERE rowid = 2 }613} {1 {FOREIGN KEY constraint failed}}614 615###########################################################################616### SECTION 3: Required and Suggested Database Indexes617###########################################################################618 619#-------------------------------------------------------------------------620# A parent key must be either a PRIMARY KEY, subject to a UNIQUE 621# constraint, or have a UNIQUE index created on it.622#623# EVIDENCE-OF: R-13435-26311 Usually, the parent key of a foreign key624# constraint is the primary key of the parent table. If they are not the625# primary key, then the parent key columns must be collectively subject626# to a UNIQUE constraint or have a UNIQUE index.627# 628# Also test that if a parent key is not subject to a PRIMARY KEY or UNIQUE629# constraint, but does have a UNIQUE index created on it, then the UNIQUE index630# must use the default collation sequences associated with the parent key631# columns.632#633# EVIDENCE-OF: R-00376-39212 If the parent key columns have a UNIQUE634# index, then that index must use the collation sequences that are635# specified in the CREATE TABLE statement for the parent table.636#637drop_all_tables638do_test e_fkey-18.1 {639 execsql {640 CREATE TABLE t2(a REFERENCES t1(x));641 }642} {}643proc test_efkey_57 {tn isError sql} {644 catchsql { DROP TABLE t1 }645 execsql $sql646 do_test e_fkey-18.$tn {647 catchsql { INSERT INTO t2 VALUES(NULL) }648 } [lindex {{0 {}} {/1 {foreign key mismatch - ".*" referencing ".*"}/}} \649 $isError]650}651test_efkey_57 2 0 { CREATE TABLE t1(x PRIMARY KEY) }652test_efkey_57 3 0 { CREATE TABLE t1(x UNIQUE) }653test_efkey_57 4 0 { CREATE TABLE t1(x); CREATE UNIQUE INDEX t1i ON t1(x) }654test_efkey_57 5 1 { 655 CREATE TABLE t1(x); 656 CREATE UNIQUE INDEX t1i ON t1(x COLLATE nocase);657}658test_efkey_57 6 1 { CREATE TABLE t1(x) }659test_efkey_57 7 1 { CREATE TABLE t1(x, y, PRIMARY KEY(x, y)) }660test_efkey_57 8 1 { CREATE TABLE t1(x, y, UNIQUE(x, y)) }661test_efkey_57 9 1 { 662 CREATE TABLE t1(x, y); 663 CREATE UNIQUE INDEX t1i ON t1(x, y);664}665 666 667#-------------------------------------------------------------------------668# This block tests an example in foreignkeys.html. Several testable669# statements refer to this example, as follows670#671# EVIDENCE-OF: R-27484-01467672#673# FK Constraints on child1, child2 and child3 are Ok.674#675# Problem with FK on child4:676#677# EVIDENCE-OF: R-51039-44840 The foreign key declared as part of table678# child4 is an error because even though the parent key column is679# indexed, the index is not UNIQUE.680#681# Problem with FK on child5:682#683# EVIDENCE-OF: R-01060-48788 The foreign key for table child5 is an684# error because even though the parent key column has a unique index,685# the index uses a different collating sequence.686#687# Problem with FK on child6 and child7:688#689# EVIDENCE-OF: R-63088-37469 Tables child6 and child7 are incorrect690# because while both have UNIQUE indices on their parent keys, the keys691# are not an exact match to the columns of a single UNIQUE index.692#693drop_all_tables694do_test e_fkey-19.1 {695 execsql {696 CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);697 CREATE UNIQUE INDEX i1 ON parent(c, d);698 CREATE INDEX i2 ON parent(e);699 CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);700 701 CREATE TABLE child1(f, g REFERENCES parent(a)); -- Ok702 CREATE TABLE child2(h, i REFERENCES parent(b)); -- Ok703 CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); -- Ok704 CREATE TABLE child4(l, m REFERENCES parent(e)); -- Err705 CREATE TABLE child5(n, o REFERENCES parent(f)); -- Err706 CREATE TABLE child6(p, q, FOREIGN KEY(p,q) REFERENCES parent(b, c)); -- Err707 CREATE TABLE child7(r REFERENCES parent(c)); -- Err708 }709} {}710do_test e_fkey-19.2 {711 execsql {712 INSERT INTO parent VALUES(1, 2, 3, 4, 5, 6);713 INSERT INTO child1 VALUES('xxx', 1);714 INSERT INTO child2 VALUES('xxx', 2);715 INSERT INTO child3 VALUES(3, 4);716 }717} {}718do_test e_fkey-19.2 {719 catchsql { INSERT INTO child4 VALUES('xxx', 5) }720} {1 {foreign key mismatch - "child4" referencing "parent"}}721do_test e_fkey-19.3 {722 catchsql { INSERT INTO child5 VALUES('xxx', 6) }723} {1 {foreign key mismatch - "child5" referencing "parent"}}724do_test e_fkey-19.4 {725 catchsql { INSERT INTO child6 VALUES(2, 3) }726} {1 {foreign key mismatch - "child6" referencing "parent"}}727do_test e_fkey-19.5 {728 catchsql { INSERT INTO child7 VALUES(3) }729} {1 {foreign key mismatch - "child7" referencing "parent"}}730 731#-------------------------------------------------------------------------732# Test errors in the database schema that are detected while preparing733# DML statements. The error text for these messages always matches 734# either "foreign key mismatch" or "no such table*" (using [string match]).735#736# EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key737# errors that require looking at more than one table definition to738# identify, then those errors are not detected when the tables are739# created.740#741# EVIDENCE-OF: R-48391-38472 Instead, such errors prevent the742# application from preparing SQL statements that modify the content of743# the child or parent tables in ways that use the foreign keys.744#745# EVIDENCE-OF: R-03108-63659 The English language error message for746# foreign key DML errors is usually "foreign key mismatch" but can also747# be "no such table" if the parent table does not exist.748#749# EVIDENCE-OF: R-35763-48267 Foreign key DML errors are reported if: The750# parent table does not exist, or The parent key columns named in the751# foreign key constraint do not exist, or The parent key columns named752# in the foreign key constraint are not the primary key of the parent753# table and are not subject to a unique constraint using collating754# sequence specified in the CREATE TABLE, or The child table references755# the primary key of the parent without specifying the primary key756# columns and the number of primary key columns in the parent do not757# match the number of child key columns.758#759do_test e_fkey-20.1 {760 execsql {761 CREATE TABLE c1(c REFERENCES nosuchtable, d);762 763 CREATE TABLE p2(a, b, UNIQUE(a, b));764 CREATE TABLE c2(c, d, FOREIGN KEY(c, d) REFERENCES p2(a, x));765 766 CREATE TABLE p3(a PRIMARY KEY, b);767 CREATE TABLE c3(c REFERENCES p3(b), d);768 769 CREATE TABLE p4(a PRIMARY KEY, b);770 CREATE UNIQUE INDEX p4i ON p4(b COLLATE nocase);771 CREATE TABLE c4(c REFERENCES p4(b), d);772 773 CREATE TABLE p5(a PRIMARY KEY, b COLLATE nocase);774 CREATE UNIQUE INDEX p5i ON p5(b COLLATE binary);775 CREATE TABLE c5(c REFERENCES p5(b), d);776 777 CREATE TABLE p6(a PRIMARY KEY, b);778 CREATE TABLE c6(c, d, FOREIGN KEY(c, d) REFERENCES p6);779 780 CREATE TABLE p7(a, b, PRIMARY KEY(a, b));781 CREATE TABLE c7(c, d REFERENCES p7);782 }783} {}784 785foreach {tn tbl ptbl err} {786 2 c1 {} "no such table: main.nosuchtable"787 3 c2 p2 "foreign key mismatch - \"c2\" referencing \"p2\""788 4 c3 p3 "foreign key mismatch - \"c3\" referencing \"p3\""789 5 c4 p4 "foreign key mismatch - \"c4\" referencing \"p4\""790 6 c5 p5 "foreign key mismatch - \"c5\" referencing \"p5\""791 7 c6 p6 "foreign key mismatch - \"c6\" referencing \"p6\""792 8 c7 p7 "foreign key mismatch - \"c7\" referencing \"p7\""793} {794 do_test e_fkey-20.$tn.1 {795 catchsql "INSERT INTO $tbl VALUES('a', 'b')"796 } [list 1 $err]797 do_test e_fkey-20.$tn.2 {798 catchsql "UPDATE $tbl SET c = ?, d = ?"799 } [list 1 $err]800 do_test e_fkey-20.$tn.3 {801 catchsql "INSERT INTO $tbl SELECT ?, ?"802 } [list 1 $err]803 804 if {$ptbl ne ""} {805 do_test e_fkey-20.$tn.4 {806 catchsql "DELETE FROM $ptbl"807 } [list 1 $err]808 do_test e_fkey-20.$tn.5 {809 catchsql "UPDATE $ptbl SET a = ?, b = ?"810 } [list 1 $err]811 do_test e_fkey-20.$tn.6 {812 catchsql "INSERT INTO $ptbl SELECT ?, ?"813 } [list 1 $err]814 }815}816 817#-------------------------------------------------------------------------818# EVIDENCE-OF: R-19353-43643819#820# Test the example of foreign key mismatch errors caused by implicitly821# mapping a child key to the primary key of the parent table when the822# child key consists of a different number of columns to that primary key.823# 824drop_all_tables825do_test e_fkey-21.1 {826 execsql {827 CREATE TABLE parent2(a, b, PRIMARY KEY(a,b));828 829 CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2); -- Ok830 CREATE TABLE child9(x REFERENCES parent2); -- Err831 CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2); -- Err832 }833} {}834do_test e_fkey-21.2 {835 execsql {836 INSERT INTO parent2 VALUES('I', 'II');837 INSERT INTO child8 VALUES('I', 'II');838 }839} {}840do_test e_fkey-21.3 {841 catchsql { INSERT INTO child9 VALUES('I') }842} {1 {foreign key mismatch - "child9" referencing "parent2"}}843do_test e_fkey-21.4 {844 catchsql { INSERT INTO child9 VALUES('II') }845} {1 {foreign key mismatch - "child9" referencing "parent2"}}846do_test e_fkey-21.5 {847 catchsql { INSERT INTO child9 VALUES(NULL) }848} {1 {foreign key mismatch - "child9" referencing "parent2"}}849do_test e_fkey-21.6 {850 catchsql { INSERT INTO child10 VALUES('I', 'II', 'III') }851} {1 {foreign key mismatch - "child10" referencing "parent2"}}852do_test e_fkey-21.7 {853 catchsql { INSERT INTO child10 VALUES(1, 2, 3) }854} {1 {foreign key mismatch - "child10" referencing "parent2"}}855do_test e_fkey-21.8 {856 catchsql { INSERT INTO child10 VALUES(NULL, NULL, NULL) }857} {1 {foreign key mismatch - "child10" referencing "parent2"}}858 859#-------------------------------------------------------------------------860# Test errors that are reported when creating the child table. 861# Specifically:862#863# * different number of child and parent key columns, and864# * child columns that do not exist.865#866# EVIDENCE-OF: R-23682-59820 By contrast, if foreign key errors can be867# recognized simply by looking at the definition of the child table and868# without having to consult the parent table definition, then the CREATE869# TABLE statement for the child table fails.870#871# These errors are reported whether or not FK support is enabled.872#873# EVIDENCE-OF: R-33883-28833 Foreign key DDL errors are reported874# regardless of whether or not foreign key constraints are enabled when875# the table is created.876#877drop_all_tables878foreach fk [list OFF ON] {879 execsql "PRAGMA foreign_keys = $fk"880 set i 0881 foreach {sql error} {882 "CREATE TABLE child1(a, b, FOREIGN KEY(a, b) REFERENCES p(c))"883 {number of columns in foreign key does not match the number of columns in the referenced table}884 "CREATE TABLE child2(a, b, FOREIGN KEY(a, b) REFERENCES p(c, d, e))"885 {number of columns in foreign key does not match the number of columns in the referenced table}886 "CREATE TABLE child2(a, b, FOREIGN KEY(a, c) REFERENCES p(c, d))"887 {unknown column "c" in foreign key definition}888 "CREATE TABLE child2(a, b, FOREIGN KEY(c, b) REFERENCES p(c, d))"889 {unknown column "c" in foreign key definition}890 } {891 do_test e_fkey-22.$fk.[incr i] {892 catchsql $sql893 } [list 1 $error]894 }895}896 897#-------------------------------------------------------------------------898# Test that a REFERENCING clause that does not specify parent key columns899# implicitly maps to the primary key of the parent table.900#901# EVIDENCE-OF: R-43879-08025 Attaching a "REFERENCES <parent-table>"902# clause to a column definition creates a foreign903# key constraint that maps the column to the primary key of904# <parent-table>.905# 906do_test e_fkey-23.1 {907 execsql {908 CREATE TABLE p1(a, b, PRIMARY KEY(a, b));909 CREATE TABLE p2(a, b PRIMARY KEY);910 CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p1);911 CREATE TABLE c2(a, b REFERENCES p2);912 }913} {}914proc test_efkey_60 {tn isError sql} {915 do_test e_fkey-23.$tn "916 catchsql {$sql}917 " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]918}919 920test_efkey_60 2 1 "INSERT INTO c1 VALUES(239, 231)"921test_efkey_60 3 0 "INSERT INTO p1 VALUES(239, 231)"922test_efkey_60 4 0 "INSERT INTO c1 VALUES(239, 231)"923test_efkey_60 5 1 "INSERT INTO c2 VALUES(239, 231)"924test_efkey_60 6 0 "INSERT INTO p2 VALUES(239, 231)"925test_efkey_60 7 0 "INSERT INTO c2 VALUES(239, 231)"926 927#-------------------------------------------------------------------------928# Test that an index on on the child key columns of an FK constraint929# is optional.930#931# EVIDENCE-OF: R-15417-28014 Indices are not required for child key932# columns933#934# Also test that if an index is created on the child key columns, it does935# not make a difference whether or not it is a UNIQUE index.936#937# EVIDENCE-OF: R-15741-50893 The child key index does not have to be938# (and usually will not be) a UNIQUE index.939#940drop_all_tables941do_test e_fkey-24.1 {942 execsql {943 CREATE TABLE parent(x, y, UNIQUE(y, x));944 CREATE TABLE c1(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));945 CREATE TABLE c2(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));946 CREATE TABLE c3(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));947 CREATE INDEX c2i ON c2(a, b);948 CREATE UNIQUE INDEX c3i ON c2(b, a);949 }950} {}951proc test_efkey_61 {tn isError sql} {952 do_test e_fkey-24.$tn "953 catchsql {$sql}954 " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]955}956foreach {tn c} [list 2 c1 3 c2 4 c3] {957 test_efkey_61 $tn.1 1 "INSERT INTO $c VALUES(1, 2)"958 test_efkey_61 $tn.2 0 "INSERT INTO parent VALUES(1, 2)"959 test_efkey_61 $tn.3 0 "INSERT INTO $c VALUES(1, 2)"960 961 execsql "DELETE FROM $c ; DELETE FROM parent"962}963 964#-------------------------------------------------------------------------965# EVIDENCE-OF: R-00279-52283966#967# Test an example showing that when a row is deleted from the parent 968# table, the child table is queried for orphaned rows as follows:969#970# SELECT rowid FROM track WHERE trackartist = ?971#972# EVIDENCE-OF: R-23302-30956 If this SELECT returns any rows at all,973# then SQLite concludes that deleting the row from the parent table974# would violate the foreign key constraint and returns an error.975#976do_test e_fkey-25.1 {977 execsql {978 CREATE TABLE artist(979 artistid INTEGER PRIMARY KEY, 980 artistname TEXT981 );982 CREATE TABLE track(983 trackid INTEGER, 984 trackname TEXT, 985 trackartist INTEGER,986 FOREIGN KEY(trackartist) REFERENCES artist(artistid)987 );988 }989} {}990do_detail_test e_fkey-25.2 {991 PRAGMA foreign_keys = OFF;992 EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;993 EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?;994} {995 {SCAN artist} 996 {SCAN track}997}998do_detail_test e_fkey-25.3 {999 PRAGMA foreign_keys = ON;1000 EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;1001} {1002 {SCAN artist} 1003 {SCAN track}1004}1005do_test e_fkey-25.4 {1006 execsql {1007 INSERT INTO artist VALUES(5, 'artist 5');1008 INSERT INTO artist VALUES(6, 'artist 6');1009 INSERT INTO artist VALUES(7, 'artist 7');1010 INSERT INTO track VALUES(1, 'track 1', 5);1011 INSERT INTO track VALUES(2, 'track 2', 6);1012 }1013} {}1014 1015do_test e_fkey-25.5 {1016 concat \1017 [execsql { SELECT rowid FROM track WHERE trackartist = 5 }] \1018 [catchsql { DELETE FROM artist WHERE artistid = 5 }]1019} {1 1 {FOREIGN KEY constraint failed}}1020 1021do_test e_fkey-25.6 {1022 concat \1023 [execsql { SELECT rowid FROM track WHERE trackartist = 7 }] \1024 [catchsql { DELETE FROM artist WHERE artistid = 7 }]1025} {0 {}}1026 1027do_test e_fkey-25.7 {1028 concat \1029 [execsql { SELECT rowid FROM track WHERE trackartist = 6 }] \1030 [catchsql { DELETE FROM artist WHERE artistid = 6 }]1031} {2 1 {FOREIGN KEY constraint failed}}1032 1033#-------------------------------------------------------------------------1034# EVIDENCE-OF: R-47936-10044 Or, more generally:1035# SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value1036#1037# Test that when a row is deleted from the parent table of an FK 1038# constraint, the child table is queried for orphaned rows. The1039# query is equivalent to:1040#1041# SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value1042#1043# Also test that when a row is inserted into the parent table, or when the 1044# parent key values of an existing row are modified, a query equivalent1045# to the following is planned. In some cases it is not executed, but it1046# is always planned.1047#1048# SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value1049#1050# EVIDENCE-OF: R-61616-46700 Similar queries may be run if the content1051# of the parent key is modified or a new row is inserted into the parent1052# table.1053#1054#1055drop_all_tables1056do_test e_fkey-26.1 {1057 execsql { CREATE TABLE parent(x, y, UNIQUE(y, x)) }1058} {}1059foreach {tn sql} {1060 2 { 1061 CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y))1062 }1063 3 { 1064 CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));1065 CREATE INDEX childi ON child(a, b);1066 }1067 4 { 1068 CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));1069 CREATE UNIQUE INDEX childi ON child(b, a);1070 }1071} {1072 execsql $sql1073 1074 execsql {PRAGMA foreign_keys = OFF}1075 set delete [concat \1076 [eqp "DELETE FROM parent WHERE 1"] \1077 [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"]1078 ]1079 set update [concat \1080 [eqp "UPDATE parent SET x=?, y=?"] \1081 [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"] \1082 [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"]1083 ]1084 execsql {PRAGMA foreign_keys = ON}1085 1086 do_test e_fkey-26.$tn.1 { eqp "DELETE FROM parent WHERE 1" } $delete1087 do_test e_fkey-26.$tn.2 { eqp "UPDATE parent set x=?, y=?" } $update1088 1089 execsql {DROP TABLE child}1090}1091 1092#-------------------------------------------------------------------------1093# EVIDENCE-OF: R-14553-340131094#1095# Test the example schema at the end of section 3. Also test that is1096# is "efficient". In this case "efficient" means that foreign key1097# related operations on the parent table do not provoke linear scans.1098#1099drop_all_tables1100do_test e_fkey-27.1 {1101 execsql {1102 CREATE TABLE artist(1103 artistid INTEGER PRIMARY KEY, 1104 artistname TEXT1105 );1106 CREATE TABLE track(1107 trackid INTEGER,1108 trackname TEXT, 1109 trackartist INTEGER REFERENCES artist1110 );1111 CREATE INDEX trackindex ON track(trackartist);1112 }1113} {}1114do_test e_fkey-27.2 {1115 eqp { INSERT INTO artist VALUES(?, ?) }1116} {}1117do_detail_test e_fkey-27.3 {1118 EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ?1119} {1120 {SCAN artist} 1121 {SEARCH track USING COVERING INDEX trackindex (trackartist=?)} 1122 {SEARCH track USING COVERING INDEX trackindex (trackartist=?)}1123}1124do_detail_test e_fkey-27.4 {1125 EXPLAIN QUERY PLAN DELETE FROM artist1126} {1127 {SCAN artist} 1128 {SEARCH track USING COVERING INDEX trackindex (trackartist=?)}1129}1130 1131###########################################################################1132### SECTION 4.1: Composite Foreign Key Constraints1133###########################################################################1134 1135#-------------------------------------------------------------------------1136# Check that parent and child keys must have the same number of columns.1137#1138# EVIDENCE-OF: R-41062-34431 Parent and child keys must have the same1139# cardinality.1140#1141foreach {tn sql err} {1142 1 "CREATE TABLE c(jj REFERENCES p(x, y))" 1143 {foreign key on jj should reference only one column of table p}1144 1145 2 "CREATE TABLE c(jj REFERENCES p())" {near ")": syntax error}1146 1147 3 "CREATE TABLE c(jj, FOREIGN KEY(jj) REFERENCES p(x, y))" 1148 {number of columns in foreign key does not match the number of columns in the referenced table}1149 1150 4 "CREATE TABLE c(jj, FOREIGN KEY(jj) REFERENCES p())" 1151 {near ")": syntax error}1152 1153 5 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p())" 1154 {near ")": syntax error}1155 1156 6 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x))" 1157 {number of columns in foreign key does not match the number of columns in the referenced table}1158 1159 7 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x,y,z))" 1160 {number of columns in foreign key does not match the number of columns in the referenced table}1161} {1162 drop_all_tables1163 do_test e_fkey-28.$tn [list catchsql $sql] [list 1 $err]1164}1165do_test e_fkey-28.8 {1166 drop_all_tables1167 execsql {1168 CREATE TABLE p(x PRIMARY KEY);1169 CREATE TABLE c(a, b, FOREIGN KEY(a,b) REFERENCES p);1170 }1171 catchsql {DELETE FROM p}1172} {1 {foreign key mismatch - "c" referencing "p"}}1173do_test e_fkey-28.9 {1174 drop_all_tables1175 execsql {1176 CREATE TABLE p(x, y, PRIMARY KEY(x,y));1177 CREATE TABLE c(a REFERENCES p);1178 }1179 catchsql {DELETE FROM p}1180} {1 {foreign key mismatch - "c" referencing "p"}}1181 1182 1183#-------------------------------------------------------------------------1184# EVIDENCE-OF: R-24676-098591185#1186# Test the example schema in the "Composite Foreign Key Constraints" 1187# section.1188#1189do_test e_fkey-29.1 {1190 execsql {1191 CREATE TABLE album(1192 albumartist TEXT,1193 albumname TEXT,1194 albumcover BINARY,1195 PRIMARY KEY(albumartist, albumname)1196 );1197 CREATE TABLE song(1198 songid INTEGER,1199 songartist TEXT,1200 songalbum TEXT,