000001  # 2009 October 7
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  #
000012  # This file implements tests to verify the "testable statements" in the
000013  # foreignkeys.in document.
000014  #
000015  # The tests in this file are arranged to mirror the structure of 
000016  # foreignkey.in, with one exception: The statements in section 2, which 
000017  # deals with enabling/disabling foreign key support, is tested first,
000018  # before section 1. This is because some statements in section 2 deal
000019  # with builds that do not include complete foreign key support (because
000020  # either SQLITE_OMIT_TRIGGER or SQLITE_OMIT_FOREIGN_KEY was defined
000021  # at build time).
000022  #
000023  
000024  set testdir [file dirname $argv0]
000025  source $testdir/tester.tcl
000026  
000027  proc eqp {sql {db db}} { uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db }
000028  
000029  ###########################################################################
000030  ### SECTION 2: Enabling Foreign Key Support
000031  ###########################################################################
000032  
000033  #-------------------------------------------------------------------------
000034  # EVIDENCE-OF: R-33710-56344 In order to use foreign key constraints in
000035  # SQLite, the library must be compiled with neither
000036  # SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined.
000037  #
000038  ifcapable trigger&&foreignkey {
000039    do_test e_fkey-1 {
000040      execsql {
000041        PRAGMA foreign_keys = ON;
000042        CREATE TABLE p(i PRIMARY KEY);
000043        CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
000044        INSERT INTO p VALUES('hello');
000045        INSERT INTO c VALUES('hello');
000046        UPDATE p SET i = 'world';
000047        SELECT * FROM c;
000048      }
000049    } {world}
000050  }
000051  
000052  #-------------------------------------------------------------------------
000053  # Test the effects of defining OMIT_TRIGGER but not OMIT_FOREIGN_KEY.
000054  #
000055  # EVIDENCE-OF: R-10109-20452 If SQLITE_OMIT_TRIGGER is defined but
000056  # SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to
000057  # version 3.6.19 (2009-10-14) - foreign key definitions are parsed and
000058  # may be queried using PRAGMA foreign_key_list, but foreign key
000059  # constraints are not enforced.
000060  #
000061  # Specifically, test that "PRAGMA foreign_keys" is a no-op in this case.
000062  # When using the pragma to query the current setting, 0 rows are returned.
000063  #
000064  # EVIDENCE-OF: R-22567-44039 The PRAGMA foreign_keys command is a no-op
000065  # in this configuration.
000066  #
000067  # EVIDENCE-OF: R-41784-13339 Tip: If the command "PRAGMA foreign_keys"
000068  # returns no data instead of a single row containing "0" or "1", then
000069  # the version of SQLite you are using does not support foreign keys
000070  # (either because it is older than 3.6.19 or because it was compiled
000071  # with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).
000072  #
000073  reset_db
000074  ifcapable !trigger&&foreignkey {
000075    do_test e_fkey-2.1 {
000076      execsql {
000077        PRAGMA foreign_keys = ON;
000078        CREATE TABLE p(i PRIMARY KEY);
000079        CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
000080        INSERT INTO p VALUES('hello');
000081        INSERT INTO c VALUES('hello');
000082        UPDATE p SET i = 'world';
000083        SELECT * FROM c;
000084      }
000085    } {hello}
000086    do_test e_fkey-2.2 {
000087      execsql { PRAGMA foreign_key_list(c) }
000088    } {0 0 p j {} CASCADE {NO ACTION} NONE}
000089    do_test e_fkey-2.3 {
000090      execsql { PRAGMA foreign_keys }
000091    } {}
000092  }
000093  
000094  
000095  #-------------------------------------------------------------------------
000096  # Test the effects of defining OMIT_FOREIGN_KEY.
000097  #
000098  # EVIDENCE-OF: R-58428-36660 If OMIT_FOREIGN_KEY is defined, then
000099  # foreign key definitions cannot even be parsed (attempting to specify a
000100  # foreign key definition is a syntax error).
000101  #
000102  # Specifically, test that foreign key constraints cannot even be parsed 
000103  # in such a build.
000104  #
000105  reset_db
000106  ifcapable !foreignkey {
000107    do_test e_fkey-3.1 {
000108      execsql { CREATE TABLE p(i PRIMARY KEY) }
000109      catchsql { CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE) }
000110    } {1 {near "ON": syntax error}}
000111    do_test e_fkey-3.2 {
000112      # This is allowed, as in this build, "REFERENCES" is not a keyword.
000113      # The declared datatype of column j is "REFERENCES p".
000114      execsql { CREATE TABLE c(j REFERENCES p) }
000115    } {}
000116    do_test e_fkey-3.3 {
000117      execsql { PRAGMA table_info(c) }
000118    } {0 j {REFERENCES p} 0 {} 0}
000119    do_test e_fkey-3.4 {
000120      execsql { PRAGMA foreign_key_list(c) }
000121    } {}
000122    do_test e_fkey-3.5 {
000123      execsql { PRAGMA foreign_keys }
000124    } {}
000125  }
000126  
000127  ifcapable !foreignkey||!trigger { finish_test ; return }
000128  reset_db
000129  
000130  
000131  #-------------------------------------------------------------------------
000132  # EVIDENCE-OF: R-07280-60510 Assuming the library is compiled with
000133  # foreign key constraints enabled, it must still be enabled by the
000134  # application at runtime, using the PRAGMA foreign_keys command.
000135  #
000136  # This also tests that foreign key constraints are disabled by default.
000137  #
000138  # EVIDENCE-OF: R-44261-39702 Foreign key constraints are disabled by
000139  # default (for backwards compatibility), so must be enabled separately
000140  # for each database connection.
000141  #
000142  drop_all_tables
000143  do_test e_fkey-4.1 {
000144    execsql {
000145      CREATE TABLE p(i PRIMARY KEY);
000146      CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
000147      INSERT INTO p VALUES('hello');
000148      INSERT INTO c VALUES('hello');
000149      UPDATE p SET i = 'world';
000150      SELECT * FROM c;
000151    } 
000152  } {hello}
000153  do_test e_fkey-4.2 {
000154    execsql {
000155      DELETE FROM c;
000156      DELETE FROM p;
000157      PRAGMA foreign_keys = ON;
000158      INSERT INTO p VALUES('hello');
000159      INSERT INTO c VALUES('hello');
000160      UPDATE p SET i = 'world';
000161      SELECT * FROM c;
000162    } 
000163  } {world}
000164  
000165  #-------------------------------------------------------------------------
000166  # EVIDENCE-OF: R-08013-37737 The application can also use a PRAGMA
000167  # foreign_keys statement to determine if foreign keys are currently
000168  # enabled.
000169  
000170  #
000171  # This also tests the example code in section 2 of foreignkeys.in.
000172  #
000173  # EVIDENCE-OF: R-11255-19907
000174  # 
000175  reset_db
000176  do_test e_fkey-5.1 {
000177    execsql { PRAGMA foreign_keys }
000178  } {0}
000179  do_test e_fkey-5.2 {
000180    execsql { 
000181      PRAGMA foreign_keys = ON;
000182      PRAGMA foreign_keys;
000183    }
000184  } {1}
000185  do_test e_fkey-5.3 {
000186    execsql { 
000187      PRAGMA foreign_keys = OFF;
000188      PRAGMA foreign_keys;
000189    }
000190  } {0}
000191  
000192  #-------------------------------------------------------------------------
000193  # Test that it is not possible to enable or disable foreign key support
000194  # while not in auto-commit mode.
000195  #
000196  # EVIDENCE-OF: R-46649-58537 It is not possible to enable or disable
000197  # foreign key constraints in the middle of a multi-statement transaction
000198  # (when SQLite is not in autocommit mode). Attempting to do so does not
000199  # return an error; it simply has no effect.
000200  #
000201  reset_db
000202  do_test e_fkey-6.1 {
000203    execsql {
000204      PRAGMA foreign_keys = ON;
000205      CREATE TABLE t1(a UNIQUE, b);
000206      CREATE TABLE t2(c, d REFERENCES t1(a));
000207      INSERT INTO t1 VALUES(1, 2);
000208      INSERT INTO t2 VALUES(2, 1);
000209      BEGIN;
000210        PRAGMA foreign_keys = OFF;
000211    }
000212    catchsql {
000213        DELETE FROM t1
000214    }
000215  } {1 {FOREIGN KEY constraint failed}}
000216  do_test e_fkey-6.2 {
000217    execsql { PRAGMA foreign_keys }
000218  } {1}
000219  do_test e_fkey-6.3 {
000220    execsql {
000221      COMMIT;
000222      PRAGMA foreign_keys = OFF;
000223      BEGIN;
000224        PRAGMA foreign_keys = ON;
000225        DELETE FROM t1;
000226        PRAGMA foreign_keys;
000227    }
000228  } {0}
000229  do_test e_fkey-6.4 {
000230    execsql COMMIT
000231  } {}
000232  
000233  ###########################################################################
000234  ### SECTION 1: Introduction to Foreign Key Constraints
000235  ###########################################################################
000236  execsql "PRAGMA foreign_keys = ON"
000237  
000238  #-------------------------------------------------------------------------
000239  # Verify that the syntax in the first example in section 1 is valid.
000240  #
000241  # EVIDENCE-OF: R-04042-24825 To do so, a foreign key definition may be
000242  # added by modifying the declaration of the track table to the
000243  # following: CREATE TABLE track( trackid INTEGER, trackname TEXT,
000244  # trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES
000245  # artist(artistid) );
000246  #
000247  do_test e_fkey-7.1 {
000248    execsql {
000249      CREATE TABLE artist(
000250        artistid    INTEGER PRIMARY KEY, 
000251        artistname  TEXT
000252      );
000253      CREATE TABLE track(
000254        trackid     INTEGER, 
000255        trackname   TEXT, 
000256        trackartist INTEGER,
000257        FOREIGN KEY(trackartist) REFERENCES artist(artistid)
000258      );
000259    }
000260  } {}
000261  
000262  #-------------------------------------------------------------------------
000263  # EVIDENCE-OF: R-61362-32087 Attempting to insert a row into the track
000264  # table that does not correspond to any row in the artist table will
000265  # fail,
000266  #
000267  do_test e_fkey-8.1 {
000268    catchsql { INSERT INTO track VALUES(1, 'track 1', 1) }
000269  } {1 {FOREIGN KEY constraint failed}}
000270  do_test e_fkey-8.2 {
000271    execsql { INSERT INTO artist VALUES(2, 'artist 1') }
000272    catchsql { INSERT INTO track VALUES(1, 'track 1', 1) }
000273  } {1 {FOREIGN KEY constraint failed}}
000274  do_test e_fkey-8.2 {
000275    execsql { INSERT INTO track VALUES(1, 'track 1', 2) }
000276  } {}
000277  
000278  #-------------------------------------------------------------------------
000279  # Attempting to delete a row from the 'artist' table while there are 
000280  # dependent rows in the track table also fails.
000281  #
000282  # EVIDENCE-OF: R-24401-52400 as will attempting to delete a row from the
000283  # artist table when there exist dependent rows in the track table
000284  #
000285  do_test e_fkey-9.1 {
000286    catchsql { DELETE FROM artist WHERE artistid = 2 }
000287  } {1 {FOREIGN KEY constraint failed}}
000288  do_test e_fkey-9.2 {
000289    execsql { 
000290      DELETE FROM track WHERE trackartist = 2;
000291      DELETE FROM artist WHERE artistid = 2;
000292    }
000293  } {}
000294  
000295  #-------------------------------------------------------------------------
000296  # If the foreign key column (trackartist) in table 'track' is set to NULL,
000297  # there is no requirement for a matching row in the 'artist' table.
000298  #
000299  # EVIDENCE-OF: R-23980-48859 There is one exception: if the foreign key
000300  # column in the track table is NULL, then no corresponding entry in the
000301  # artist table is required.
000302  #
000303  do_test e_fkey-10.1 {
000304    execsql {
000305      INSERT INTO track VALUES(1, 'track 1', NULL);
000306      INSERT INTO track VALUES(2, 'track 2', NULL);
000307    }
000308  } {}
000309  do_test e_fkey-10.2 {
000310    execsql { SELECT * FROM artist }
000311  } {}
000312  do_test e_fkey-10.3 {
000313    # Setting the trackid to a non-NULL value fails, of course.
000314    catchsql { UPDATE track SET trackartist = 5 WHERE trackid = 1 }
000315  } {1 {FOREIGN KEY constraint failed}}
000316  do_test e_fkey-10.4 {
000317    execsql {
000318      INSERT INTO artist VALUES(5, 'artist 5');
000319      UPDATE track SET trackartist = 5 WHERE trackid = 1;
000320    }
000321    catchsql { DELETE FROM artist WHERE artistid = 5}
000322  } {1 {FOREIGN KEY constraint failed}}
000323  do_test e_fkey-10.5 {
000324    execsql { 
000325      UPDATE track SET trackartist = NULL WHERE trackid = 1;
000326      DELETE FROM artist WHERE artistid = 5;
000327    }
000328  } {}
000329  
000330  #-------------------------------------------------------------------------
000331  # Test that the following is true fo all rows in the track table:
000332  #
000333  #   trackartist IS NULL OR 
000334  #   EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
000335  #
000336  # EVIDENCE-OF: R-52486-21352 Expressed in SQL, this means that for every
000337  # row in the track table, the following expression evaluates to true:
000338  # trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE
000339  # artistid=trackartist)
000340  
000341  # This procedure executes a test case to check that statement 
000342  # R-52486-21352 is true after executing the SQL statement passed.
000343  # as the second argument.
000344  proc test_r52486_21352 {tn sql} {
000345    set res [catchsql $sql]
000346    set results {
000347      {0 {}} 
000348      {1 {UNIQUE constraint failed: artist.artistid}} 
000349      {1 {FOREIGN KEY constraint failed}}
000350    }
000351    if {[lsearch $results $res]<0} {
000352      error $res
000353    }
000354  
000355    do_test e_fkey-11.$tn {
000356      execsql {
000357        SELECT count(*) FROM track WHERE NOT (
000358          trackartist IS NULL OR 
000359          EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
000360        )
000361      }
000362    } {0}
000363  }
000364  
000365  # Execute a series of random INSERT, UPDATE and DELETE operations
000366  # (some of which may fail due to FK or PK constraint violations) on 
000367  # the two tables in the example schema. Test that R-52486-21352
000368  # is true after executing each operation.
000369  #
000370  set Template {
000371    {INSERT INTO track VALUES($t, 'track $t', $a)}
000372    {DELETE FROM track WHERE trackid = $t}
000373    {UPDATE track SET trackartist = $a WHERE trackid = $t}
000374    {INSERT INTO artist VALUES($a, 'artist $a')}
000375    {DELETE FROM artist WHERE artistid = $a}
000376    {UPDATE artist SET artistid = $a2 WHERE artistid = $a}
000377  }
000378  for {set i 0} {$i < 500} {incr i} {
000379    set a   [expr int(rand()*10)]
000380    set a2  [expr int(rand()*10)]
000381    set t   [expr int(rand()*50)]
000382    set sql [subst [lindex $Template [expr int(rand()*6)]]]
000383  
000384    test_r52486_21352 $i $sql
000385  }
000386  
000387  #-------------------------------------------------------------------------
000388  # Check that a NOT NULL constraint can be added to the example schema
000389  # to prohibit NULL child keys from being inserted.
000390  #
000391  # EVIDENCE-OF: R-42412-59321 Tip: If the application requires a stricter
000392  # relationship between artist and track, where NULL values are not
000393  # permitted in the trackartist column, simply add the appropriate "NOT
000394  # NULL" constraint to the schema.
000395  #
000396  drop_all_tables
000397  do_test e_fkey-12.1 {
000398    execsql {
000399      CREATE TABLE artist(
000400        artistid    INTEGER PRIMARY KEY, 
000401        artistname  TEXT
000402      );
000403      CREATE TABLE track(
000404        trackid     INTEGER, 
000405        trackname   TEXT, 
000406        trackartist INTEGER NOT NULL,
000407        FOREIGN KEY(trackartist) REFERENCES artist(artistid)
000408      );
000409    }
000410  } {}
000411  do_test e_fkey-12.2 {
000412    catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) }
000413  } {1 {NOT NULL constraint failed: track.trackartist}}
000414  
000415  #-------------------------------------------------------------------------
000416  # EVIDENCE-OF: R-16127-35442
000417  #
000418  # Test an example from foreignkeys.html.
000419  #
000420  drop_all_tables
000421  do_test e_fkey-13.1 {
000422    execsql {
000423      CREATE TABLE artist(
000424        artistid    INTEGER PRIMARY KEY, 
000425        artistname  TEXT
000426      );
000427      CREATE TABLE track(
000428        trackid     INTEGER, 
000429        trackname   TEXT, 
000430        trackartist INTEGER,
000431        FOREIGN KEY(trackartist) REFERENCES artist(artistid)
000432      );
000433      INSERT INTO artist VALUES(1, 'Dean Martin');
000434      INSERT INTO artist VALUES(2, 'Frank Sinatra');
000435      INSERT INTO track VALUES(11, 'That''s Amore', 1);
000436      INSERT INTO track VALUES(12, 'Christmas Blues', 1);
000437      INSERT INTO track VALUES(13, 'My Way', 2);
000438    }
000439  } {}
000440  do_test e_fkey-13.2 {
000441    catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', 3) }
000442  } {1 {FOREIGN KEY constraint failed}}
000443  do_test e_fkey-13.3 {
000444    execsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) }
000445  } {}
000446  do_test e_fkey-13.4 {
000447    catchsql { 
000448      UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
000449    }
000450  } {1 {FOREIGN KEY constraint failed}}
000451  do_test e_fkey-13.5 {
000452    execsql {
000453      INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
000454      UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
000455      INSERT INTO track VALUES(15, 'Boogie Woogie', 3);
000456    }
000457  } {}
000458  
000459  #-------------------------------------------------------------------------
000460  # EVIDENCE-OF: R-15958-50233
000461  #
000462  # Test the second example from the first section of foreignkeys.html.
000463  #
000464  do_test e_fkey-14.1 {
000465    catchsql {
000466      DELETE FROM artist WHERE artistname = 'Frank Sinatra';
000467    }
000468  } {1 {FOREIGN KEY constraint failed}}
000469  do_test e_fkey-14.2 {
000470    execsql {
000471      DELETE FROM track WHERE trackname = 'My Way';
000472      DELETE FROM artist WHERE artistname = 'Frank Sinatra';
000473    }
000474  } {}
000475  do_test e_fkey-14.3 {
000476    catchsql {
000477      UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
000478    }
000479  } {1 {FOREIGN KEY constraint failed}}
000480  do_test e_fkey-14.4 {
000481    execsql {
000482      DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
000483      UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
000484    }
000485  } {}
000486  
000487  
000488  #-------------------------------------------------------------------------
000489  # EVIDENCE-OF: R-56032-24923 The foreign key constraint is satisfied if
000490  # for each row in the child table either one or more of the child key
000491  # columns are NULL, or there exists a row in the parent table for which
000492  # each parent key column contains a value equal to the value in its
000493  # associated child key column.
000494  #
000495  # Test also that the usual comparison rules are used when testing if there 
000496  # is a matching row in the parent table of a foreign key constraint.
000497  #
000498  # EVIDENCE-OF: R-57765-12380 In the above paragraph, the term "equal"
000499  # means equal when values are compared using the rules specified here.
000500  #
000501  drop_all_tables
000502  do_test e_fkey-15.1 {
000503    execsql {
000504      CREATE TABLE par(p PRIMARY KEY);
000505      CREATE TABLE chi(c REFERENCES par);
000506  
000507      INSERT INTO par VALUES(1);
000508      INSERT INTO par VALUES('1');
000509      INSERT INTO par VALUES(X'31');
000510      SELECT typeof(p) FROM par;
000511    }
000512  } {integer text blob}
000513  
000514  proc test_efkey_45 {tn isError sql} {
000515    do_test e_fkey-15.$tn.1 "
000516      catchsql {$sql}
000517    " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
000518  
000519    do_test e_fkey-15.$tn.2 {
000520      execsql {
000521        SELECT * FROM chi WHERE c IS NOT NULL AND c NOT IN (SELECT p FROM par)
000522      }
000523    } {}
000524  }
000525  
000526  test_efkey_45 1 0 "INSERT INTO chi VALUES(1)"
000527  test_efkey_45 2 1 "INSERT INTO chi VALUES('1.0')"
000528  test_efkey_45 3 0 "INSERT INTO chi VALUES('1')"
000529  test_efkey_45 4 1 "DELETE FROM par WHERE p = '1'"
000530  test_efkey_45 5 0 "DELETE FROM chi WHERE c = '1'"
000531  test_efkey_45 6 0 "DELETE FROM par WHERE p = '1'"
000532  test_efkey_45 7 1 "INSERT INTO chi VALUES('1')"
000533  test_efkey_45 8 0 "INSERT INTO chi VALUES(X'31')"
000534  test_efkey_45 9 1 "INSERT INTO chi VALUES(X'32')"
000535  
000536  #-------------------------------------------------------------------------
000537  # Specifically, test that when comparing child and parent key values the
000538  # default collation sequence of the parent key column is used.
000539  #
000540  # EVIDENCE-OF: R-15796-47513 When comparing text values, the collating
000541  # sequence associated with the parent key column is always used.
000542  #
000543  drop_all_tables
000544  do_test e_fkey-16.1 {
000545    execsql {
000546      CREATE TABLE t1(a COLLATE nocase PRIMARY KEY);
000547      CREATE TABLE t2(b REFERENCES t1);
000548    }
000549  } {}
000550  do_test e_fkey-16.2 {
000551    execsql {
000552      INSERT INTO t1 VALUES('oNe');
000553      INSERT INTO t2 VALUES('one');
000554      INSERT INTO t2 VALUES('ONE');
000555      UPDATE t2 SET b = 'OnE';
000556      UPDATE t1 SET a = 'ONE';
000557    }
000558  } {}
000559  do_test e_fkey-16.3 {
000560    catchsql { UPDATE t2 SET b = 'two' WHERE rowid = 1 }
000561  } {1 {FOREIGN KEY constraint failed}}
000562  do_test e_fkey-16.4 {
000563    catchsql { DELETE FROM t1 WHERE rowid = 1 }
000564  } {1 {FOREIGN KEY constraint failed}}
000565  
000566  #-------------------------------------------------------------------------
000567  # Specifically, test that when comparing child and parent key values the
000568  # affinity of the parent key column is applied to the child key value
000569  # before the comparison takes place.
000570  #
000571  # EVIDENCE-OF: R-04240-13860 When comparing values, if the parent key
000572  # column has an affinity, then that affinity is applied to the child key
000573  # value before the comparison is performed.
000574  #
000575  drop_all_tables
000576  do_test e_fkey-17.1 {
000577    execsql {
000578      CREATE TABLE t1(a NUMERIC PRIMARY KEY);
000579      CREATE TABLE t2(b TEXT REFERENCES t1);
000580    }
000581  } {}
000582  do_test e_fkey-17.2 {
000583    execsql {
000584      INSERT INTO t1 VALUES(1);
000585      INSERT INTO t1 VALUES(2);
000586      INSERT INTO t1 VALUES('three');
000587      INSERT INTO t2 VALUES('2.0');
000588      SELECT b, typeof(b) FROM t2;
000589    }
000590  } {2.0 text}
000591  do_test e_fkey-17.3 {
000592    execsql { SELECT typeof(a) FROM t1 }
000593  } {integer integer text}
000594  do_test e_fkey-17.4 {
000595    catchsql { DELETE FROM t1 WHERE rowid = 2 }
000596  } {1 {FOREIGN KEY constraint failed}}
000597  
000598  ###########################################################################
000599  ### SECTION 3: Required and Suggested Database Indexes
000600  ###########################################################################
000601  
000602  #-------------------------------------------------------------------------
000603  # A parent key must be either a PRIMARY KEY, subject to a UNIQUE 
000604  # constraint, or have a UNIQUE index created on it.
000605  #
000606  # EVIDENCE-OF: R-13435-26311 Usually, the parent key of a foreign key
000607  # constraint is the primary key of the parent table. If they are not the
000608  # primary key, then the parent key columns must be collectively subject
000609  # to a UNIQUE constraint or have a UNIQUE index.
000610  # 
000611  # Also test that if a parent key is not subject to a PRIMARY KEY or UNIQUE
000612  # constraint, but does have a UNIQUE index created on it, then the UNIQUE index
000613  # must use the default collation sequences associated with the parent key
000614  # columns.
000615  #
000616  # EVIDENCE-OF: R-00376-39212 If the parent key columns have a UNIQUE
000617  # index, then that index must use the collation sequences that are
000618  # specified in the CREATE TABLE statement for the parent table.
000619  #
000620  drop_all_tables
000621  do_test e_fkey-18.1 {
000622    execsql {
000623      CREATE TABLE t2(a REFERENCES t1(x));
000624    }
000625  } {}
000626  proc test_efkey_57 {tn isError sql} {
000627    catchsql { DROP TABLE t1 }
000628    execsql $sql
000629    do_test e_fkey-18.$tn {
000630      catchsql { INSERT INTO t2 VALUES(NULL) }
000631    } [lindex {{0 {}} {/1 {foreign key mismatch - ".*" referencing ".*"}/}} \
000632       $isError]
000633  }
000634  test_efkey_57 2 0 { CREATE TABLE t1(x PRIMARY KEY) }
000635  test_efkey_57 3 0 { CREATE TABLE t1(x UNIQUE) }
000636  test_efkey_57 4 0 { CREATE TABLE t1(x); CREATE UNIQUE INDEX t1i ON t1(x) }
000637  test_efkey_57 5 1 { 
000638    CREATE TABLE t1(x); 
000639    CREATE UNIQUE INDEX t1i ON t1(x COLLATE nocase);
000640  }
000641  test_efkey_57 6 1 { CREATE TABLE t1(x) }
000642  test_efkey_57 7 1 { CREATE TABLE t1(x, y, PRIMARY KEY(x, y)) }
000643  test_efkey_57 8 1 { CREATE TABLE t1(x, y, UNIQUE(x, y)) }
000644  test_efkey_57 9 1 { 
000645    CREATE TABLE t1(x, y); 
000646    CREATE UNIQUE INDEX t1i ON t1(x, y);
000647  }
000648  
000649  
000650  #-------------------------------------------------------------------------
000651  # This block tests an example in foreignkeys.html. Several testable
000652  # statements refer to this example, as follows
000653  #
000654  # EVIDENCE-OF: R-27484-01467
000655  #
000656  # FK Constraints on child1, child2 and child3 are Ok.
000657  #
000658  # Problem with FK on child4:
000659  #
000660  # EVIDENCE-OF: R-51039-44840 The foreign key declared as part of table
000661  # child4 is an error because even though the parent key column is
000662  # indexed, the index is not UNIQUE.
000663  #
000664  # Problem with FK on child5:
000665  #
000666  # EVIDENCE-OF: R-01060-48788 The foreign key for table child5 is an
000667  # error because even though the parent key column has a unique index,
000668  # the index uses a different collating sequence.
000669  #
000670  # Problem with FK on child6 and child7:
000671  #
000672  # EVIDENCE-OF: R-63088-37469 Tables child6 and child7 are incorrect
000673  # because while both have UNIQUE indices on their parent keys, the keys
000674  # are not an exact match to the columns of a single UNIQUE index.
000675  #
000676  drop_all_tables
000677  do_test e_fkey-19.1 {
000678    execsql {
000679      CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
000680      CREATE UNIQUE INDEX i1 ON parent(c, d);
000681      CREATE INDEX i2 ON parent(e);
000682      CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);
000683  
000684      CREATE TABLE child1(f, g REFERENCES parent(a));                       -- Ok
000685      CREATE TABLE child2(h, i REFERENCES parent(b));                       -- Ok
000686      CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); -- Ok
000687      CREATE TABLE child4(l, m REFERENCES parent(e));                       -- Err
000688      CREATE TABLE child5(n, o REFERENCES parent(f));                       -- Err
000689      CREATE TABLE child6(p, q, FOREIGN KEY(p,q) REFERENCES parent(b, c));  -- Err
000690      CREATE TABLE child7(r REFERENCES parent(c));                          -- Err
000691    }
000692  } {}
000693  do_test e_fkey-19.2 {
000694    execsql {
000695      INSERT INTO parent VALUES(1, 2, 3, 4, 5, 6);
000696      INSERT INTO child1 VALUES('xxx', 1);
000697      INSERT INTO child2 VALUES('xxx', 2);
000698      INSERT INTO child3 VALUES(3, 4);
000699    }
000700  } {}
000701  do_test e_fkey-19.2 {
000702    catchsql { INSERT INTO child4 VALUES('xxx', 5) }
000703  } {1 {foreign key mismatch - "child4" referencing "parent"}}
000704  do_test e_fkey-19.3 {
000705    catchsql { INSERT INTO child5 VALUES('xxx', 6) }
000706  } {1 {foreign key mismatch - "child5" referencing "parent"}}
000707  do_test e_fkey-19.4 {
000708    catchsql { INSERT INTO child6 VALUES(2, 3) }
000709  } {1 {foreign key mismatch - "child6" referencing "parent"}}
000710  do_test e_fkey-19.5 {
000711    catchsql { INSERT INTO child7 VALUES(3) }
000712  } {1 {foreign key mismatch - "child7" referencing "parent"}}
000713  
000714  #-------------------------------------------------------------------------
000715  # Test errors in the database schema that are detected while preparing
000716  # DML statements. The error text for these messages always matches 
000717  # either "foreign key mismatch" or "no such table*" (using [string match]).
000718  #
000719  # EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key
000720  # errors that require looking at more than one table definition to
000721  # identify, then those errors are not detected when the tables are
000722  # created.
000723  #
000724  # EVIDENCE-OF: R-48391-38472 Instead, such errors prevent the
000725  # application from preparing SQL statements that modify the content of
000726  # the child or parent tables in ways that use the foreign keys.
000727  #
000728  # EVIDENCE-OF: R-03108-63659 The English language error message for
000729  # foreign key DML errors is usually "foreign key mismatch" but can also
000730  # be "no such table" if the parent table does not exist.
000731  #
000732  # EVIDENCE-OF: R-35763-48267 Foreign key DML errors are reported if: The
000733  # parent table does not exist, or The parent key columns named in the
000734  # foreign key constraint do not exist, or The parent key columns named
000735  # in the foreign key constraint are not the primary key of the parent
000736  # table and are not subject to a unique constraint using collating
000737  # sequence specified in the CREATE TABLE, or The child table references
000738  # the primary key of the parent without specifying the primary key
000739  # columns and the number of primary key columns in the parent do not
000740  # match the number of child key columns.
000741  #
000742  do_test e_fkey-20.1 {
000743    execsql {
000744      CREATE TABLE c1(c REFERENCES nosuchtable, d);
000745  
000746      CREATE TABLE p2(a, b, UNIQUE(a, b));
000747      CREATE TABLE c2(c, d, FOREIGN KEY(c, d) REFERENCES p2(a, x));
000748  
000749      CREATE TABLE p3(a PRIMARY KEY, b);
000750      CREATE TABLE c3(c REFERENCES p3(b), d);
000751  
000752      CREATE TABLE p4(a PRIMARY KEY, b);
000753      CREATE UNIQUE INDEX p4i ON p4(b COLLATE nocase);
000754      CREATE TABLE c4(c REFERENCES p4(b), d);
000755  
000756      CREATE TABLE p5(a PRIMARY KEY, b COLLATE nocase);
000757      CREATE UNIQUE INDEX p5i ON p5(b COLLATE binary);
000758      CREATE TABLE c5(c REFERENCES p5(b), d);
000759  
000760      CREATE TABLE p6(a PRIMARY KEY, b);
000761      CREATE TABLE c6(c, d, FOREIGN KEY(c, d) REFERENCES p6);
000762  
000763      CREATE TABLE p7(a, b, PRIMARY KEY(a, b));
000764      CREATE TABLE c7(c, d REFERENCES p7);
000765    }
000766  } {}
000767  
000768  foreach {tn tbl ptbl err} {
000769    2 c1 {} "no such table: main.nosuchtable"
000770    3 c2 p2 "foreign key mismatch - \"c2\" referencing \"p2\""
000771    4 c3 p3 "foreign key mismatch - \"c3\" referencing \"p3\""
000772    5 c4 p4 "foreign key mismatch - \"c4\" referencing \"p4\""
000773    6 c5 p5 "foreign key mismatch - \"c5\" referencing \"p5\""
000774    7 c6 p6 "foreign key mismatch - \"c6\" referencing \"p6\""
000775    8 c7 p7 "foreign key mismatch - \"c7\" referencing \"p7\""
000776  } {
000777    do_test e_fkey-20.$tn.1 {
000778      catchsql "INSERT INTO $tbl VALUES('a', 'b')"
000779    } [list 1 $err]
000780    do_test e_fkey-20.$tn.2 {
000781      catchsql "UPDATE $tbl SET c = ?, d = ?"
000782    } [list 1 $err]
000783    do_test e_fkey-20.$tn.3 {
000784      catchsql "INSERT INTO $tbl SELECT ?, ?"
000785    } [list 1 $err]
000786  
000787    if {$ptbl ne ""} {
000788      do_test e_fkey-20.$tn.4 {
000789        catchsql "DELETE FROM $ptbl"
000790      } [list 1 $err]
000791      do_test e_fkey-20.$tn.5 {
000792        catchsql "UPDATE $ptbl SET a = ?, b = ?"
000793      } [list 1 $err]
000794      do_test e_fkey-20.$tn.6 {
000795        catchsql "INSERT INTO $ptbl SELECT ?, ?"
000796      } [list 1 $err]
000797    }
000798  }
000799  
000800  #-------------------------------------------------------------------------
000801  # EVIDENCE-OF: R-19353-43643
000802  #
000803  # Test the example of foreign key mismatch errors caused by implicitly
000804  # mapping a child key to the primary key of the parent table when the
000805  # child key consists of a different number of columns to that primary key.
000806  # 
000807  drop_all_tables
000808  do_test e_fkey-21.1 {
000809    execsql {
000810      CREATE TABLE parent2(a, b, PRIMARY KEY(a,b));
000811  
000812      CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2);     -- Ok
000813      CREATE TABLE child9(x REFERENCES parent2);                          -- Err
000814      CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2); -- Err
000815    }
000816  } {}
000817  do_test e_fkey-21.2 {
000818    execsql {
000819      INSERT INTO parent2 VALUES('I', 'II');
000820      INSERT INTO child8 VALUES('I', 'II');
000821    }
000822  } {}
000823  do_test e_fkey-21.3 {
000824    catchsql { INSERT INTO child9 VALUES('I') }
000825  } {1 {foreign key mismatch - "child9" referencing "parent2"}}
000826  do_test e_fkey-21.4 {
000827    catchsql { INSERT INTO child9 VALUES('II') }
000828  } {1 {foreign key mismatch - "child9" referencing "parent2"}}
000829  do_test e_fkey-21.5 {
000830    catchsql { INSERT INTO child9 VALUES(NULL) }
000831  } {1 {foreign key mismatch - "child9" referencing "parent2"}}
000832  do_test e_fkey-21.6 {
000833    catchsql { INSERT INTO child10 VALUES('I', 'II', 'III') }
000834  } {1 {foreign key mismatch - "child10" referencing "parent2"}}
000835  do_test e_fkey-21.7 {
000836    catchsql { INSERT INTO child10 VALUES(1, 2, 3) }
000837  } {1 {foreign key mismatch - "child10" referencing "parent2"}}
000838  do_test e_fkey-21.8 {
000839    catchsql { INSERT INTO child10 VALUES(NULL, NULL, NULL) }
000840  } {1 {foreign key mismatch - "child10" referencing "parent2"}}
000841  
000842  #-------------------------------------------------------------------------
000843  # Test errors that are reported when creating the child table. 
000844  # Specifically:
000845  #
000846  #   * different number of child and parent key columns, and
000847  #   * child columns that do not exist.
000848  #
000849  # EVIDENCE-OF: R-23682-59820 By contrast, if foreign key errors can be
000850  # recognized simply by looking at the definition of the child table and
000851  # without having to consult the parent table definition, then the CREATE
000852  # TABLE statement for the child table fails.
000853  #
000854  # These errors are reported whether or not FK support is enabled.
000855  #
000856  # EVIDENCE-OF: R-33883-28833 Foreign key DDL errors are reported
000857  # regardless of whether or not foreign key constraints are enabled when
000858  # the table is created.
000859  #
000860  drop_all_tables
000861  foreach fk [list OFF ON] {
000862    execsql "PRAGMA foreign_keys = $fk"
000863    set i 0
000864    foreach {sql error} {
000865      "CREATE TABLE child1(a, b, FOREIGN KEY(a, b) REFERENCES p(c))"
000866        {number of columns in foreign key does not match the number of columns in the referenced table}
000867      "CREATE TABLE child2(a, b, FOREIGN KEY(a, b) REFERENCES p(c, d, e))"
000868        {number of columns in foreign key does not match the number of columns in the referenced table}
000869      "CREATE TABLE child2(a, b, FOREIGN KEY(a, c) REFERENCES p(c, d))"
000870        {unknown column "c" in foreign key definition}
000871      "CREATE TABLE child2(a, b, FOREIGN KEY(c, b) REFERENCES p(c, d))"
000872        {unknown column "c" in foreign key definition}
000873    } {
000874      do_test e_fkey-22.$fk.[incr i] {
000875        catchsql $sql
000876      } [list 1 $error]
000877    }
000878  }
000879  
000880  #-------------------------------------------------------------------------
000881  # Test that a REFERENCING clause that does not specify parent key columns
000882  # implicitly maps to the primary key of the parent table.
000883  #
000884  # EVIDENCE-OF: R-43879-08025 Attaching a "REFERENCES <parent-table>"
000885  # clause to a column definition creates a foreign
000886  # key constraint that maps the column to the primary key of
000887  # <parent-table>.
000888  # 
000889  do_test e_fkey-23.1 {
000890    execsql {
000891      CREATE TABLE p1(a, b, PRIMARY KEY(a, b));
000892      CREATE TABLE p2(a, b PRIMARY KEY);
000893      CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p1);
000894      CREATE TABLE c2(a, b REFERENCES p2);
000895    }
000896  } {}
000897  proc test_efkey_60 {tn isError sql} {
000898    do_test e_fkey-23.$tn "
000899      catchsql {$sql}
000900    " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
000901  }
000902  
000903  test_efkey_60 2 1 "INSERT INTO c1 VALUES(239, 231)"
000904  test_efkey_60 3 0 "INSERT INTO p1 VALUES(239, 231)"
000905  test_efkey_60 4 0 "INSERT INTO c1 VALUES(239, 231)"
000906  test_efkey_60 5 1 "INSERT INTO c2 VALUES(239, 231)"
000907  test_efkey_60 6 0 "INSERT INTO p2 VALUES(239, 231)"
000908  test_efkey_60 7 0 "INSERT INTO c2 VALUES(239, 231)"
000909  
000910  #-------------------------------------------------------------------------
000911  # Test that an index on on the child key columns of an FK constraint
000912  # is optional.
000913  #
000914  # EVIDENCE-OF: R-15417-28014 Indices are not required for child key
000915  # columns
000916  #
000917  # Also test that if an index is created on the child key columns, it does
000918  # not make a difference whether or not it is a UNIQUE index.
000919  #
000920  # EVIDENCE-OF: R-15741-50893 The child key index does not have to be
000921  # (and usually will not be) a UNIQUE index.
000922  #
000923  drop_all_tables
000924  do_test e_fkey-24.1 {
000925    execsql {
000926      CREATE TABLE parent(x, y, UNIQUE(y, x));
000927      CREATE TABLE c1(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
000928      CREATE TABLE c2(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
000929      CREATE TABLE c3(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
000930      CREATE INDEX c2i ON c2(a, b);
000931      CREATE UNIQUE INDEX c3i ON c2(b, a);
000932    }
000933  } {}
000934  proc test_efkey_61 {tn isError sql} {
000935    do_test e_fkey-24.$tn "
000936      catchsql {$sql}
000937    " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
000938  }
000939  foreach {tn c} [list 2 c1 3 c2 4 c3] {
000940    test_efkey_61 $tn.1 1 "INSERT INTO $c VALUES(1, 2)"
000941    test_efkey_61 $tn.2 0 "INSERT INTO parent VALUES(1, 2)"
000942    test_efkey_61 $tn.3 0 "INSERT INTO $c VALUES(1, 2)"
000943  
000944    execsql "DELETE FROM $c ; DELETE FROM parent"
000945  }
000946  
000947  #-------------------------------------------------------------------------
000948  # EVIDENCE-OF: R-00279-52283
000949  #
000950  # Test an example showing that when a row is deleted from the parent 
000951  # table, the child table is queried for orphaned rows as follows:
000952  #
000953  #   SELECT rowid FROM track WHERE trackartist = ?
000954  #
000955  # EVIDENCE-OF: R-23302-30956 If this SELECT returns any rows at all,
000956  # then SQLite concludes that deleting the row from the parent table
000957  # would violate the foreign key constraint and returns an error.
000958  #
000959  do_test e_fkey-25.1 {
000960    execsql {
000961      CREATE TABLE artist(
000962        artistid    INTEGER PRIMARY KEY, 
000963        artistname  TEXT
000964      );
000965      CREATE TABLE track(
000966        trackid     INTEGER, 
000967        trackname   TEXT, 
000968        trackartist INTEGER,
000969        FOREIGN KEY(trackartist) REFERENCES artist(artistid)
000970      );
000971    }
000972  } {}
000973  do_execsql_test e_fkey-25.2 {
000974    PRAGMA foreign_keys = OFF;
000975    EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
000976    EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?;
000977  } {
000978    0 0 0 {SCAN TABLE artist} 
000979    0 0 0 {SCAN TABLE track}
000980  }
000981  do_execsql_test e_fkey-25.3 {
000982    PRAGMA foreign_keys = ON;
000983    EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
000984  } {
000985    0 0 0 {SCAN TABLE artist} 
000986    0 0 0 {SCAN TABLE track}
000987  }
000988  do_test e_fkey-25.4 {
000989    execsql {
000990      INSERT INTO artist VALUES(5, 'artist 5');
000991      INSERT INTO artist VALUES(6, 'artist 6');
000992      INSERT INTO artist VALUES(7, 'artist 7');
000993      INSERT INTO track VALUES(1, 'track 1', 5);
000994      INSERT INTO track VALUES(2, 'track 2', 6);
000995    }
000996  } {}
000997  
000998  do_test e_fkey-25.5 {
000999    concat \
001000      [execsql { SELECT rowid FROM track WHERE trackartist = 5 }]   \
001001      [catchsql { DELETE FROM artist WHERE artistid = 5 }]
001002  } {1 1 {FOREIGN KEY constraint failed}}
001003  
001004  do_test e_fkey-25.6 {
001005    concat \
001006      [execsql { SELECT rowid FROM track WHERE trackartist = 7 }]   \
001007      [catchsql { DELETE FROM artist WHERE artistid = 7 }]
001008  } {0 {}}
001009  
001010  do_test e_fkey-25.7 {
001011    concat \
001012      [execsql { SELECT rowid FROM track WHERE trackartist = 6 }]   \
001013      [catchsql { DELETE FROM artist WHERE artistid = 6 }]
001014  } {2 1 {FOREIGN KEY constraint failed}}
001015  
001016  #-------------------------------------------------------------------------
001017  # EVIDENCE-OF: R-47936-10044 Or, more generally:
001018  # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
001019  #
001020  # Test that when a row is deleted from the parent table of an FK 
001021  # constraint, the child table is queried for orphaned rows. The
001022  # query is equivalent to:
001023  #
001024  #   SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
001025  #
001026  # Also test that when a row is inserted into the parent table, or when the 
001027  # parent key values of an existing row are modified, a query equivalent
001028  # to the following is planned. In some cases it is not executed, but it
001029  # is always planned.
001030  #
001031  #   SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
001032  #
001033  # EVIDENCE-OF: R-61616-46700 Similar queries may be run if the content
001034  # of the parent key is modified or a new row is inserted into the parent
001035  # table.
001036  #
001037  #
001038  drop_all_tables
001039  do_test e_fkey-26.1 {
001040    execsql { CREATE TABLE parent(x, y, UNIQUE(y, x)) }
001041  } {}
001042  foreach {tn sql} {
001043    2 { 
001044      CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y))
001045    }
001046    3 { 
001047      CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
001048      CREATE INDEX childi ON child(a, b);
001049    }
001050    4 { 
001051      CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
001052      CREATE UNIQUE INDEX childi ON child(b, a);
001053    }
001054  } {
001055    execsql $sql
001056  
001057    execsql {PRAGMA foreign_keys = OFF}
001058    set delete [concat \
001059        [eqp "DELETE FROM parent WHERE 1"] \
001060        [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"]
001061    ]
001062    set update [concat \
001063        [eqp "UPDATE parent SET x=?, y=?"] \
001064        [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"] \
001065        [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"]
001066    ]
001067    execsql {PRAGMA foreign_keys = ON}
001068  
001069    do_test e_fkey-26.$tn.1 { eqp "DELETE FROM parent WHERE 1" } $delete
001070    do_test e_fkey-26.$tn.2 { eqp "UPDATE parent set x=?, y=?" } $update
001071  
001072    execsql {DROP TABLE child}
001073  }
001074  
001075  #-------------------------------------------------------------------------
001076  # EVIDENCE-OF: R-14553-34013
001077  #
001078  # Test the example schema at the end of section 3. Also test that is
001079  # is "efficient". In this case "efficient" means that foreign key
001080  # related operations on the parent table do not provoke linear scans.
001081  #
001082  drop_all_tables
001083  do_test e_fkey-27.1 {
001084    execsql {
001085      CREATE TABLE artist(
001086        artistid    INTEGER PRIMARY KEY, 
001087        artistname  TEXT
001088      );
001089      CREATE TABLE track(
001090        trackid     INTEGER,
001091        trackname   TEXT, 
001092        trackartist INTEGER REFERENCES artist
001093      );
001094      CREATE INDEX trackindex ON track(trackartist);
001095    }
001096  } {}
001097  do_test e_fkey-27.2 {
001098    eqp { INSERT INTO artist VALUES(?, ?) }
001099  } {}
001100  do_execsql_test e_fkey-27.3 {
001101    EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ?
001102  } {
001103    0 0 0 {SCAN TABLE artist} 
001104    0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)} 
001105    0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)}
001106  }
001107  do_execsql_test e_fkey-27.4 {
001108    EXPLAIN QUERY PLAN DELETE FROM artist
001109  } {
001110    0 0 0 {SCAN TABLE artist} 
001111    0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)}
001112  }
001113  
001114  
001115  ###########################################################################
001116  ### SECTION 4.1: Composite Foreign Key Constraints
001117  ###########################################################################
001118  
001119  #-------------------------------------------------------------------------
001120  # Check that parent and child keys must have the same number of columns.
001121  #
001122  # EVIDENCE-OF: R-41062-34431 Parent and child keys must have the same
001123  # cardinality.
001124  #
001125  foreach {tn sql err} {
001126    1 "CREATE TABLE c(jj REFERENCES p(x, y))" 
001127      {foreign key on jj should reference only one column of table p}
001128  
001129    2 "CREATE TABLE c(jj REFERENCES p())" {near ")": syntax error}
001130  
001131    3 "CREATE TABLE c(jj, FOREIGN KEY(jj) REFERENCES p(x, y))" 
001132      {number of columns in foreign key does not match the number of columns in the referenced table}
001133  
001134    4 "CREATE TABLE c(jj, FOREIGN KEY(jj) REFERENCES p())" 
001135      {near ")": syntax error}
001136  
001137    5 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p())" 
001138      {near ")": syntax error}
001139  
001140    6 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x))" 
001141      {number of columns in foreign key does not match the number of columns in the referenced table}
001142  
001143    7 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x,y,z))" 
001144      {number of columns in foreign key does not match the number of columns in the referenced table}
001145  } {
001146    drop_all_tables
001147    do_test e_fkey-28.$tn [list catchsql $sql] [list 1 $err]
001148  }
001149  do_test e_fkey-28.8 {
001150    drop_all_tables
001151    execsql {
001152      CREATE TABLE p(x PRIMARY KEY);
001153      CREATE TABLE c(a, b, FOREIGN KEY(a,b) REFERENCES p);
001154    }
001155    catchsql {DELETE FROM p}
001156  } {1 {foreign key mismatch - "c" referencing "p"}}
001157  do_test e_fkey-28.9 {
001158    drop_all_tables
001159    execsql {
001160      CREATE TABLE p(x, y, PRIMARY KEY(x,y));
001161      CREATE TABLE c(a REFERENCES p);
001162    }
001163    catchsql {DELETE FROM p}
001164  } {1 {foreign key mismatch - "c" referencing "p"}}
001165  
001166  
001167  #-------------------------------------------------------------------------
001168  # EVIDENCE-OF: R-24676-09859
001169  #
001170  # Test the example schema in the "Composite Foreign Key Constraints" 
001171  # section.
001172  #
001173  do_test e_fkey-29.1 {
001174    execsql {
001175      CREATE TABLE album(
001176        albumartist TEXT,
001177        albumname TEXT,
001178        albumcover BINARY,
001179        PRIMARY KEY(albumartist, albumname)
001180      );
001181      CREATE TABLE song(
001182        songid INTEGER,
001183        songartist TEXT,
001184        songalbum TEXT,
001185        songname TEXT,
001186        FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist,albumname)
001187      );
001188    }
001189  } {}
001190  
001191  do_test e_fkey-29.2 {
001192    execsql {
001193      INSERT INTO album VALUES('Elvis Presley', 'Elvis'' Christmas Album', NULL);
001194      INSERT INTO song VALUES(
001195        1, 'Elvis Presley', 'Elvis'' Christmas Album', 'Here Comes Santa Clause'
001196      );
001197    }
001198  } {}
001199  do_test e_fkey-29.3 {
001200    catchsql {
001201      INSERT INTO song VALUES(2, 'Elvis Presley', 'Elvis Is Back!', 'Fever');
001202    }
001203  } {1 {FOREIGN KEY constraint failed}}
001204  
001205  
001206  #-------------------------------------------------------------------------
001207  # EVIDENCE-OF: R-33626-48418 In SQLite, if any of the child key columns
001208  # (in this case songartist and songalbum) are NULL, then there is no
001209  # requirement for a corresponding row in the parent table.
001210  #
001211  do_test e_fkey-30.1 {
001212    execsql {
001213      INSERT INTO song VALUES(2, 'Elvis Presley', NULL, 'Fever');
001214      INSERT INTO song VALUES(3, NULL, 'Elvis Is Back', 'Soldier Boy');
001215    }
001216  } {}
001217  
001218  ###########################################################################
001219  ### SECTION 4.2: Deferred Foreign Key Constraints
001220  ###########################################################################
001221  
001222  #-------------------------------------------------------------------------
001223  # Test that if a statement violates an immediate FK constraint, and the
001224  # database does not satisfy the FK constraint once all effects of the
001225  # statement have been applied, an error is reported and the effects of
001226  # the statement rolled back.
001227  #
001228  # EVIDENCE-OF: R-09323-30470 If a statement modifies the contents of the
001229  # database so that an immediate foreign key constraint is in violation
001230  # at the conclusion the statement, an exception is thrown and the
001231  # effects of the statement are reverted.
001232  #
001233  drop_all_tables
001234  do_test e_fkey-31.1 {
001235    execsql {
001236      CREATE TABLE king(a, b, PRIMARY KEY(a));
001237      CREATE TABLE prince(c REFERENCES king, d);
001238    }
001239  } {}
001240  
001241  do_test e_fkey-31.2 {
001242    # Execute a statement that violates the immediate FK constraint.
001243    catchsql { INSERT INTO prince VALUES(1, 2) }
001244  } {1 {FOREIGN KEY constraint failed}}
001245  
001246  do_test e_fkey-31.3 {
001247    # This time, use a trigger to fix the constraint violation before the
001248    # statement has finished executing. Then execute the same statement as
001249    # in the previous test case. This time, no error.
001250    execsql {
001251      CREATE TRIGGER kt AFTER INSERT ON prince WHEN
001252        NOT EXISTS (SELECT a FROM king WHERE a = new.c)
001253      BEGIN
001254        INSERT INTO king VALUES(new.c, NULL);
001255      END
001256    }
001257    execsql { INSERT INTO prince VALUES(1, 2) }
001258  } {}
001259  
001260  # Test that operating inside a transaction makes no difference to 
001261  # immediate constraint violation handling.
001262  do_test e_fkey-31.4 {
001263    execsql {
001264      BEGIN;
001265      INSERT INTO prince VALUES(2, 3);
001266      DROP TRIGGER kt;
001267    }
001268    catchsql { INSERT INTO prince VALUES(3, 4) }
001269  } {1 {FOREIGN KEY constraint failed}}
001270  do_test e_fkey-31.5 {
001271    execsql {
001272      COMMIT;
001273      SELECT * FROM king;
001274    }
001275  } {1 {} 2 {}}
001276  
001277  #-------------------------------------------------------------------------
001278  # Test that if a deferred constraint is violated within a transaction,
001279  # nothing happens immediately and the database is allowed to persist
001280  # in a state that does not satisfy the FK constraint. However attempts
001281  # to COMMIT the transaction fail until the FK constraint is satisfied.
001282  #
001283  # EVIDENCE-OF: R-49178-21358 By contrast, if a statement modifies the
001284  # contents of the database such that a deferred foreign key constraint
001285  # is violated, the violation is not reported immediately.
001286  #
001287  # EVIDENCE-OF: R-39692-12488 Deferred foreign key constraints are not
001288  # checked until the transaction tries to COMMIT.
001289  #
001290  # EVIDENCE-OF: R-55147-47664 For as long as the user has an open
001291  # transaction, the database is allowed to exist in a state that violates
001292  # any number of deferred foreign key constraints.
001293  #
001294  # EVIDENCE-OF: R-29604-30395 However, COMMIT will fail as long as
001295  # foreign key constraints remain in violation.
001296  #
001297  proc test_efkey_34 {tn isError sql} {
001298    do_test e_fkey-32.$tn "
001299      catchsql {$sql}
001300    " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
001301  }
001302  drop_all_tables
001303  
001304  test_efkey_34  1 0 {
001305    CREATE TABLE ll(k PRIMARY KEY);
001306    CREATE TABLE kk(c REFERENCES ll DEFERRABLE INITIALLY DEFERRED);
001307  }
001308  test_efkey_34  2 0 "BEGIN"
001309  test_efkey_34  3 0   "INSERT INTO kk VALUES(5)"
001310  test_efkey_34  4 0   "INSERT INTO kk VALUES(10)"
001311  test_efkey_34  5 1 "COMMIT"
001312  test_efkey_34  6 0   "INSERT INTO ll VALUES(10)"
001313  test_efkey_34  7 1 "COMMIT"
001314  test_efkey_34  8 0   "INSERT INTO ll VALUES(5)"
001315  test_efkey_34  9 0 "COMMIT"
001316  
001317  #-------------------------------------------------------------------------
001318  # When not running inside a transaction, a deferred constraint is similar
001319  # to an immediate constraint (violations are reported immediately).
001320  #
001321  # EVIDENCE-OF: R-56844-61705 If the current statement is not inside an
001322  # explicit transaction (a BEGIN/COMMIT/ROLLBACK block), then an implicit
001323  # transaction is committed as soon as the statement has finished
001324  # executing. In this case deferred constraints behave the same as
001325  # immediate constraints.
001326  #
001327  drop_all_tables
001328  proc test_efkey_35 {tn isError sql} {
001329    do_test e_fkey-33.$tn "
001330      catchsql {$sql}
001331    " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
001332  }
001333  do_test e_fkey-33.1 {
001334    execsql {
001335      CREATE TABLE parent(x, y);
001336      CREATE UNIQUE INDEX pi ON parent(x, y);
001337      CREATE TABLE child(a, b,
001338        FOREIGN KEY(a, b) REFERENCES parent(x, y) DEFERRABLE INITIALLY DEFERRED
001339      );
001340    }
001341  } {}
001342  test_efkey_35 2 1 "INSERT INTO child  VALUES('x', 'y')"
001343  test_efkey_35 3 0 "INSERT INTO parent VALUES('x', 'y')"
001344  test_efkey_35 4 0 "INSERT INTO child  VALUES('x', 'y')"
001345  
001346  
001347  #-------------------------------------------------------------------------
001348  # EVIDENCE-OF: R-12782-61841
001349  #
001350  # Test that an FK constraint is made deferred by adding the following
001351  # to the definition:
001352  #
001353  #   DEFERRABLE INITIALLY DEFERRED
001354  #
001355  # EVIDENCE-OF: R-09005-28791
001356  #
001357  # Also test that adding any of the following to a foreign key definition 
001358  # makes the constraint IMMEDIATE:
001359  #
001360  #   NOT DEFERRABLE INITIALLY DEFERRED
001361  #   NOT DEFERRABLE INITIALLY IMMEDIATE
001362  #   NOT DEFERRABLE
001363  #   DEFERRABLE INITIALLY IMMEDIATE
001364  #   DEFERRABLE
001365  #
001366  # Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT
001367  # DEFERRABLE clause).
001368  #
001369  # EVIDENCE-OF: R-35290-16460 Foreign key constraints are immediate by
001370  # default.
001371  #
001372  # EVIDENCE-OF: R-30323-21917 Each foreign key constraint in SQLite is
001373  # classified as either immediate or deferred.
001374  #
001375  drop_all_tables
001376  do_test e_fkey-34.1 {
001377    execsql {
001378      CREATE TABLE parent(x, y, z, PRIMARY KEY(x,y,z));
001379      CREATE TABLE c1(a, b, c,
001380        FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY DEFERRED
001381      );
001382      CREATE TABLE c2(a, b, c,
001383        FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY IMMEDIATE
001384      );
001385      CREATE TABLE c3(a, b, c,
001386        FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE
001387      );
001388      CREATE TABLE c4(a, b, c,
001389        FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY IMMEDIATE
001390      );
001391      CREATE TABLE c5(a, b, c,
001392        FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE
001393      );
001394      CREATE TABLE c6(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent);
001395  
001396      -- This FK constraint is the only deferrable one.
001397      CREATE TABLE c7(a, b, c,
001398        FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY DEFERRED
001399      );
001400  
001401      INSERT INTO parent VALUES('a', 'b', 'c');
001402      INSERT INTO parent VALUES('d', 'e', 'f');
001403      INSERT INTO parent VALUES('g', 'h', 'i');
001404      INSERT INTO parent VALUES('j', 'k', 'l');
001405      INSERT INTO parent VALUES('m', 'n', 'o');
001406      INSERT INTO parent VALUES('p', 'q', 'r');
001407      INSERT INTO parent VALUES('s', 't', 'u');
001408  
001409      INSERT INTO c1 VALUES('a', 'b', 'c');
001410      INSERT INTO c2 VALUES('d', 'e', 'f');
001411      INSERT INTO c3 VALUES('g', 'h', 'i');
001412      INSERT INTO c4 VALUES('j', 'k', 'l');
001413      INSERT INTO c5 VALUES('m', 'n', 'o');
001414      INSERT INTO c6 VALUES('p', 'q', 'r');
001415      INSERT INTO c7 VALUES('s', 't', 'u');
001416    }
001417  } {}
001418  
001419  proc test_efkey_29 {tn sql isError} {
001420    do_test e_fkey-34.$tn "catchsql {$sql}" [
001421      lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError
001422    ]
001423  }
001424  test_efkey_29  2 "BEGIN"                                   0
001425  test_efkey_29  3 "DELETE FROM parent WHERE x = 'a'"        1
001426  test_efkey_29  4 "DELETE FROM parent WHERE x = 'd'"        1
001427  test_efkey_29  5 "DELETE FROM parent WHERE x = 'g'"        1
001428  test_efkey_29  6 "DELETE FROM parent WHERE x = 'j'"        1
001429  test_efkey_29  7 "DELETE FROM parent WHERE x = 'm'"        1
001430  test_efkey_29  8 "DELETE FROM parent WHERE x = 'p'"        1
001431  test_efkey_29  9 "DELETE FROM parent WHERE x = 's'"        0
001432  test_efkey_29 10 "COMMIT"                                  1
001433  test_efkey_29 11 "ROLLBACK"                                0
001434  
001435  test_efkey_29  9 "BEGIN"                                   0
001436  test_efkey_29 10 "UPDATE parent SET z = 'z' WHERE z = 'c'" 1
001437  test_efkey_29 11 "UPDATE parent SET z = 'z' WHERE z = 'f'" 1
001438  test_efkey_29 12 "UPDATE parent SET z = 'z' WHERE z = 'i'" 1
001439  test_efkey_29 13 "UPDATE parent SET z = 'z' WHERE z = 'l'" 1
001440  test_efkey_29 14 "UPDATE parent SET z = 'z' WHERE z = 'o'" 1
001441  test_efkey_29 15 "UPDATE parent SET z = 'z' WHERE z = 'r'" 1
001442  test_efkey_29 16 "UPDATE parent SET z = 'z' WHERE z = 'u'" 0
001443  test_efkey_29 17 "COMMIT"                                  1
001444  test_efkey_29 18 "ROLLBACK"                                0
001445  
001446  test_efkey_29 17 "BEGIN"                                   0
001447  test_efkey_29 18 "INSERT INTO c1 VALUES(1, 2, 3)"          1
001448  test_efkey_29 19 "INSERT INTO c2 VALUES(1, 2, 3)"          1
001449  test_efkey_29 20 "INSERT INTO c3 VALUES(1, 2, 3)"          1
001450  test_efkey_29 21 "INSERT INTO c4 VALUES(1, 2, 3)"          1
001451  test_efkey_29 22 "INSERT INTO c5 VALUES(1, 2, 3)"          1
001452  test_efkey_29 22 "INSERT INTO c6 VALUES(1, 2, 3)"          1
001453  test_efkey_29 22 "INSERT INTO c7 VALUES(1, 2, 3)"          0
001454  test_efkey_29 23 "COMMIT"                                  1
001455  test_efkey_29 24 "INSERT INTO parent VALUES(1, 2, 3)"      0
001456  test_efkey_29 25 "COMMIT"                                  0
001457  
001458  test_efkey_29 26 "BEGIN"                                   0
001459  test_efkey_29 27 "UPDATE c1 SET a = 10"                    1
001460  test_efkey_29 28 "UPDATE c2 SET a = 10"                    1
001461  test_efkey_29 29 "UPDATE c3 SET a = 10"                    1
001462  test_efkey_29 30 "UPDATE c4 SET a = 10"                    1
001463  test_efkey_29 31 "UPDATE c5 SET a = 10"                    1
001464  test_efkey_29 31 "UPDATE c6 SET a = 10"                    1
001465  test_efkey_29 31 "UPDATE c7 SET a = 10"                    0
001466  test_efkey_29 32 "COMMIT"                                  1
001467  test_efkey_29 33 "ROLLBACK"                                0
001468  
001469  #-------------------------------------------------------------------------
001470  # EVIDENCE-OF: R-24499-57071
001471  #
001472  # Test an example from foreignkeys.html dealing with a deferred foreign 
001473  # key constraint.
001474  #
001475  do_test e_fkey-35.1 {
001476    drop_all_tables
001477    execsql {
001478      CREATE TABLE artist(
001479        artistid    INTEGER PRIMARY KEY, 
001480        artistname  TEXT
001481      );
001482      CREATE TABLE track(
001483        trackid     INTEGER,
001484        trackname   TEXT, 
001485        trackartist INTEGER REFERENCES artist(artistid) DEFERRABLE INITIALLY DEFERRED
001486      );
001487    }
001488  } {}
001489  do_test e_fkey-35.2 {
001490    execsql {
001491      BEGIN;
001492        INSERT INTO track VALUES(1, 'White Christmas', 5);
001493    }
001494    catchsql COMMIT
001495  } {1 {FOREIGN KEY constraint failed}}
001496  do_test e_fkey-35.3 {
001497    execsql {
001498      INSERT INTO artist VALUES(5, 'Bing Crosby');
001499      COMMIT;
001500    }
001501  } {}
001502  
001503  #-------------------------------------------------------------------------
001504  # Verify that a nested savepoint may be released without satisfying 
001505  # deferred foreign key constraints.
001506  #
001507  # EVIDENCE-OF: R-07223-48323 A nested savepoint transaction may be
001508  # RELEASEd while the database is in a state that does not satisfy a
001509  # deferred foreign key constraint.
001510  #
001511  drop_all_tables
001512  do_test e_fkey-36.1 {
001513    execsql {
001514      CREATE TABLE t1(a PRIMARY KEY,
001515        b REFERENCES t1 DEFERRABLE INITIALLY DEFERRED
001516      );
001517      INSERT INTO t1 VALUES(1, 1);
001518      INSERT INTO t1 VALUES(2, 2);
001519      INSERT INTO t1 VALUES(3, 3);
001520    }
001521  } {}
001522  do_test e_fkey-36.2 {
001523    execsql {
001524      BEGIN;
001525        SAVEPOINT one;
001526          INSERT INTO t1 VALUES(4, 5);
001527        RELEASE one;
001528    }
001529  } {}
001530  do_test e_fkey-36.3 {
001531    catchsql COMMIT
001532  } {1 {FOREIGN KEY constraint failed}}
001533  do_test e_fkey-36.4 {
001534    execsql {
001535      UPDATE t1 SET a = 5 WHERE a = 4;
001536      COMMIT;
001537    }
001538  } {}
001539  
001540  
001541  #-------------------------------------------------------------------------
001542  # Check that a transaction savepoint (an outermost savepoint opened when
001543  # the database was in auto-commit mode) cannot be released without
001544  # satisfying deferred foreign key constraints. It may be rolled back.
001545  #
001546  # EVIDENCE-OF: R-44295-13823 A transaction savepoint (a non-nested
001547  # savepoint that was opened while there was not currently an open
001548  # transaction), on the other hand, is subject to the same restrictions
001549  # as a COMMIT - attempting to RELEASE it while the database is in such a
001550  # state will fail.
001551  #
001552  do_test e_fkey-37.1 {
001553    execsql {
001554      SAVEPOINT one;
001555        SAVEPOINT two;
001556          INSERT INTO t1 VALUES(6, 7);
001557        RELEASE two;
001558    }
001559  } {}
001560  do_test e_fkey-37.2 {
001561    catchsql {RELEASE one}
001562  } {1 {FOREIGN KEY constraint failed}}
001563  do_test e_fkey-37.3 {
001564    execsql {
001565        UPDATE t1 SET a = 7 WHERE a = 6;
001566      RELEASE one;
001567    }
001568  } {}
001569  do_test e_fkey-37.4 {
001570    execsql {
001571      SAVEPOINT one;
001572        SAVEPOINT two;
001573          INSERT INTO t1 VALUES(9, 10);
001574        RELEASE two;
001575    }
001576  } {}
001577  do_test e_fkey-37.5 {
001578    catchsql {RELEASE one}
001579  } {1 {FOREIGN KEY constraint failed}}
001580  do_test e_fkey-37.6 {
001581    execsql {ROLLBACK TO one ; RELEASE one}
001582  } {}
001583  
001584  #-------------------------------------------------------------------------
001585  # Test that if a COMMIT operation fails due to deferred foreign key 
001586  # constraints, any nested savepoints remain open.
001587  #
001588  # EVIDENCE-OF: R-37736-42616 If a COMMIT statement (or the RELEASE of a
001589  # transaction SAVEPOINT) fails because the database is currently in a
001590  # state that violates a deferred foreign key constraint and there are
001591  # currently nested savepoints, the nested savepoints remain open.
001592  #
001593  do_test e_fkey-38.1 {
001594    execsql {
001595      DELETE FROM t1 WHERE a>3;
001596      SELECT * FROM t1;
001597    }
001598  } {1 1 2 2 3 3}
001599  do_test e_fkey-38.2 {
001600    execsql {
001601      BEGIN;
001602        INSERT INTO t1 VALUES(4, 4);
001603        SAVEPOINT one;
001604          INSERT INTO t1 VALUES(5, 6);
001605          SELECT * FROM t1;
001606    }
001607  } {1 1 2 2 3 3 4 4 5 6}
001608  do_test e_fkey-38.3 {
001609    catchsql COMMIT
001610  } {1 {FOREIGN KEY constraint failed}}
001611  do_test e_fkey-38.4 {
001612    execsql {
001613      ROLLBACK TO one;
001614      COMMIT;
001615      SELECT * FROM t1;
001616    }
001617  } {1 1 2 2 3 3 4 4}
001618  
001619  do_test e_fkey-38.5 {
001620    execsql {
001621      SAVEPOINT a;
001622        INSERT INTO t1 VALUES(5, 5);
001623        SAVEPOINT b;
001624          INSERT INTO t1 VALUES(6, 7);
001625          SAVEPOINT c;
001626            INSERT INTO t1 VALUES(7, 8);
001627    }
001628  } {}
001629  do_test e_fkey-38.6 {
001630    catchsql {RELEASE a}
001631  } {1 {FOREIGN KEY constraint failed}}
001632  do_test e_fkey-38.7 {
001633    execsql  {ROLLBACK TO c}
001634    catchsql {RELEASE a}
001635  } {1 {FOREIGN KEY constraint failed}}
001636  do_test e_fkey-38.8 {
001637    execsql  {
001638      ROLLBACK TO b;
001639      RELEASE a;
001640      SELECT * FROM t1;
001641    }
001642  } {1 1 2 2 3 3 4 4 5 5}
001643  
001644  ###########################################################################
001645  ### SECTION 4.3: ON DELETE and ON UPDATE Actions
001646  ###########################################################################
001647  
001648  #-------------------------------------------------------------------------
001649  # Test that configured ON DELETE and ON UPDATE actions take place when
001650  # deleting or modifying rows of the parent table, respectively.
001651  #
001652  # EVIDENCE-OF: R-48270-44282 Foreign key ON DELETE and ON UPDATE clauses
001653  # are used to configure actions that take place when deleting rows from
001654  # the parent table (ON DELETE), or modifying the parent key values of
001655  # existing rows (ON UPDATE).
001656  #
001657  # Test that a single FK constraint may have different actions configured
001658  # for ON DELETE and ON UPDATE.
001659  #
001660  # EVIDENCE-OF: R-48124-63225 A single foreign key constraint may have
001661  # different actions configured for ON DELETE and ON UPDATE.
001662  #
001663  do_test e_fkey-39.1 {
001664    execsql {
001665      CREATE TABLE p(a, b PRIMARY KEY, c);
001666      CREATE TABLE c1(d, e, f DEFAULT 'k0' REFERENCES p 
001667        ON UPDATE SET DEFAULT
001668        ON DELETE SET NULL
001669      );
001670  
001671      INSERT INTO p VALUES(0, 'k0', '');
001672      INSERT INTO p VALUES(1, 'k1', 'I');
001673      INSERT INTO p VALUES(2, 'k2', 'II');
001674      INSERT INTO p VALUES(3, 'k3', 'III');
001675  
001676      INSERT INTO c1 VALUES(1, 'xx', 'k1');
001677      INSERT INTO c1 VALUES(2, 'xx', 'k2');
001678      INSERT INTO c1 VALUES(3, 'xx', 'k3');
001679    }
001680  } {}
001681  do_test e_fkey-39.2 {
001682    execsql {
001683      UPDATE p SET b = 'k4' WHERE a = 1;
001684      SELECT * FROM c1;
001685    }
001686  } {1 xx k0 2 xx k2 3 xx k3}
001687  do_test e_fkey-39.3 {
001688    execsql {
001689      DELETE FROM p WHERE a = 2;
001690      SELECT * FROM c1;
001691    }
001692  } {1 xx k0 2 xx {} 3 xx k3}
001693  do_test e_fkey-39.4 {
001694    execsql {
001695      CREATE UNIQUE INDEX pi ON p(c);
001696      REPLACE INTO p VALUES(5, 'k5', 'III');
001697      SELECT * FROM c1;
001698    }
001699  } {1 xx k0 2 xx {} 3 xx {}}
001700  
001701  #-------------------------------------------------------------------------
001702  # Each foreign key in the system has an ON UPDATE and ON DELETE action,
001703  # either "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".
001704  #
001705  # EVIDENCE-OF: R-33326-45252 The ON DELETE and ON UPDATE action
001706  # associated with each foreign key in an SQLite database is one of "NO
001707  # ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".
001708  #
001709  # If none is specified explicitly, "NO ACTION" is the default.
001710  #
001711  # EVIDENCE-OF: R-19803-45884 If an action is not explicitly specified,
001712  # it defaults to "NO ACTION".
001713  # 
001714  drop_all_tables
001715  do_test e_fkey-40.1 {
001716    execsql {
001717      CREATE TABLE parent(x PRIMARY KEY, y);
001718      CREATE TABLE child1(a, 
001719        b REFERENCES parent ON UPDATE NO ACTION ON DELETE RESTRICT
001720      );
001721      CREATE TABLE child2(a, 
001722        b REFERENCES parent ON UPDATE RESTRICT ON DELETE SET NULL
001723      );
001724      CREATE TABLE child3(a, 
001725        b REFERENCES parent ON UPDATE SET NULL ON DELETE SET DEFAULT
001726      );
001727      CREATE TABLE child4(a, 
001728        b REFERENCES parent ON UPDATE SET DEFAULT ON DELETE CASCADE
001729      );
001730  
001731      -- Create some foreign keys that use the default action - "NO ACTION"
001732      CREATE TABLE child5(a, b REFERENCES parent ON UPDATE CASCADE);
001733      CREATE TABLE child6(a, b REFERENCES parent ON DELETE RESTRICT);
001734      CREATE TABLE child7(a, b REFERENCES parent ON DELETE NO ACTION);
001735      CREATE TABLE child8(a, b REFERENCES parent ON UPDATE NO ACTION);
001736    }
001737  } {}
001738  
001739  foreach {tn zTab lRes} {
001740    2 child1 {0 0 parent b {} {NO ACTION} RESTRICT NONE}
001741    3 child2 {0 0 parent b {} RESTRICT {SET NULL} NONE}
001742    4 child3 {0 0 parent b {} {SET NULL} {SET DEFAULT} NONE}
001743    5 child4 {0 0 parent b {} {SET DEFAULT} CASCADE NONE}
001744    6 child5 {0 0 parent b {} CASCADE {NO ACTION} NONE}
001745    7 child6 {0 0 parent b {} {NO ACTION} RESTRICT NONE}
001746    8 child7 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
001747    9 child8 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
001748  } {
001749    do_test e_fkey-40.$tn { execsql "PRAGMA foreign_key_list($zTab)" } $lRes
001750  }
001751  
001752  #-------------------------------------------------------------------------
001753  # Test that "NO ACTION" means that nothing happens to a child row when
001754  # it's parent row is updated or deleted.
001755  #
001756  # EVIDENCE-OF: R-19971-54976 Configuring "NO ACTION" means just that:
001757  # when a parent key is modified or deleted from the database, no special
001758  # action is taken.
001759  #
001760  drop_all_tables
001761  do_test e_fkey-41.1 {
001762    execsql {
001763      CREATE TABLE parent(p1, p2, PRIMARY KEY(p1, p2));
001764      CREATE TABLE child(c1, c2, 
001765        FOREIGN KEY(c1, c2) REFERENCES parent
001766        ON UPDATE NO ACTION
001767        ON DELETE NO ACTION
001768        DEFERRABLE INITIALLY DEFERRED
001769      );
001770      INSERT INTO parent VALUES('j', 'k');
001771      INSERT INTO parent VALUES('l', 'm');
001772      INSERT INTO child VALUES('j', 'k');
001773      INSERT INTO child VALUES('l', 'm');
001774    }
001775  } {}
001776  do_test e_fkey-41.2 {
001777    execsql {
001778      BEGIN;
001779        UPDATE parent SET p1='k' WHERE p1='j';
001780        DELETE FROM parent WHERE p1='l';
001781        SELECT * FROM child;
001782    }
001783  } {j k l m}
001784  do_test e_fkey-41.3 {
001785    catchsql COMMIT
001786  } {1 {FOREIGN KEY constraint failed}}
001787  do_test e_fkey-41.4 {
001788    execsql ROLLBACK
001789  } {}
001790  
001791  #-------------------------------------------------------------------------
001792  # Test that "RESTRICT" means the application is prohibited from deleting
001793  # or updating a parent table row when there exists one or more child keys
001794  # mapped to it.
001795  #
001796  # EVIDENCE-OF: R-04272-38653 The "RESTRICT" action means that the
001797  # application is prohibited from deleting (for ON DELETE RESTRICT) or
001798  # modifying (for ON UPDATE RESTRICT) a parent key when there exists one
001799  # or more child keys mapped to it.
001800  #
001801  drop_all_tables
001802  do_test e_fkey-41.1 {
001803    execsql {
001804      CREATE TABLE parent(p1, p2);
001805      CREATE UNIQUE INDEX parent_i ON parent(p1, p2);
001806      CREATE TABLE child1(c1, c2, 
001807        FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON DELETE RESTRICT
001808      );
001809      CREATE TABLE child2(c1, c2, 
001810        FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON UPDATE RESTRICT
001811      );
001812    }
001813  } {}
001814  do_test e_fkey-41.2 {
001815    execsql {
001816      INSERT INTO parent VALUES('a', 'b');
001817      INSERT INTO parent VALUES('c', 'd');
001818      INSERT INTO child1 VALUES('b', 'a');
001819      INSERT INTO child2 VALUES('d', 'c');
001820    }
001821  } {}
001822  do_test e_fkey-41.3 {
001823    catchsql { DELETE FROM parent WHERE p1 = 'a' }
001824  } {1 {FOREIGN KEY constraint failed}}
001825  do_test e_fkey-41.4 {
001826    catchsql { UPDATE parent SET p2 = 'e' WHERE p1 = 'c' }
001827  } {1 {FOREIGN KEY constraint failed}}
001828  
001829  #-------------------------------------------------------------------------
001830  # Test that RESTRICT is slightly different from NO ACTION for IMMEDIATE
001831  # constraints, in that it is enforced immediately, not at the end of the 
001832  # statement.
001833  #
001834  # EVIDENCE-OF: R-37997-42187 The difference between the effect of a
001835  # RESTRICT action and normal foreign key constraint enforcement is that
001836  # the RESTRICT action processing happens as soon as the field is updated
001837  # - not at the end of the current statement as it would with an
001838  # immediate constraint, or at the end of the current transaction as it
001839  # would with a deferred constraint.
001840  #
001841  drop_all_tables
001842  do_test e_fkey-42.1 {
001843    execsql {
001844      CREATE TABLE parent(x PRIMARY KEY);
001845      CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT);
001846      CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION);
001847  
001848      INSERT INTO parent VALUES('key1');
001849      INSERT INTO parent VALUES('key2');
001850      INSERT INTO child1 VALUES('key1');
001851      INSERT INTO child2 VALUES('key2');
001852  
001853      CREATE TRIGGER parent_t AFTER UPDATE ON parent BEGIN
001854        UPDATE child1 set c = new.x WHERE c = old.x;
001855        UPDATE child2 set c = new.x WHERE c = old.x;
001856      END;
001857    }
001858  } {}
001859  do_test e_fkey-42.2 {
001860    catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' }
001861  } {1 {FOREIGN KEY constraint failed}}
001862  do_test e_fkey-42.3 {
001863    execsql { 
001864      UPDATE parent SET x = 'key two' WHERE x = 'key2';
001865      SELECT * FROM child2;
001866    }
001867  } {{key two}}
001868  
001869  drop_all_tables
001870  do_test e_fkey-42.4 {
001871    execsql {
001872      CREATE TABLE parent(x PRIMARY KEY);
001873      CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT);
001874      CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION);
001875  
001876      INSERT INTO parent VALUES('key1');
001877      INSERT INTO parent VALUES('key2');
001878      INSERT INTO child1 VALUES('key1');
001879      INSERT INTO child2 VALUES('key2');
001880  
001881      CREATE TRIGGER parent_t AFTER DELETE ON parent BEGIN
001882        UPDATE child1 SET c = NULL WHERE c = old.x;
001883        UPDATE child2 SET c = NULL WHERE c = old.x;
001884      END;
001885    }
001886  } {}
001887  do_test e_fkey-42.5 {
001888    catchsql { DELETE FROM parent WHERE x = 'key1' }
001889  } {1 {FOREIGN KEY constraint failed}}
001890  do_test e_fkey-42.6 {
001891    execsql { 
001892      DELETE FROM parent WHERE x = 'key2';
001893      SELECT * FROM child2;
001894    }
001895  } {{}}
001896  
001897  drop_all_tables
001898  do_test e_fkey-42.7 {
001899    execsql {
001900      CREATE TABLE parent(x PRIMARY KEY);
001901      CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT);
001902      CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION);
001903  
001904      INSERT INTO parent VALUES('key1');
001905      INSERT INTO parent VALUES('key2');
001906      INSERT INTO child1 VALUES('key1');
001907      INSERT INTO child2 VALUES('key2');
001908    }
001909  } {}
001910  do_test e_fkey-42.8 {
001911    catchsql { REPLACE INTO parent VALUES('key1') }
001912  } {1 {FOREIGN KEY constraint failed}}
001913  do_test e_fkey-42.9 {
001914    execsql { 
001915      REPLACE INTO parent VALUES('key2');
001916      SELECT * FROM child2;
001917    }
001918  } {key2}
001919  
001920  #-------------------------------------------------------------------------
001921  # Test that RESTRICT is enforced immediately, even for a DEFERRED constraint.
001922  #
001923  # EVIDENCE-OF: R-24179-60523 Even if the foreign key constraint it is
001924  # attached to is deferred, configuring a RESTRICT action causes SQLite
001925  # to return an error immediately if a parent key with dependent child
001926  # keys is deleted or modified.
001927  #
001928  drop_all_tables
001929  do_test e_fkey-43.1 {
001930    execsql {
001931      CREATE TABLE parent(x PRIMARY KEY);
001932      CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT
001933        DEFERRABLE INITIALLY DEFERRED
001934      );
001935      CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION
001936        DEFERRABLE INITIALLY DEFERRED
001937      );
001938  
001939      INSERT INTO parent VALUES('key1');
001940      INSERT INTO parent VALUES('key2');
001941      INSERT INTO child1 VALUES('key1');
001942      INSERT INTO child2 VALUES('key2');
001943      BEGIN;
001944    }
001945  } {}
001946  do_test e_fkey-43.2 {
001947    catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' }
001948  } {1 {FOREIGN KEY constraint failed}}
001949  do_test e_fkey-43.3 {
001950    execsql { UPDATE parent SET x = 'key two' WHERE x = 'key2' }
001951  } {}
001952  do_test e_fkey-43.4 {
001953    catchsql COMMIT
001954  } {1 {FOREIGN KEY constraint failed}}
001955  do_test e_fkey-43.5 {
001956    execsql {
001957      UPDATE child2 SET c = 'key two';
001958      COMMIT;
001959    }
001960  } {}
001961  
001962  drop_all_tables
001963  do_test e_fkey-43.6 {
001964    execsql {
001965      CREATE TABLE parent(x PRIMARY KEY);
001966      CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT
001967        DEFERRABLE INITIALLY DEFERRED
001968      );
001969      CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION
001970        DEFERRABLE INITIALLY DEFERRED
001971      );
001972  
001973      INSERT INTO parent VALUES('key1');
001974      INSERT INTO parent VALUES('key2');
001975      INSERT INTO child1 VALUES('key1');
001976      INSERT INTO child2 VALUES('key2');
001977      BEGIN;
001978    }
001979  } {}
001980  do_test e_fkey-43.7 {
001981    catchsql { DELETE FROM parent WHERE x = 'key1' }
001982  } {1 {FOREIGN KEY constraint failed}}
001983  do_test e_fkey-43.8 {
001984    execsql { DELETE FROM parent WHERE x = 'key2' }
001985  } {}
001986  do_test e_fkey-43.9 {
001987    catchsql COMMIT
001988  } {1 {FOREIGN KEY constraint failed}}
001989  do_test e_fkey-43.10 {
001990    execsql {
001991      UPDATE child2 SET c = NULL;
001992      COMMIT;
001993    }
001994  } {}
001995  
001996  #-------------------------------------------------------------------------
001997  # Test SET NULL actions.
001998  #
001999  # EVIDENCE-OF: R-03353-05327 If the configured action is "SET NULL",
002000  # then when a parent key is deleted (for ON DELETE SET NULL) or modified
002001  # (for ON UPDATE SET NULL), the child key columns of all rows in the
002002  # child table that mapped to the parent key are set to contain SQL NULL
002003  # values.
002004  #
002005  drop_all_tables
002006  do_test e_fkey-44.1 {
002007    execsql {
002008      CREATE TABLE pA(x PRIMARY KEY);
002009      CREATE TABLE cA(c REFERENCES pA ON DELETE SET NULL);
002010      CREATE TABLE cB(c REFERENCES pA ON UPDATE SET NULL);
002011  
002012      INSERT INTO pA VALUES(X'ABCD');
002013      INSERT INTO pA VALUES(X'1234');
002014      INSERT INTO cA VALUES(X'ABCD');
002015      INSERT INTO cB VALUES(X'1234');
002016    }
002017  } {}
002018  do_test e_fkey-44.2 {
002019    execsql {
002020      DELETE FROM pA WHERE rowid = 1;
002021      SELECT quote(x) FROM pA;
002022    }
002023  } {X'1234'}
002024  do_test e_fkey-44.3 {
002025    execsql {
002026      SELECT quote(c) FROM cA;
002027    }
002028  } {NULL}
002029  do_test e_fkey-44.4 {
002030    execsql {
002031      UPDATE pA SET x = X'8765' WHERE rowid = 2;
002032      SELECT quote(x) FROM pA;
002033    }
002034  } {X'8765'}
002035  do_test e_fkey-44.5 {
002036    execsql { SELECT quote(c) FROM cB }
002037  } {NULL}
002038  
002039  #-------------------------------------------------------------------------
002040  # Test SET DEFAULT actions.
002041  #
002042  # EVIDENCE-OF: R-43054-54832 The "SET DEFAULT" actions are similar to
002043  # "SET NULL", except that each of the child key columns is set to
002044  # contain the columns default value instead of NULL.
002045  #
002046  drop_all_tables
002047  do_test e_fkey-45.1 {
002048    execsql {
002049      CREATE TABLE pA(x PRIMARY KEY);
002050      CREATE TABLE cA(c DEFAULT X'0000' REFERENCES pA ON DELETE SET DEFAULT);
002051      CREATE TABLE cB(c DEFAULT X'9999' REFERENCES pA ON UPDATE SET DEFAULT);
002052  
002053      INSERT INTO pA(rowid, x) VALUES(1, X'0000');
002054      INSERT INTO pA(rowid, x) VALUES(2, X'9999');
002055      INSERT INTO pA(rowid, x) VALUES(3, X'ABCD');
002056      INSERT INTO pA(rowid, x) VALUES(4, X'1234');
002057  
002058      INSERT INTO cA VALUES(X'ABCD');
002059      INSERT INTO cB VALUES(X'1234');
002060    }
002061  } {}
002062  do_test e_fkey-45.2 {
002063    execsql {
002064      DELETE FROM pA WHERE rowid = 3;
002065      SELECT quote(x) FROM pA ORDER BY rowid;
002066    }
002067  } {X'0000' X'9999' X'1234'}
002068  do_test e_fkey-45.3 {
002069    execsql { SELECT quote(c) FROM cA }
002070  } {X'0000'}
002071  do_test e_fkey-45.4 {
002072    execsql {
002073      UPDATE pA SET x = X'8765' WHERE rowid = 4;
002074      SELECT quote(x) FROM pA ORDER BY rowid;
002075    }
002076  } {X'0000' X'9999' X'8765'}
002077  do_test e_fkey-45.5 {
002078    execsql { SELECT quote(c) FROM cB }
002079  } {X'9999'}
002080  
002081  #-------------------------------------------------------------------------
002082  # Test ON DELETE CASCADE actions.
002083  #
002084  # EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or
002085  # update operation on the parent key to each dependent child key.
002086  #
002087  # EVIDENCE-OF: R-61809-62207 For an "ON DELETE CASCADE" action, this
002088  # means that each row in the child table that was associated with the
002089  # deleted parent row is also deleted.
002090  #
002091  drop_all_tables
002092  do_test e_fkey-46.1 {
002093    execsql {
002094      CREATE TABLE p1(a, b UNIQUE);
002095      CREATE TABLE c1(c REFERENCES p1(b) ON DELETE CASCADE, d);
002096      INSERT INTO p1 VALUES(NULL, NULL);
002097      INSERT INTO p1 VALUES(4, 4);
002098      INSERT INTO p1 VALUES(5, 5);
002099      INSERT INTO c1 VALUES(NULL, NULL);
002100      INSERT INTO c1 VALUES(4, 4);
002101      INSERT INTO c1 VALUES(5, 5);
002102      SELECT count(*) FROM c1;
002103    }
002104  } {3}
002105  do_test e_fkey-46.2 {
002106    execsql {
002107      DELETE FROM p1 WHERE a = 4;
002108      SELECT d, c FROM c1;
002109    }
002110  } {{} {} 5 5}
002111  do_test e_fkey-46.3 {
002112    execsql {
002113      DELETE FROM p1;
002114      SELECT d, c FROM c1;
002115    }
002116  } {{} {}}
002117  do_test e_fkey-46.4 {
002118    execsql { SELECT * FROM p1 }
002119  } {}
002120  
002121  
002122  #-------------------------------------------------------------------------
002123  # Test ON UPDATE CASCADE actions.
002124  #
002125  # EVIDENCE-OF: R-13877-64542 For an "ON UPDATE CASCADE" action, it means
002126  # that the values stored in each dependent child key are modified to
002127  # match the new parent key values.
002128  #
002129  # EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or
002130  # update operation on the parent key to each dependent child key.
002131  #
002132  drop_all_tables
002133  do_test e_fkey-47.1 {
002134    execsql {
002135      CREATE TABLE p1(a, b UNIQUE);
002136      CREATE TABLE c1(c REFERENCES p1(b) ON UPDATE CASCADE, d);
002137      INSERT INTO p1 VALUES(NULL, NULL);
002138      INSERT INTO p1 VALUES(4, 4);
002139      INSERT INTO p1 VALUES(5, 5);
002140      INSERT INTO c1 VALUES(NULL, NULL);
002141      INSERT INTO c1 VALUES(4, 4);
002142      INSERT INTO c1 VALUES(5, 5);
002143      SELECT count(*) FROM c1;
002144    }
002145  } {3}
002146  do_test e_fkey-47.2 {
002147    execsql {
002148      UPDATE p1 SET b = 10 WHERE b = 5;
002149      SELECT d, c FROM c1;
002150    }
002151  } {{} {} 4 4 5 10}
002152  do_test e_fkey-47.3 {
002153    execsql {
002154      UPDATE p1 SET b = 11 WHERE b = 4;
002155      SELECT d, c FROM c1;
002156    }
002157  } {{} {} 4 11 5 10}
002158  do_test e_fkey-47.4 {
002159    execsql { 
002160      UPDATE p1 SET b = 6 WHERE b IS NULL;
002161      SELECT d, c FROM c1;
002162    }
002163  } {{} {} 4 11 5 10}
002164  do_test e_fkey-46.5 {
002165    execsql { SELECT * FROM p1 }
002166  } {{} 6 4 11 5 10}
002167  
002168  #-------------------------------------------------------------------------
002169  # EVIDENCE-OF: R-65058-57158
002170  #
002171  # Test an example from the "ON DELETE and ON UPDATE Actions" section 
002172  # of foreignkeys.html.
002173  #
002174  drop_all_tables
002175  do_test e_fkey-48.1 {
002176    execsql {
002177      CREATE TABLE artist(
002178        artistid    INTEGER PRIMARY KEY, 
002179        artistname  TEXT
002180      );
002181      CREATE TABLE track(
002182        trackid     INTEGER,
002183        trackname   TEXT, 
002184        trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE
002185      );
002186  
002187      INSERT INTO artist VALUES(1, 'Dean Martin');
002188      INSERT INTO artist VALUES(2, 'Frank Sinatra');
002189      INSERT INTO track VALUES(11, 'That''s Amore', 1);
002190      INSERT INTO track VALUES(12, 'Christmas Blues', 1);
002191      INSERT INTO track VALUES(13, 'My Way', 2);
002192    }
002193  } {}
002194  do_test e_fkey-48.2 {
002195    execsql {
002196      UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin';
002197    }
002198  } {}
002199  do_test e_fkey-48.3 {
002200    execsql { SELECT * FROM artist }
002201  } {2 {Frank Sinatra} 100 {Dean Martin}}
002202  do_test e_fkey-48.4 {
002203    execsql { SELECT * FROM track }
002204  } {11 {That's Amore} 100 12 {Christmas Blues} 100 13 {My Way} 2}
002205  
002206  
002207  #-------------------------------------------------------------------------
002208  # Verify that adding an FK action does not absolve the user of the 
002209  # requirement not to violate the foreign key constraint.
002210  #
002211  # EVIDENCE-OF: R-53968-51642 Configuring an ON UPDATE or ON DELETE
002212  # action does not mean that the foreign key constraint does not need to
002213  # be satisfied.
002214  #
002215  drop_all_tables
002216  do_test e_fkey-49.1 {
002217    execsql {
002218      CREATE TABLE parent(a COLLATE nocase, b, c, PRIMARY KEY(c, a));
002219      CREATE TABLE child(d DEFAULT 'a', e, f DEFAULT 'c',
002220        FOREIGN KEY(f, d) REFERENCES parent ON UPDATE SET DEFAULT
002221      );
002222  
002223      INSERT INTO parent VALUES('A', 'b', 'c');
002224      INSERT INTO parent VALUES('ONE', 'two', 'three');
002225      INSERT INTO child VALUES('one', 'two', 'three');
002226    }
002227  } {}
002228  do_test e_fkey-49.2 {
002229    execsql {
002230      BEGIN;
002231        UPDATE parent SET a = '' WHERE a = 'oNe';
002232        SELECT * FROM child;
002233    }
002234  } {a two c}
002235  do_test e_fkey-49.3 {
002236    execsql {
002237      ROLLBACK;
002238      DELETE FROM parent WHERE a = 'A';
002239      SELECT * FROM parent;
002240    }
002241  } {ONE two three}
002242  do_test e_fkey-49.4 {
002243    catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' }
002244  } {1 {FOREIGN KEY constraint failed}}
002245  
002246  
002247  #-------------------------------------------------------------------------
002248  # EVIDENCE-OF: R-11856-19836
002249  #
002250  # Test an example from the "ON DELETE and ON UPDATE Actions" section 
002251  # of foreignkeys.html. This example shows that adding an "ON DELETE DEFAULT"
002252  # clause does not abrogate the need to satisfy the foreign key constraint
002253  # (R-28220-46694).
002254  #
002255  # EVIDENCE-OF: R-28220-46694 For example, if an "ON DELETE SET DEFAULT"
002256  # action is configured, but there is no row in the parent table that
002257  # corresponds to the default values of the child key columns, deleting a
002258  # parent key while dependent child keys exist still causes a foreign key
002259  # violation.
002260  #
002261  drop_all_tables
002262  do_test e_fkey-50.1 {
002263    execsql {
002264      CREATE TABLE artist(
002265        artistid    INTEGER PRIMARY KEY, 
002266        artistname  TEXT
002267      );
002268      CREATE TABLE track(
002269        trackid     INTEGER,
002270        trackname   TEXT, 
002271        trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT
002272      );
002273      INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
002274      INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
002275    }
002276  } {}
002277  do_test e_fkey-50.2 {
002278    catchsql { DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.' }
002279  } {1 {FOREIGN KEY constraint failed}}
002280  do_test e_fkey-50.3 {
002281    execsql {
002282      INSERT INTO artist VALUES(0, 'Unknown Artist');
002283      DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
002284    }
002285  } {}
002286  do_test e_fkey-50.4 {
002287    execsql { SELECT * FROM artist }
002288  } {0 {Unknown Artist}}
002289  do_test e_fkey-50.5 {
002290    execsql { SELECT * FROM track }
002291  } {14 {Mr. Bojangles} 0}
002292  
002293  #-------------------------------------------------------------------------
002294  # EVIDENCE-OF: R-09564-22170
002295  #
002296  # Check that the order of steps in an UPDATE or DELETE on a parent 
002297  # table is as follows:
002298  #
002299  #   1. Execute applicable BEFORE trigger programs,
002300  #   2. Check local (non foreign key) constraints,
002301  #   3. Update or delete the row in the parent table,
002302  #   4. Perform any required foreign key actions,
002303  #   5. Execute applicable AFTER trigger programs. 
002304  #
002305  drop_all_tables
002306  do_test e_fkey-51.1 {
002307    proc maxparent {args} { db one {SELECT max(x) FROM parent} }
002308    db func maxparent maxparent
002309  
002310    execsql {
002311      CREATE TABLE parent(x PRIMARY KEY);
002312  
002313      CREATE TRIGGER bu BEFORE UPDATE ON parent BEGIN
002314        INSERT INTO parent VALUES(new.x-old.x);
002315      END;
002316      CREATE TABLE child(
002317        a DEFAULT (maxparent()) REFERENCES parent ON UPDATE SET DEFAULT
002318      );
002319      CREATE TRIGGER au AFTER UPDATE ON parent BEGIN
002320        INSERT INTO parent VALUES(new.x+old.x);
002321      END;
002322  
002323      INSERT INTO parent VALUES(1);
002324      INSERT INTO child VALUES(1);
002325    }
002326  } {}
002327  do_test e_fkey-51.2 {
002328    execsql {
002329      UPDATE parent SET x = 22;
002330      SELECT * FROM parent ORDER BY rowid; SELECT 'xxx' ; SELECT a FROM child;
002331    }
002332  } {22 21 23 xxx 22}
002333  do_test e_fkey-51.3 {
002334    execsql {
002335      DELETE FROM child;
002336      DELETE FROM parent;
002337      INSERT INTO parent VALUES(-1);
002338      INSERT INTO child VALUES(-1);
002339      UPDATE parent SET x = 22;
002340      SELECT * FROM parent ORDER BY rowid; SELECT 'xxx' ; SELECT a FROM child;
002341    }
002342  } {22 23 21 xxx 23}
002343  
002344  
002345  #-------------------------------------------------------------------------
002346  # Verify that ON UPDATE actions only actually take place if the parent key
002347  # is set to a new value that is distinct from the old value. The default
002348  # collation sequence and affinity are used to determine if the new value
002349  # is 'distinct' from the old or not.
002350  #
002351  # EVIDENCE-OF: R-27383-10246 An ON UPDATE action is only taken if the
002352  # values of the parent key are modified so that the new parent key
002353  # values are not equal to the old.
002354  #
002355  drop_all_tables
002356  do_test e_fkey-52.1 {
002357    execsql {
002358      CREATE TABLE zeus(a INTEGER COLLATE NOCASE, b, PRIMARY KEY(a, b));
002359      CREATE TABLE apollo(c, d, 
002360        FOREIGN KEY(c, d) REFERENCES zeus ON UPDATE CASCADE
002361      );
002362      INSERT INTO zeus VALUES('abc', 'xyz');
002363      INSERT INTO apollo VALUES('ABC', 'xyz');
002364    }
002365    execsql {
002366      UPDATE zeus SET a = 'aBc';
002367      SELECT * FROM apollo;
002368    }
002369  } {ABC xyz}
002370  do_test e_fkey-52.2 {
002371    execsql {
002372      UPDATE zeus SET a = 1, b = 1;
002373      SELECT * FROM apollo;
002374    }
002375  } {1 1}
002376  do_test e_fkey-52.3 {
002377    execsql {
002378      UPDATE zeus SET a = 1, b = 1;
002379      SELECT typeof(c), c, typeof(d), d FROM apollo;
002380    }
002381  } {integer 1 integer 1}
002382  do_test e_fkey-52.4 {
002383    execsql {
002384      UPDATE zeus SET a = '1';
002385      SELECT typeof(c), c, typeof(d), d FROM apollo;
002386    }
002387  } {integer 1 integer 1}
002388  do_test e_fkey-52.5 {
002389    execsql {
002390      UPDATE zeus SET b = '1';
002391      SELECT typeof(c), c, typeof(d), d FROM apollo;
002392    }
002393  } {integer 1 text 1}
002394  do_test e_fkey-52.6 {
002395    execsql {
002396      UPDATE zeus SET b = NULL;
002397      SELECT typeof(c), c, typeof(d), d FROM apollo;
002398    }
002399  } {integer 1 null {}}
002400  
002401  #-------------------------------------------------------------------------
002402  # EVIDENCE-OF: R-35129-58141
002403  #
002404  # Test an example from the "ON DELETE and ON UPDATE Actions" section 
002405  # of foreignkeys.html. This example demonstrates that ON UPDATE actions
002406  # only take place if at least one parent key column is set to a value 
002407  # that is distinct from its previous value.
002408  #
002409  drop_all_tables
002410  do_test e_fkey-53.1 {
002411    execsql {
002412      CREATE TABLE parent(x PRIMARY KEY);
002413      CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL);
002414      INSERT INTO parent VALUES('key');
002415      INSERT INTO child VALUES('key');
002416    }
002417  } {}
002418  do_test e_fkey-53.2 {
002419    execsql {
002420      UPDATE parent SET x = 'key';
002421      SELECT IFNULL(y, 'null') FROM child;
002422    }
002423  } {key}
002424  do_test e_fkey-53.3 {
002425    execsql {
002426      UPDATE parent SET x = 'key2';
002427      SELECT IFNULL(y, 'null') FROM child;
002428    }
002429  } {null}
002430  
002431  ###########################################################################
002432  ### SECTION 5: CREATE, ALTER and DROP TABLE commands
002433  ###########################################################################
002434  
002435  #-------------------------------------------------------------------------
002436  # Test that parent keys are not checked when tables are created.
002437  #
002438  # EVIDENCE-OF: R-36018-21755 The parent key definitions of foreign key
002439  # constraints are not checked when a table is created.
002440  #
002441  # EVIDENCE-OF: R-25384-39337 There is nothing stopping the user from
002442  # creating a foreign key definition that refers to a parent table that
002443  # does not exist, or to parent key columns that do not exist or are not
002444  # collectively bound by a PRIMARY KEY or UNIQUE constraint.
002445  #
002446  # Child keys are checked to ensure all component columns exist. If parent
002447  # key columns are explicitly specified, SQLite checks to make sure there
002448  # are the same number of columns in the child and parent keys. (TODO: This
002449  # is tested but does not correspond to any testable statement.)
002450  #
002451  # Also test that the above statements are true regardless of whether or not
002452  # foreign keys are enabled:  "A CREATE TABLE command operates the same whether
002453  # or not foreign key constraints are enabled."
002454  #
002455  # EVIDENCE-OF: R-08908-23439 A CREATE TABLE command operates the same
002456  # whether or not foreign key constraints are enabled.
002457  # 
002458  foreach {tn zCreateTbl lRes} {
002459    1 "CREATE TABLE t1(a, b REFERENCES t1)"                            {0 {}}
002460    2 "CREATE TABLE t1(a, b REFERENCES t2)"                            {0 {}}
002461    3 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1)"          {0 {}}
002462    4 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)"          {0 {}}
002463    5 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)"          {0 {}}
002464    6 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2(n,d))"     {0 {}}
002465    7 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1(a,b))"     {0 {}}
002466  
002467    A "CREATE TABLE t1(a, b, FOREIGN KEY(c,b) REFERENCES t2)"          
002468       {1 {unknown column "c" in foreign key definition}}
002469    B "CREATE TABLE t1(a, b, FOREIGN KEY(c,b) REFERENCES t2(d))"          
002470       {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
002471  } {
002472    do_test e_fkey-54.$tn.off {
002473      drop_all_tables
002474      execsql {PRAGMA foreign_keys = OFF}
002475      catchsql $zCreateTbl
002476    } $lRes
002477    do_test e_fkey-54.$tn.on {
002478      drop_all_tables
002479      execsql {PRAGMA foreign_keys = ON}
002480      catchsql $zCreateTbl
002481    } $lRes
002482  }
002483  
002484  #-------------------------------------------------------------------------
002485  # EVIDENCE-OF: R-47952-62498 It is not possible to use the "ALTER TABLE
002486  # ... ADD COLUMN" syntax to add a column that includes a REFERENCES
002487  # clause, unless the default value of the new column is NULL. Attempting
002488  # to do so returns an error.
002489  #
002490  proc test_efkey_6 {tn zAlter isError} {
002491    drop_all_tables 
002492  
002493    do_test e_fkey-56.$tn.1 "
002494      execsql { CREATE TABLE tbl(a, b) }
002495      [list catchsql $zAlter]
002496    " [lindex {{0 {}} {1 {Cannot add a REFERENCES column with non-NULL default value}}} $isError]
002497  
002498  }
002499  
002500  test_efkey_6 1 "ALTER TABLE tbl ADD COLUMN c REFERENCES xx" 0
002501  test_efkey_6 2 "ALTER TABLE tbl ADD COLUMN c DEFAULT NULL REFERENCES xx" 0
002502  test_efkey_6 3 "ALTER TABLE tbl ADD COLUMN c DEFAULT 0 REFERENCES xx" 1
002503  
002504  #-------------------------------------------------------------------------
002505  # Test that ALTER TABLE adjusts REFERENCES clauses when the parent table
002506  # is RENAMED.
002507  #
002508  # EVIDENCE-OF: R-47080-02069 If an "ALTER TABLE ... RENAME TO" command
002509  # is used to rename a table that is the parent table of one or more
002510  # foreign key constraints, the definitions of the foreign key
002511  # constraints are modified to refer to the parent table by its new name
002512  #
002513  # Test that these adjustments are visible in the sqlite_master table.
002514  #
002515  # EVIDENCE-OF: R-63827-54774 The text of the child CREATE TABLE
002516  # statement or statements stored in the sqlite_master table are modified
002517  # to reflect the new parent table name.
002518  #
002519  do_test e_fkey-56.1 {
002520    drop_all_tables
002521    execsql {
002522      CREATE TABLE 'p 1 "parent one"'(a REFERENCES 'p 1 "parent one"', b, PRIMARY KEY(b));
002523  
002524      CREATE TABLE c1(c, d REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE);
002525      CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE);
002526      CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE);
002527  
002528      INSERT INTO 'p 1 "parent one"' VALUES(1, 1);
002529      INSERT INTO c1 VALUES(1, 1);
002530      INSERT INTO c2 VALUES(1, 1);
002531      INSERT INTO c3 VALUES(1, 1);
002532  
002533      -- CREATE TABLE q(a, b, PRIMARY KEY(b));
002534    }
002535  } {}
002536  do_test e_fkey-56.2 {
002537    execsql { ALTER TABLE 'p 1 "parent one"' RENAME TO p }
002538  } {}
002539  do_test e_fkey-56.3 {
002540    execsql {
002541      UPDATE p SET a = 'xxx', b = 'xxx';
002542      SELECT * FROM p;
002543      SELECT * FROM c1;
002544      SELECT * FROM c2;
002545      SELECT * FROM c3;
002546    }
002547  } {xxx xxx 1 xxx 1 xxx 1 xxx}
002548  do_test e_fkey-56.4 {
002549    execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
002550  } [list                                                                     \
002551    {CREATE TABLE "p"(a REFERENCES "p", b, PRIMARY KEY(b))}                   \
002552    {CREATE TABLE c1(c, d REFERENCES "p" ON UPDATE CASCADE)}                  \
002553    {CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES "p" ON UPDATE CASCADE)}  \
002554    {CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES "p" ON UPDATE CASCADE)} \
002555  ]
002556  
002557  #-------------------------------------------------------------------------
002558  # Check that a DROP TABLE does an implicit DELETE FROM. Which does not
002559  # cause any triggers to fire, but does fire foreign key actions.
002560  #
002561  # EVIDENCE-OF: R-14208-23986 If foreign key constraints are enabled when
002562  # it is prepared, the DROP TABLE command performs an implicit DELETE to
002563  # remove all rows from the table before dropping it.
002564  #
002565  # EVIDENCE-OF: R-11078-03945 The implicit DELETE does not cause any SQL
002566  # triggers to fire, but may invoke foreign key actions or constraint
002567  # violations.
002568  #
002569  do_test e_fkey-57.1 {
002570    drop_all_tables
002571    execsql {
002572      CREATE TABLE p(a, b, PRIMARY KEY(a, b));
002573  
002574      CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET NULL);
002575      CREATE TABLE c2(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET DEFAULT);
002576      CREATE TABLE c3(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE CASCADE);
002577      CREATE TABLE c4(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE RESTRICT);
002578      CREATE TABLE c5(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE NO ACTION);
002579  
002580      CREATE TABLE c6(c, d, 
002581        FOREIGN KEY(c, d) REFERENCES p ON DELETE RESTRICT 
002582        DEFERRABLE INITIALLY DEFERRED
002583      );
002584      CREATE TABLE c7(c, d, 
002585        FOREIGN KEY(c, d) REFERENCES p ON DELETE NO ACTION
002586        DEFERRABLE INITIALLY DEFERRED
002587      );
002588  
002589      CREATE TABLE log(msg);
002590      CREATE TRIGGER tt AFTER DELETE ON p BEGIN
002591        INSERT INTO log VALUES('delete ' || old.rowid);
002592      END;
002593    }
002594  } {}
002595  
002596  do_test e_fkey-57.2 {
002597    execsql {
002598      INSERT INTO p VALUES('a', 'b');
002599      INSERT INTO c1 VALUES('a', 'b');
002600      INSERT INTO c2 VALUES('a', 'b');
002601      INSERT INTO c3 VALUES('a', 'b');
002602      BEGIN;
002603        DROP TABLE p;
002604        SELECT * FROM c1;
002605    }
002606  } {{} {}}
002607  do_test e_fkey-57.3 {
002608    execsql { SELECT * FROM c2 }
002609  } {{} {}}
002610  do_test e_fkey-57.4 {
002611    execsql { SELECT * FROM c3 }
002612  } {}
002613  do_test e_fkey-57.5 {
002614    execsql { SELECT * FROM log }
002615  } {}
002616  do_test e_fkey-57.6 {
002617    execsql ROLLBACK
002618  } {}
002619  do_test e_fkey-57.7 {
002620    execsql {
002621      BEGIN;
002622        DELETE FROM p;
002623        SELECT * FROM log;
002624      ROLLBACK;
002625    }
002626  } {{delete 1}}
002627  
002628  #-------------------------------------------------------------------------
002629  # If an IMMEDIATE foreign key fails as a result of a DROP TABLE, the
002630  # DROP TABLE command fails.
002631  #
002632  # EVIDENCE-OF: R-32768-47925 If an immediate foreign key constraint is
002633  # violated, the DROP TABLE statement fails and the table is not dropped.
002634  #
002635  do_test e_fkey-58.1 {
002636    execsql { 
002637      DELETE FROM c1;
002638      DELETE FROM c2;
002639      DELETE FROM c3;
002640    }
002641    execsql { INSERT INTO c5 VALUES('a', 'b') }
002642    catchsql { DROP TABLE p }
002643  } {1 {FOREIGN KEY constraint failed}}
002644  do_test e_fkey-58.2 {
002645    execsql { SELECT * FROM p }
002646  } {a b}
002647  do_test e_fkey-58.3 {
002648    catchsql {
002649      BEGIN;
002650        DROP TABLE p;
002651    }
002652  } {1 {FOREIGN KEY constraint failed}}
002653  do_test e_fkey-58.4 {
002654    execsql {
002655      SELECT * FROM p;
002656      SELECT * FROM c5;
002657      ROLLBACK;
002658    }
002659  } {a b a b}
002660  
002661  #-------------------------------------------------------------------------
002662  # If a DEFERRED foreign key fails as a result of a DROP TABLE, attempting
002663  # to commit the transaction fails unless the violation is fixed.
002664  #
002665  # EVIDENCE-OF: R-05903-08460 If a deferred foreign key constraint is
002666  # violated, then an error is reported when the user attempts to commit
002667  # the transaction if the foreign key constraint violations still exist
002668  # at that point.
002669  #
002670  do_test e_fkey-59.1 {
002671    execsql { 
002672      DELETE FROM c1 ; DELETE FROM c2 ; DELETE FROM c3 ;
002673      DELETE FROM c4 ; DELETE FROM c5 ; DELETE FROM c6 ;
002674      DELETE FROM c7 
002675    }
002676  } {}
002677  do_test e_fkey-59.2 {
002678    execsql { INSERT INTO c7 VALUES('a', 'b') }
002679    execsql {
002680      BEGIN;
002681        DROP TABLE p;
002682    }
002683  } {}
002684  do_test e_fkey-59.3 {
002685    catchsql COMMIT
002686  } {1 {FOREIGN KEY constraint failed}}
002687  do_test e_fkey-59.4 {
002688    execsql { CREATE TABLE p(a, b, PRIMARY KEY(a, b)) }
002689    catchsql COMMIT
002690  } {1 {FOREIGN KEY constraint failed}}
002691  do_test e_fkey-59.5 {
002692    execsql { INSERT INTO p VALUES('a', 'b') }
002693    execsql COMMIT
002694  } {}
002695  
002696  #-------------------------------------------------------------------------
002697  # Any "foreign key mismatch" errors encountered while running an implicit
002698  # "DELETE FROM tbl" are ignored.
002699  #
002700  # EVIDENCE-OF: R-57242-37005 Any "foreign key mismatch" errors
002701  # encountered as part of an implicit DELETE are ignored.
002702  #
002703  drop_all_tables
002704  do_test e_fkey-60.1 {
002705    execsql {
002706      PRAGMA foreign_keys = OFF;
002707  
002708      CREATE TABLE p(a PRIMARY KEY, b REFERENCES nosuchtable);
002709      CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES a);
002710      CREATE TABLE c2(c REFERENCES p(b), d);
002711      CREATE TABLE c3(c REFERENCES p ON DELETE SET NULL, d);
002712  
002713      INSERT INTO p VALUES(1, 2);
002714      INSERT INTO c1 VALUES(1, 2);
002715      INSERT INTO c2 VALUES(1, 2);
002716      INSERT INTO c3 VALUES(1, 2);
002717    }
002718  } {}
002719  do_test e_fkey-60.2 {
002720    execsql { PRAGMA foreign_keys = ON }
002721    catchsql { DELETE FROM p }
002722  } {1 {no such table: main.nosuchtable}}
002723  do_test e_fkey-60.3 {
002724    execsql {
002725      BEGIN;
002726        DROP TABLE p;
002727        SELECT * FROM c3;
002728      ROLLBACK;
002729    }
002730  } {{} 2}
002731  do_test e_fkey-60.4 {
002732    execsql { CREATE TABLE nosuchtable(x PRIMARY KEY) }
002733    catchsql { DELETE FROM p }
002734  } {1 {foreign key mismatch - "c2" referencing "p"}}
002735  do_test e_fkey-60.5 {
002736    execsql { DROP TABLE c1 }
002737    catchsql { DELETE FROM p }
002738  } {1 {foreign key mismatch - "c2" referencing "p"}}
002739  do_test e_fkey-60.6 {
002740    execsql { DROP TABLE c2 }
002741    execsql { DELETE FROM p }
002742  } {}
002743  
002744  #-------------------------------------------------------------------------
002745  # Test that the special behaviors of ALTER and DROP TABLE are only
002746  # activated when foreign keys are enabled. Special behaviors are:
002747  #
002748  #   1. ADD COLUMN not allowing a REFERENCES clause with a non-NULL 
002749  #      default value.
002750  #   2. Modifying foreign key definitions when a parent table is RENAMEd.
002751  #   3. Running an implicit DELETE FROM command as part of DROP TABLE.
002752  #
002753  # EVIDENCE-OF: R-54142-41346 The properties of the DROP TABLE and ALTER
002754  # TABLE commands described above only apply if foreign keys are enabled.
002755  #
002756  do_test e_fkey-61.1.1 {
002757    drop_all_tables
002758    execsql { CREATE TABLE t1(a, b) }
002759    catchsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 }
002760  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
002761  do_test e_fkey-61.1.2 {
002762    execsql { PRAGMA foreign_keys = OFF }
002763    execsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 }
002764    execsql { SELECT sql FROM sqlite_master WHERE name = 't1' }
002765  } {{CREATE TABLE t1(a, b, c DEFAULT 'xxx' REFERENCES t2)}}
002766  do_test e_fkey-61.1.3 {
002767    execsql { PRAGMA foreign_keys = ON }
002768  } {}
002769  
002770  do_test e_fkey-61.2.1 {
002771    drop_all_tables
002772    execsql {
002773      CREATE TABLE p(a UNIQUE);
002774      CREATE TABLE c(b REFERENCES p(a));
002775      BEGIN;
002776        ALTER TABLE p RENAME TO parent;
002777        SELECT sql FROM sqlite_master WHERE name = 'c';
002778      ROLLBACK;
002779    }
002780  } {{CREATE TABLE c(b REFERENCES "parent"(a))}}
002781  do_test e_fkey-61.2.2 {
002782    execsql {
002783      PRAGMA foreign_keys = OFF;
002784      ALTER TABLE p RENAME TO parent;
002785      SELECT sql FROM sqlite_master WHERE name = 'c';
002786    }
002787  } {{CREATE TABLE c(b REFERENCES p(a))}}
002788  do_test e_fkey-61.2.3 {
002789    execsql { PRAGMA foreign_keys = ON }
002790  } {}
002791  
002792  do_test e_fkey-61.3.1 {
002793    drop_all_tables
002794    execsql {
002795      CREATE TABLE p(a UNIQUE);
002796      CREATE TABLE c(b REFERENCES p(a) ON DELETE SET NULL);
002797      INSERT INTO p VALUES('x');
002798      INSERT INTO c VALUES('x');
002799      BEGIN;
002800        DROP TABLE p;
002801        SELECT * FROM c;
002802      ROLLBACK;
002803    }
002804  } {{}}
002805  do_test e_fkey-61.3.2 {
002806    execsql {
002807      PRAGMA foreign_keys = OFF;
002808      DROP TABLE p;
002809      SELECT * FROM c;
002810    }
002811  } {x}
002812  do_test e_fkey-61.3.3 {
002813    execsql { PRAGMA foreign_keys = ON }
002814  } {}
002815  
002816  ###########################################################################
002817  ### SECTION 6: Limits and Unsupported Features
002818  ###########################################################################
002819  
002820  #-------------------------------------------------------------------------
002821  # Test that MATCH clauses are parsed, but SQLite treats every foreign key
002822  # constraint as if it were "MATCH SIMPLE".
002823  #
002824  # EVIDENCE-OF: R-24728-13230 SQLite parses MATCH clauses (i.e. does not
002825  # report a syntax error if you specify one), but does not enforce them.
002826  #
002827  # EVIDENCE-OF: R-24450-46174 All foreign key constraints in SQLite are
002828  # handled as if MATCH SIMPLE were specified.
002829  #
002830  foreach zMatch [list SIMPLE PARTIAL FULL Simple parTIAL FuLL ] {
002831    drop_all_tables
002832    do_test e_fkey-62.$zMatch.1 {
002833      execsql "
002834        CREATE TABLE p(a, b, c, PRIMARY KEY(b, c));
002835        CREATE TABLE c(d, e, f, FOREIGN KEY(e, f) REFERENCES p MATCH $zMatch);
002836      "
002837    } {}
002838    do_test e_fkey-62.$zMatch.2 {
002839      execsql { INSERT INTO p VALUES(1, 2, 3)         }
002840  
002841      # MATCH SIMPLE behavior: Allow any child key that contains one or more
002842      # NULL value to be inserted. Non-NULL values do not have to map to any
002843      # parent key values, so long as at least one field of the child key is
002844      # NULL.
002845      execsql { INSERT INTO c VALUES('w', 2, 3)       }
002846      execsql { INSERT INTO c VALUES('x', 'x', NULL)  }
002847      execsql { INSERT INTO c VALUES('y', NULL, 'x')  }
002848      execsql { INSERT INTO c VALUES('z', NULL, NULL) }
002849  
002850      # Check that the FK is enforced properly if there are no NULL values 
002851      # in the child key columns.
002852      catchsql { INSERT INTO c VALUES('a', 2, 4) }
002853    } {1 {FOREIGN KEY constraint failed}}
002854  }
002855  
002856  #-------------------------------------------------------------------------
002857  # Test that SQLite does not support the SET CONSTRAINT statement. And
002858  # that it is possible to create both immediate and deferred constraints.
002859  #
002860  # EVIDENCE-OF: R-21599-16038 In SQLite, a foreign key constraint is
002861  # permanently marked as deferred or immediate when it is created.
002862  #
002863  drop_all_tables
002864  do_test e_fkey-62.1 {
002865    catchsql { SET CONSTRAINTS ALL IMMEDIATE }
002866  } {1 {near "SET": syntax error}}
002867  do_test e_fkey-62.2 {
002868    catchsql { SET CONSTRAINTS ALL DEFERRED }
002869  } {1 {near "SET": syntax error}}
002870  
002871  do_test e_fkey-62.3 {
002872    execsql {
002873      CREATE TABLE p(a, b, PRIMARY KEY(a, b));
002874      CREATE TABLE cd(c, d, 
002875        FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY DEFERRED);
002876      CREATE TABLE ci(c, d, 
002877        FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY IMMEDIATE);
002878      BEGIN;
002879    }
002880  } {}
002881  do_test e_fkey-62.4 {
002882    catchsql { INSERT INTO ci VALUES('x', 'y') }
002883  } {1 {FOREIGN KEY constraint failed}}
002884  do_test e_fkey-62.5 {
002885    catchsql { INSERT INTO cd VALUES('x', 'y') }
002886  } {0 {}}
002887  do_test e_fkey-62.6 {
002888    catchsql { COMMIT }
002889  } {1 {FOREIGN KEY constraint failed}}
002890  do_test e_fkey-62.7 {
002891    execsql { 
002892      DELETE FROM cd;
002893      COMMIT;
002894    }
002895  } {}
002896  
002897  #-------------------------------------------------------------------------
002898  # Test that the maximum recursion depth of foreign key action programs is
002899  # governed by the SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH
002900  # settings.
002901  #
002902  # EVIDENCE-OF: R-42264-30503 The SQLITE_MAX_TRIGGER_DEPTH and
002903  # SQLITE_LIMIT_TRIGGER_DEPTH settings determine the maximum allowable
002904  # depth of trigger program recursion. For the purposes of these limits,
002905  # foreign key actions are considered trigger programs.
002906  #
002907  proc test_on_delete_recursion {limit} {
002908    drop_all_tables
002909    execsql { 
002910      BEGIN;
002911      CREATE TABLE t0(a PRIMARY KEY, b);
002912      INSERT INTO t0 VALUES('x0', NULL);
002913    }
002914    for {set i 1} {$i <= $limit} {incr i} {
002915      execsql "
002916        CREATE TABLE t$i (
002917          a PRIMARY KEY, b REFERENCES t[expr $i-1] ON DELETE CASCADE
002918        );
002919        INSERT INTO t$i VALUES('x$i', 'x[expr $i-1]');
002920      "
002921    }
002922    execsql COMMIT
002923    catchsql "
002924      DELETE FROM t0;
002925      SELECT count(*) FROM t$limit;
002926    "
002927  }
002928  proc test_on_update_recursion {limit} {
002929    drop_all_tables
002930    execsql { 
002931      BEGIN;
002932      CREATE TABLE t0(a PRIMARY KEY);
002933      INSERT INTO t0 VALUES('xxx');
002934    }
002935    for {set i 1} {$i <= $limit} {incr i} {
002936      set j [expr $i-1]
002937  
002938      execsql "
002939        CREATE TABLE t$i (a PRIMARY KEY REFERENCES t$j ON UPDATE CASCADE);
002940        INSERT INTO t$i VALUES('xxx');
002941      "
002942    }
002943    execsql COMMIT
002944    catchsql "
002945      UPDATE t0 SET a = 'yyy';
002946      SELECT NOT (a='yyy') FROM t$limit;
002947    "
002948  }
002949  
002950  # If the current build was created using clang with the -fsanitize=address
002951  # switch, then the library uses considerably more stack space than usual.
002952  # So much more, that some of the following tests cause stack overflows
002953  # if they are run under this configuration.
002954  #
002955  if {[clang_sanitize_address]==0} {
002956    do_test e_fkey-63.1.1 {
002957      test_on_delete_recursion $SQLITE_MAX_TRIGGER_DEPTH
002958    } {0 0}
002959    do_test e_fkey-63.1.2 {
002960      test_on_delete_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1]
002961    } {1 {too many levels of trigger recursion}}
002962    do_test e_fkey-63.1.3 {
002963      sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5
002964        test_on_delete_recursion 5
002965    } {0 0}
002966    do_test e_fkey-63.1.4 {
002967      test_on_delete_recursion 6
002968    } {1 {too many levels of trigger recursion}}
002969    do_test e_fkey-63.1.5 {
002970      sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000
002971    } {5}
002972    do_test e_fkey-63.2.1 {
002973      test_on_update_recursion $SQLITE_MAX_TRIGGER_DEPTH
002974    } {0 0}
002975    do_test e_fkey-63.2.2 {
002976      test_on_update_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1]
002977    } {1 {too many levels of trigger recursion}}
002978    do_test e_fkey-63.2.3 {
002979      sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5
002980        test_on_update_recursion 5
002981    } {0 0}
002982    do_test e_fkey-63.2.4 {
002983      test_on_update_recursion 6
002984    } {1 {too many levels of trigger recursion}}
002985    do_test e_fkey-63.2.5 {
002986      sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000
002987    } {5}
002988  }
002989  
002990  #-------------------------------------------------------------------------
002991  # The setting of the recursive_triggers pragma does not affect foreign
002992  # key actions.
002993  #
002994  # EVIDENCE-OF: R-44355-00270 The PRAGMA recursive_triggers setting does
002995  # not affect the operation of foreign key actions.
002996  #
002997  foreach recursive_triggers_setting [list 0 1 ON OFF] {
002998    drop_all_tables
002999    execsql "PRAGMA recursive_triggers = $recursive_triggers_setting"
003000  
003001    do_test e_fkey-64.$recursive_triggers_setting.1 {
003002      execsql {
003003        CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1 ON DELETE CASCADE);
003004        INSERT INTO t1 VALUES(1, NULL);
003005        INSERT INTO t1 VALUES(2, 1);
003006        INSERT INTO t1 VALUES(3, 2);
003007        INSERT INTO t1 VALUES(4, 3);
003008        INSERT INTO t1 VALUES(5, 4);
003009        SELECT count(*) FROM t1;
003010      }
003011    } {5}
003012    do_test e_fkey-64.$recursive_triggers_setting.2 {
003013      execsql { SELECT count(*) FROM t1 WHERE a = 1 }
003014    } {1}
003015    do_test e_fkey-64.$recursive_triggers_setting.3 {
003016      execsql { 
003017        DELETE FROM t1 WHERE a = 1;
003018        SELECT count(*) FROM t1;
003019      }
003020    } {0}
003021  }
003022  
003023  finish_test