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