/ Check-in [4b489ecb]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Change many comments in e_fkey.test to include the full text of the corresponding statement in foreignkeys.html.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4b489ecb250ea0e80d5bf8806f07259e1107f8ad
User & Date: dan 2009-12-21 08:53:18
Context
2009-12-22
00:29
Add a pragma to disable the query flattener - for use during testing. check-in: 1d8550e5 user: drh tags: trunk
2009-12-21
08:53
Change many comments in e_fkey.test to include the full text of the corresponding statement in foreignkeys.html. check-in: 4b489ecb user: dan tags: trunk
2009-12-20
15:00
Fix a problem in FTS3 phrase queries. Add tests to check that it really is fixed. check-in: 956de051 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_fkey.test.

    95     95   #-------------------------------------------------------------------------
    96     96   # Test the effects of defining OMIT_FOREIGN_KEY.
    97     97   #
    98     98   # EVIDENCE-OF: R-58428-36660 If OMIT_FOREIGN_KEY is defined, then
    99     99   # foreign key definitions cannot even be parsed (attempting to specify a
   100    100   # foreign key definition is a syntax error).
   101    101   #
   102         -# /* EV: R-58428-36660 */
   103         -#
   104    102   # Specifically, test that foreign key constraints cannot even be parsed 
   105    103   # in such a build.
   106    104   #
   107    105   reset_db
   108    106   ifcapable !foreignkey {
   109    107     do_test e_fkey-3.1 {
   110    108       execsql { CREATE TABLE p(i PRIMARY KEY) }
................................................................................
   325    323     execsql { 
   326    324       UPDATE track SET trackartist = NULL WHERE trackid = 1;
   327    325       DELETE FROM artist WHERE artistid = 5;
   328    326     }
   329    327   } {}
   330    328   
   331    329   #-------------------------------------------------------------------------
   332         -# /* EV: R-52486-21352 */
   333         -#
   334    330   # Test that the following is true fo all rows in the track table:
   335    331   #
   336    332   #   trackartist IS NULL OR 
   337    333   #   EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
   338    334   #
   339    335   # EVIDENCE-OF: R-52486-21352 Expressed in SQL, this means that for every
   340    336   # row in the track table, the following expression evaluates to true:
................................................................................
   384    380     set t   [expr int(rand()*50)]
   385    381     set sql [subst [lindex $Template [expr int(rand()*6)]]]
   386    382   
   387    383     test_r52486_21352 $i $sql
   388    384   }
   389    385   
   390    386   #-------------------------------------------------------------------------
   391         -# /* EV: R-42412-59321 */
   392         -#
   393    387   # Check that a NOT NULL constraint can be added to the example schema
   394    388   # to prohibit NULL child keys from being inserted.
   395    389   #
   396    390   # EVIDENCE-OF: R-42412-59321 Tip: If the application requires a stricter
   397    391   # relationship between artist and track, where NULL values are not
   398    392   # permitted in the trackartist column, simply add the appropriate "NOT
   399    393   # NULL" constraint to the schema.
................................................................................
   878    872       do_test e_fkey-22.$fk.[incr i] {
   879    873         catchsql $sql
   880    874       } [list 1 $error]
   881    875     }
   882    876   }
   883    877   
   884    878   #-------------------------------------------------------------------------
   885         -# /* EV: R-47109-40581 */
   886         -#
   887    879   # Test that a REFERENCING clause that does not specify parent key columns
   888    880   # implicitly maps to the primary key of the parent table.
          881  +#
          882  +# EVIDENCE-OF: R-47109-40581 Attaching a "REFERENCES
          883  +# <parent-table>" clause to a column definition creates a foreign
          884  +# key constraint that maps the column to the primary key of
          885  +# <parent-table>.
   889    886   # 
   890    887   do_test e_fkey-23.1 {
   891    888     execsql {
   892    889       CREATE TABLE p1(a, b, PRIMARY KEY(a, b));
   893    890       CREATE TABLE p2(a, b PRIMARY KEY);
   894    891       CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p1);
   895    892       CREATE TABLE c2(a, b REFERENCES p2);
................................................................................
   911    908   #-------------------------------------------------------------------------
   912    909   # Test that an index on on the child key columns of an FK constraint
   913    910   # is optional.
   914    911   #
   915    912   # EVIDENCE-OF: R-15417-28014 Indices are not required for child key
   916    913   # columns
   917    914   #
   918         -# /* EV: R-15741-50893 */
   919         -#
   920    915   # Also test that if an index is created on the child key columns, it does
   921    916   # not make a difference whether or not it is a UNIQUE index.
          917  +#
          918  +# EVIDENCE-OF: R-15741-50893 The child key index does not have to be
          919  +# (and usually will not be) a UNIQUE index.
   922    920   #
   923    921   drop_all_tables
   924    922   do_test e_fkey-24.1 {
   925    923     execsql {
   926    924       CREATE TABLE parent(x, y, UNIQUE(y, x));
   927    925       CREATE TABLE c1(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
   928    926       CREATE TABLE c2(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
................................................................................
  1016   1014   #
  1017   1015   # Test that when a row is deleted from the parent table of an FK 
  1018   1016   # constraint, the child table is queried for orphaned rows. The
  1019   1017   # query is equivalent to:
  1020   1018   #
  1021   1019   #   SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
  1022   1020   #
  1023         -# /* EV: R-61616-46700 */
  1024         -#
  1025   1021   # Also test that when a row is inserted into the parent table, or when the 
  1026   1022   # parent key values of an existing row are modified, a query equivalent
  1027   1023   # to the following is planned. In some cases it is not executed, but it
  1028   1024   # is always planned.
  1029   1025   #
  1030   1026   #   SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
  1031   1027   #
................................................................................
  1068   1064     do_test e_fkey-26.$tn.1 { eqp "DELETE FROM parent WHERE 1" } $delete
  1069   1065     do_test e_fkey-26.$tn.2 { eqp "UPDATE parent set x=?, y=?" } $update
  1070   1066   
  1071   1067     execsql {DROP TABLE child}
  1072   1068   }
  1073   1069   
  1074   1070   #-------------------------------------------------------------------------
  1075         -# /* EV: R-14553-34013 */
         1071  +# EVIDENCE-OF: R-14553-34013
  1076   1072   #
  1077   1073   # Test the example schema at the end of section 3. Also test that is
  1078   1074   # is "efficient". In this case "efficient" means that foreign key
  1079   1075   # related operations on the parent table do not provoke linear scans.
  1080   1076   #
  1081   1077   drop_all_tables
  1082   1078   do_test e_fkey-27.1 {
................................................................................
  1112   1108   
  1113   1109   
  1114   1110   ###########################################################################
  1115   1111   ### SECTION 4.1: Composite Foreign Key Constraints
  1116   1112   ###########################################################################
  1117   1113   
  1118   1114   #-------------------------------------------------------------------------
  1119         -# /* EV: R-41062-34431 */
  1120         -#
  1121   1115   # Check that parent and child keys must have the same number of columns.
         1116  +#
         1117  +# EVIDENCE-OF: R-41062-34431 Parent and child keys must have the same
         1118  +# cardinality.
  1122   1119   #
  1123   1120   foreach {tn sql err} {
  1124   1121     1 "CREATE TABLE c(jj REFERENCES p(x, y))" 
  1125   1122       {foreign key on jj should reference only one column of table p}
  1126   1123   
  1127   1124     2 "CREATE TABLE c(jj REFERENCES p())" {near ")": syntax error}
  1128   1125   
................................................................................
  1159   1156       CREATE TABLE c(a REFERENCES p);
  1160   1157     }
  1161   1158     catchsql {DELETE FROM p}
  1162   1159   } {1 {foreign key mismatch}}
  1163   1160   
  1164   1161   
  1165   1162   #-------------------------------------------------------------------------
  1166         -# /* EV: R-24676-09859 */
         1163  +# EVIDENCE-OF: R-24676-09859
  1167   1164   #
  1168   1165   # Test the example schema in the "Composite Foreign Key Constraints" 
  1169   1166   # section.
  1170   1167   #
  1171   1168   do_test e_fkey-29.1 {
  1172   1169     execsql {
  1173   1170       CREATE TABLE album(
................................................................................
  1198   1195     catchsql {
  1199   1196       INSERT INTO song VALUES(2, 'Elvis Presley', 'Elvis Is Back!', 'Fever');
  1200   1197     }
  1201   1198   } {1 {foreign key constraint failed}}
  1202   1199   
  1203   1200   
  1204   1201   #-------------------------------------------------------------------------
  1205         -# /* EV: R-33626-48418 */
  1206         -#
  1207         -# Check that if any of the child key columns in the above schema are NULL,
  1208         -# there is no requirement for a corresponding parent key.
         1202  +# EVIDENCE-OF: R-33626-48418 In SQLite, if any of the child key columns
         1203  +# (in this case songartist and songalbum) are NULL, then there is no
         1204  +# requirement for a corresponding row in the parent table.
  1209   1205   #
  1210   1206   do_test e_fkey-30.1 {
  1211   1207     execsql {
  1212   1208       INSERT INTO song VALUES(2, 'Elvis Presley', NULL, 'Fever');
  1213   1209       INSERT INTO song VALUES(3, NULL, 'Elvis Is Back', 'Soldier Boy');
  1214   1210     }
  1215   1211   } {}
  1216   1212   
  1217   1213   ###########################################################################
  1218   1214   ### SECTION 4.2: Deferred Foreign Key Constraints
  1219   1215   ###########################################################################
  1220   1216   
  1221   1217   #-------------------------------------------------------------------------
  1222         -# Note: R-35290-16460 is tested below.
  1223         -#
  1224         -# TODO: R-30323-21917
  1225         -
  1226         -#-------------------------------------------------------------------------
  1227         -# /* EV: R-09323-30470 */
  1228         -#
  1229   1218   # Test that if a statement violates an immediate FK constraint, and the
  1230   1219   # database does not satisfy the FK constraint once all effects of the
  1231   1220   # statement have been applied, an error is reported and the effects of
  1232   1221   # the statement rolled back.
         1222  +#
         1223  +# EVIDENCE-OF: R-09323-30470 If a statement modifies the contents of the
         1224  +# database so that an immediate foreign key constraint is in violation
         1225  +# at the conclusion the statement, an exception is thrown and the
         1226  +# effects of the statement are reverted.
  1233   1227   #
  1234   1228   drop_all_tables
  1235   1229   do_test e_fkey-31.1 {
  1236   1230     execsql {
  1237   1231       CREATE TABLE king(a, b, PRIMARY KEY(a));
  1238   1232       CREATE TABLE prince(c REFERENCES king, d);
  1239   1233     }
................................................................................
  1272   1266     execsql {
  1273   1267       COMMIT;
  1274   1268       SELECT * FROM king;
  1275   1269     }
  1276   1270   } {1 {} 2 {}}
  1277   1271   
  1278   1272   #-------------------------------------------------------------------------
  1279         -# /* EV: R-49178-21358 */
  1280         -# /* EV: R-39692-12488 */
  1281         -# /* EV: R-55147-47664 */
  1282         -# /* EV: R-29604-30395 */
  1283         -#
  1284   1273   # Test that if a deferred constraint is violated within a transaction,
  1285   1274   # nothing happens immediately and the database is allowed to persist
  1286   1275   # in a state that does not satisfy the FK constraint. However attempts
  1287   1276   # to COMMIT the transaction fail until the FK constraint is satisfied.
         1277  +#
         1278  +# EVIDENCE-OF: R-49178-21358 By contrast, if a statement modifies the
         1279  +# contents of the database such that a deferred foreign key constraint
         1280  +# is violated, the violation is not reported immediately.
         1281  +#
         1282  +# EVIDENCE-OF: R-39692-12488 Deferred foreign key constraints are not
         1283  +# checked until the transaction tries to COMMIT.
         1284  +#
         1285  +# EVIDENCE-OF: R-55147-47664 For as long as the user has an open
         1286  +# transaction, the database is allowed to exist in a state that violates
         1287  +# any number of deferred foreign key constraints.
         1288  +#
         1289  +# EVIDENCE-OF: R-29604-30395 However, COMMIT will fail as long as
         1290  +# foreign key constraints remain in violation.
  1288   1291   #
  1289   1292   proc test_efkey_34 {tn isError sql} {
  1290   1293     do_test e_fkey-32.$tn "
  1291   1294       catchsql {$sql}
  1292   1295     " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError]
  1293   1296   }
  1294   1297   drop_all_tables
................................................................................
  1303   1306   test_efkey_34  5 1 "COMMIT"
  1304   1307   test_efkey_34  6 0   "INSERT INTO ll VALUES(10)"
  1305   1308   test_efkey_34  7 1 "COMMIT"
  1306   1309   test_efkey_34  8 0   "INSERT INTO ll VALUES(5)"
  1307   1310   test_efkey_34  9 0 "COMMIT"
  1308   1311   
  1309   1312   #-------------------------------------------------------------------------
  1310         -# /* EV: R-56844-61705 */
  1311         -#
  1312   1313   # When not running inside a transaction, a deferred constraint is similar
  1313   1314   # to an immediate constraint (violations are reported immediately).
         1315  +#
         1316  +# EVIDENCE-OF: R-56844-61705 If the current statement is not inside an
         1317  +# explicit transaction (a BEGIN/COMMIT/ROLLBACK block), then an implicit
         1318  +# transaction is committed as soon as the statement has finished
         1319  +# executing. In this case deferred constraints behave the same as
         1320  +# immediate constraints.
  1314   1321   #
  1315   1322   drop_all_tables
  1316   1323   proc test_efkey_35 {tn isError sql} {
  1317   1324     do_test e_fkey-33.$tn "
  1318   1325       catchsql {$sql}
  1319   1326     " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError]
  1320   1327   }
................................................................................
  1329   1336   } {}
  1330   1337   test_efkey_35 2 1 "INSERT INTO child  VALUES('x', 'y')"
  1331   1338   test_efkey_35 3 0 "INSERT INTO parent VALUES('x', 'y')"
  1332   1339   test_efkey_35 4 0 "INSERT INTO child  VALUES('x', 'y')"
  1333   1340   
  1334   1341   
  1335   1342   #-------------------------------------------------------------------------
  1336         -# /* EV: R-12782-61841 */
         1343  +# EVIDENCE-OF: R-12782-61841
  1337   1344   #
  1338   1345   # Test that an FK constraint is made deferred by adding the following
  1339   1346   # to the definition:
  1340   1347   #
  1341   1348   #   DEFERRABLE INITIALLY DEFERRED
  1342   1349   #
  1343         -# /* EV: R-09005-28791 */
         1350  +# EVIDENCE-OF: R-09005-28791
  1344   1351   #
  1345   1352   # Also test that adding any of the following to a foreign key definition 
  1346   1353   # makes the constraint IMMEDIATE:
  1347   1354   #
  1348   1355   #   NOT DEFERRABLE INITIALLY DEFERRED
  1349   1356   #   NOT DEFERRABLE INITIALLY IMMEDIATE
  1350   1357   #   NOT DEFERRABLE
  1351   1358   #   DEFERRABLE INITIALLY IMMEDIATE
  1352   1359   #   DEFERRABLE
  1353   1360   #
  1354         -# /* EV: R-35290-16460 */
  1355         -#
  1356   1361   # Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT
  1357   1362   # DEFERRABLE clause).
  1358   1363   #
  1359         -# /* EV: R-30323-21917 */  FKs are either IMMEDIATE or DEFERRED.
         1364  +# EVIDENCE-OF: R-35290-16460 Foreign key constraints are immediate by
         1365  +# default.
         1366  +#
         1367  +# EVIDENCE-OF: R-30323-21917 Each foreign key constraint in SQLite is
         1368  +# classified as either immediate or deferred.
  1360   1369   #
  1361   1370   drop_all_tables
  1362   1371   do_test e_fkey-34.1 {
  1363   1372     execsql {
  1364   1373       CREATE TABLE parent(x, y, z, PRIMARY KEY(x,y,z));
  1365   1374       CREATE TABLE c1(a, b, c,
  1366   1375         FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY DEFERRED
................................................................................
  1449   1458   test_efkey_29 31 "UPDATE c5 SET a = 10"                    1
  1450   1459   test_efkey_29 31 "UPDATE c6 SET a = 10"                    1
  1451   1460   test_efkey_29 31 "UPDATE c7 SET a = 10"                    0
  1452   1461   test_efkey_29 32 "COMMIT"                                  1
  1453   1462   test_efkey_29 33 "ROLLBACK"                                0
  1454   1463   
  1455   1464   #-------------------------------------------------------------------------
  1456         -# /* EV: R-35043-01546 */
         1465  +# EVIDENCE-OF: R-35043-01546
  1457   1466   #
  1458   1467   # Test an example from foreignkeys.html dealing with a deferred foreign 
  1459   1468   # key constraint.
  1460   1469   #
  1461   1470   do_test e_fkey-35.1 {
  1462   1471     drop_all_tables
  1463   1472     execsql {
................................................................................
  1483   1492     execsql {
  1484   1493       INSERT INTO artist VALUES(5, 'Bing Crosby');
  1485   1494       COMMIT;
  1486   1495     }
  1487   1496   } {}
  1488   1497   
  1489   1498   #-------------------------------------------------------------------------
  1490         -# /* EV: R-07223-48323 */
  1491         -#
  1492   1499   # Verify that a nested savepoint may be released without satisfying 
  1493   1500   # deferred foreign key constraints.
         1501  +#
         1502  +# EVIDENCE-OF: R-07223-48323 A nested savepoint transaction may be
         1503  +# RELEASEd while the database is in a state that does not satisfy a
         1504  +# deferred foreign key constraint.
  1494   1505   #
  1495   1506   drop_all_tables
  1496   1507   do_test e_fkey-36.1 {
  1497   1508     execsql {
  1498   1509       CREATE TABLE t1(a PRIMARY KEY,
  1499   1510         b REFERENCES t1 DEFERRABLE INITIALLY DEFERRED
  1500   1511       );
................................................................................
  1519   1530       UPDATE t1 SET a = 5 WHERE a = 4;
  1520   1531       COMMIT;
  1521   1532     }
  1522   1533   } {}
  1523   1534   
  1524   1535   
  1525   1536   #-------------------------------------------------------------------------
  1526         -# /* EV: R-44295-13823 */
  1527         -#
  1528   1537   # Check that a transaction savepoint (an outermost savepoint opened when
  1529   1538   # the database was in auto-commit mode) cannot be released without
  1530   1539   # satisfying deferred foreign key constraints. It may be rolled back.
         1540  +#
         1541  +# EVIDENCE-OF: R-44295-13823 A transaction savepoint (a non-nested
         1542  +# savepoint that was opened while there was not currently an open
         1543  +# transaction), on the other hand, is subject to the same restrictions
         1544  +# as a COMMIT - attempting to RELEASE it while the database is in such a
         1545  +# state will fail.
  1531   1546   #
  1532   1547   do_test e_fkey-37.1 {
  1533   1548     execsql {
  1534   1549       SAVEPOINT one;
  1535   1550         SAVEPOINT two;
  1536   1551           INSERT INTO t1 VALUES(6, 7);
  1537   1552         RELEASE two;
................................................................................
  1558   1573     catchsql {RELEASE one}
  1559   1574   } {1 {foreign key constraint failed}}
  1560   1575   do_test e_fkey-37.6 {
  1561   1576     execsql {ROLLBACK TO one ; RELEASE one}
  1562   1577   } {}
  1563   1578   
  1564   1579   #-------------------------------------------------------------------------
  1565         -# /* EV: R-37736-42616 */
  1566         -#
  1567   1580   # Test that if a COMMIT operation fails due to deferred foreign key 
  1568   1581   # constraints, any nested savepoints remain open.
         1582  +#
         1583  +# EVIDENCE-OF: R-37736-42616 If a COMMIT statement (or the RELEASE of a
         1584  +# transaction SAVEPOINT) fails because the database is currently in a
         1585  +# state that violates a deferred foreign key constraint and there are
         1586  +# currently nested savepoints, the nested savepoints remain open.
  1569   1587   #
  1570   1588   do_test e_fkey-38.1 {
  1571   1589     execsql {
  1572   1590       DELETE FROM t1 WHERE a>3;
  1573   1591       SELECT * FROM t1;
  1574   1592     }
  1575   1593   } {1 1 2 2 3 3}
................................................................................
  1619   1637   } {1 1 2 2 3 3 4 4 5 5}
  1620   1638   
  1621   1639   ###########################################################################
  1622   1640   ### SECTION 4.3: ON DELETE and ON UPDATE Actions
  1623   1641   ###########################################################################
  1624   1642   
  1625   1643   #-------------------------------------------------------------------------
  1626         -# /* EV: R-48270-44282 */
  1627         -#
  1628   1644   # Test that configured ON DELETE and ON UPDATE actions take place when
  1629   1645   # deleting or modifying rows of the parent table, respectively.
  1630   1646   #
  1631         -# /* EV: R-48124-63225 */
         1647  +# EVIDENCE-OF: R-48270-44282 Foreign key ON DELETE and ON UPDATE clauses
         1648  +# are used to configure actions that take place when deleting rows from
         1649  +# the parent table (ON DELETE), or modifying the parent key values of
         1650  +# existing rows (ON UPDATE).
  1632   1651   #
  1633   1652   # Test that a single FK constraint may have different actions configured
  1634   1653   # for ON DELETE and ON UPDATE.
         1654  +#
         1655  +# EVIDENCE-OF: R-48124-63225 A single foreign key constraint may have
         1656  +# different actions configured for ON DELETE and ON UPDATE.
  1635   1657   #
  1636   1658   do_test e_fkey-39.1 {
  1637   1659     execsql {
  1638   1660       CREATE TABLE p(a, b PRIMARY KEY, c);
  1639   1661       CREATE TABLE c1(d, e, f DEFAULT 'k0' REFERENCES p 
  1640   1662         ON UPDATE SET DEFAULT
  1641   1663         ON DELETE SET NULL
................................................................................
  1668   1690       CREATE UNIQUE INDEX pi ON p(c);
  1669   1691       REPLACE INTO p VALUES(5, 'k5', 'III');
  1670   1692       SELECT * FROM c1;
  1671   1693     }
  1672   1694   } {1 xx k0 2 xx {} 3 xx {}}
  1673   1695   
  1674   1696   #-------------------------------------------------------------------------
  1675         -# /* EV: R-33326-45252 */
  1676         -#
  1677   1697   # Each foreign key in the system has an ON UPDATE and ON DELETE action,
  1678   1698   # either "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".
  1679   1699   #
  1680         -# /* EV: R-19803-45884 */
         1700  +# EVIDENCE-OF: R-33326-45252 The ON DELETE and ON UPDATE action
         1701  +# associated with each foreign key in an SQLite database is one of "NO
         1702  +# ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".
  1681   1703   #
  1682   1704   # If none is specified explicitly, "NO ACTION" is the default.
         1705  +#
         1706  +# EVIDENCE-OF: R-19803-45884 If an action is not explicitly specified,
         1707  +# it defaults to "NO ACTION".
  1683   1708   # 
  1684   1709   drop_all_tables
  1685   1710   do_test e_fkey-40.1 {
  1686   1711     execsql {
  1687   1712       CREATE TABLE parent(x PRIMARY KEY, y);
  1688   1713       CREATE TABLE child1(a, 
  1689   1714         b REFERENCES parent ON UPDATE NO ACTION ON DELETE RESTRICT
................................................................................
  1716   1741     8 child7 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
  1717   1742     9 child8 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
  1718   1743   } {
  1719   1744     do_test e_fkey-40.$tn { execsql "PRAGMA foreign_key_list($zTab)" } $lRes
  1720   1745   }
  1721   1746   
  1722   1747   #-------------------------------------------------------------------------
  1723         -# /* EV: R-19971-54976 */
  1724         -#
  1725   1748   # Test that "NO ACTION" means that nothing happens to a child row when
  1726   1749   # it's parent row is updated or deleted.
         1750  +#
         1751  +# EVIDENCE-OF: R-19971-54976 Configuring "NO ACTION" means just that:
         1752  +# when a parent key is modified or deleted from the database, no special
         1753  +# action is taken.
  1727   1754   #
  1728   1755   drop_all_tables
  1729   1756   do_test e_fkey-41.1 {
  1730   1757     execsql {
  1731   1758       CREATE TABLE parent(p1, p2, PRIMARY KEY(p1, p2));
  1732   1759       CREATE TABLE child(c1, c2, 
  1733   1760         FOREIGN KEY(c1, c2) REFERENCES parent
................................................................................
  1753   1780     catchsql COMMIT
  1754   1781   } {1 {foreign key constraint failed}}
  1755   1782   do_test e_fkey-41.4 {
  1756   1783     execsql ROLLBACK
  1757   1784   } {}
  1758   1785   
  1759   1786   #-------------------------------------------------------------------------
  1760         -# /* EV: R-04272-38653 */
  1761         -#
  1762   1787   # Test that "RESTRICT" means the application is prohibited from deleting
  1763   1788   # or updating a parent table row when there exists one or more child keys
  1764   1789   # mapped to it.
         1790  +#
         1791  +# EVIDENCE-OF: R-04272-38653 The "RESTRICT" action means that the
         1792  +# application is prohibited from deleting (for ON DELETE RESTRICT) or
         1793  +# modifying (for ON UPDATE RESTRICT) a parent key when there exists one
         1794  +# or more child keys mapped to it.
  1765   1795   #
  1766   1796   drop_all_tables
  1767   1797   do_test e_fkey-41.1 {
  1768   1798     execsql {
  1769   1799       CREATE TABLE parent(p1, p2);
  1770   1800       CREATE UNIQUE INDEX parent_i ON parent(p1, p2);
  1771   1801       CREATE TABLE child1(c1, c2, 
................................................................................
  1788   1818     catchsql { DELETE FROM parent WHERE p1 = 'a' }
  1789   1819   } {1 {foreign key constraint failed}}
  1790   1820   do_test e_fkey-41.4 {
  1791   1821     catchsql { UPDATE parent SET p2 = 'e' WHERE p1 = 'c' }
  1792   1822   } {1 {foreign key constraint failed}}
  1793   1823   
  1794   1824   #-------------------------------------------------------------------------
  1795         -# /* EV: R-37997-42187 */
  1796         -# 
  1797   1825   # Test that RESTRICT is slightly different from NO ACTION for IMMEDIATE
  1798   1826   # constraints, in that it is enforced immediately, not at the end of the 
  1799   1827   # statement.
         1828  +#
         1829  +# EVIDENCE-OF: R-37997-42187 The difference between the effect of a
         1830  +# RESTRICT action and normal foreign key constraint enforcement is that
         1831  +# the RESTRICT action processing happens as soon as the field is updated
         1832  +# - not at the end of the current statement as it would with an
         1833  +# immediate constraint, or at the end of the current transaction as it
         1834  +# would with a deferred constraint.
  1800   1835   #
  1801   1836   drop_all_tables
  1802   1837   do_test e_fkey-42.1 {
  1803   1838     execsql {
  1804   1839       CREATE TABLE parent(x PRIMARY KEY);
  1805   1840       CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT);
  1806   1841       CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION);
................................................................................
  1874   1909     execsql { 
  1875   1910       REPLACE INTO parent VALUES('key2');
  1876   1911       SELECT * FROM child2;
  1877   1912     }
  1878   1913   } {key2}
  1879   1914   
  1880   1915   #-------------------------------------------------------------------------
  1881         -# /* EV: R-24179-60523 */
  1882         -# 
  1883   1916   # Test that RESTRICT is enforced immediately, even for a DEFERRED constraint.
         1917  +#
         1918  +# EVIDENCE-OF: R-24179-60523 Even if the foreign key constraint it is
         1919  +# attached to is deferred, configuring a RESTRICT action causes SQLite
         1920  +# to return an error immediately if a parent key with dependent child
         1921  +# keys is deleted or modified.
  1884   1922   #
  1885   1923   drop_all_tables
  1886   1924   do_test e_fkey-43.1 {
  1887   1925     execsql {
  1888   1926       CREATE TABLE parent(x PRIMARY KEY);
  1889   1927       CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT
  1890   1928         DEFERRABLE INITIALLY DEFERRED
................................................................................
  1947   1985     execsql {
  1948   1986       UPDATE child2 SET c = NULL;
  1949   1987       COMMIT;
  1950   1988     }
  1951   1989   } {}
  1952   1990   
  1953   1991   #-------------------------------------------------------------------------
  1954         -# /* EV: R-03353-05327 */
  1955         -#
  1956   1992   # Test SET NULL actions.
         1993  +#
         1994  +# EVIDENCE-OF: R-03353-05327 If the configured action is "SET NULL",
         1995  +# then when a parent key is deleted (for ON DELETE SET NULL) or modified
         1996  +# (for ON UPDATE SET NULL), the child key columns of all rows in the
         1997  +# child table that mapped to the parent key are set to contain SQL NULL
         1998  +# values.
  1957   1999   #
  1958   2000   drop_all_tables
  1959   2001   do_test e_fkey-44.1 {
  1960   2002     execsql {
  1961   2003       CREATE TABLE pA(x PRIMARY KEY);
  1962   2004       CREATE TABLE cA(c REFERENCES pA ON DELETE SET NULL);
  1963   2005       CREATE TABLE cB(c REFERENCES pA ON UPDATE SET NULL);
................................................................................
  1986   2028     }
  1987   2029   } {X'8765'}
  1988   2030   do_test e_fkey-44.5 {
  1989   2031     execsql { SELECT quote(c) FROM cB }
  1990   2032   } {NULL}
  1991   2033   
  1992   2034   #-------------------------------------------------------------------------
  1993         -# /* EV: R-43054-54832 */
  1994         -#
  1995   2035   # Test SET DEFAULT actions.
         2036  +#
         2037  +# EVIDENCE-OF: R-43054-54832 The "SET DEFAULT" actions are similar to
         2038  +# "SET NULL", except that each of the child key columns is set to
         2039  +# contain the columns default value instead of NULL.
  1996   2040   #
  1997   2041   drop_all_tables
  1998   2042   do_test e_fkey-45.1 {
  1999   2043     execsql {
  2000   2044       CREATE TABLE pA(x PRIMARY KEY);
  2001   2045       CREATE TABLE cA(c DEFAULT X'0000' REFERENCES pA ON DELETE SET DEFAULT);
  2002   2046       CREATE TABLE cB(c DEFAULT X'9999' REFERENCES pA ON UPDATE SET DEFAULT);
................................................................................
  2026   2070     }
  2027   2071   } {X'0000' X'9999' X'8765'}
  2028   2072   do_test e_fkey-45.5 {
  2029   2073     execsql { SELECT quote(c) FROM cB }
  2030   2074   } {X'9999'}
  2031   2075   
  2032   2076   #-------------------------------------------------------------------------
  2033         -# /* EV: R-61376-57267 */
  2034         -# /* EV: R-61809-62207 */
  2035         -#
  2036   2077   # Test ON DELETE CASCADE actions.
         2078  +#
         2079  +# EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or
         2080  +# update operation on the parent key to each dependent child key.
         2081  +#
         2082  +# EVIDENCE-OF: R-61809-62207 For an "ON DELETE CASCADE" action, this
         2083  +# means that each row in the child table that was associated with the
         2084  +# deleted parent row is also deleted.
  2037   2085   #
  2038   2086   drop_all_tables
  2039   2087   do_test e_fkey-46.1 {
  2040   2088     execsql {
  2041   2089       CREATE TABLE p1(a, b UNIQUE);
  2042   2090       CREATE TABLE c1(c REFERENCES p1(b) ON DELETE CASCADE, d);
  2043   2091       INSERT INTO p1 VALUES(NULL, NULL);
................................................................................
  2063   2111   } {{} {}}
  2064   2112   do_test e_fkey-46.4 {
  2065   2113     execsql { SELECT * FROM p1 }
  2066   2114   } {}
  2067   2115   
  2068   2116   
  2069   2117   #-------------------------------------------------------------------------
  2070         -# /* EV: R-61376-57267 */
  2071         -# /* EV: R-13877-64542 */
  2072         -#
  2073   2118   # Test ON UPDATE CASCADE actions.
         2119  +#
         2120  +# EVIDENCE-OF: R-13877-64542 For an "ON UPDATE CASCADE" action, it means
         2121  +# that the values stored in each dependent child key are modified to
         2122  +# match the new parent key values.
         2123  +#
         2124  +# EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or
         2125  +# update operation on the parent key to each dependent child key.
  2074   2126   #
  2075   2127   drop_all_tables
  2076   2128   do_test e_fkey-47.1 {
  2077   2129     execsql {
  2078   2130       CREATE TABLE p1(a, b UNIQUE);
  2079   2131       CREATE TABLE c1(c REFERENCES p1(b) ON UPDATE CASCADE, d);
  2080   2132       INSERT INTO p1 VALUES(NULL, NULL);
................................................................................
  2105   2157     }
  2106   2158   } {{} {} 4 11 5 10}
  2107   2159   do_test e_fkey-46.5 {
  2108   2160     execsql { SELECT * FROM p1 }
  2109   2161   } {{} 6 4 11 5 10}
  2110   2162   
  2111   2163   #-------------------------------------------------------------------------
  2112         -# /* EV: R-51329-33438 */
         2164  +# EVIDENCE-OF: R-51329-33438
  2113   2165   #
  2114   2166   # Test an example from the "ON DELETE and ON UPDATE Actions" section 
  2115   2167   # of foreignkeys.html.
  2116   2168   #
  2117   2169   drop_all_tables
  2118   2170   do_test e_fkey-48.1 {
  2119   2171     execsql {
................................................................................
  2144   2196   } {2 {Frank Sinatra} 100 {Dean Martin}}
  2145   2197   do_test e_fkey-48.4 {
  2146   2198     execsql { SELECT * FROM track }
  2147   2199   } {11 {That's Amore} 100 12 {Christmas Blues} 100 13 {My Way} 2}
  2148   2200   
  2149   2201   
  2150   2202   #-------------------------------------------------------------------------
  2151         -# /* EV: R-53968-51642 */
  2152         -#
  2153   2203   # Verify that adding an FK action does not absolve the user of the 
  2154   2204   # requirement not to violate the foreign key constraint.
         2205  +#
         2206  +# EVIDENCE-OF: R-53968-51642 Configuring an ON UPDATE or ON DELETE
         2207  +# action does not mean that the foreign key constraint does not need to
         2208  +# be satisfied.
  2155   2209   #
  2156   2210   drop_all_tables
  2157   2211   do_test e_fkey-49.1 {
  2158   2212     execsql {
  2159   2213       CREATE TABLE parent(a COLLATE nocase, b, c, PRIMARY KEY(c, a));
  2160   2214       CREATE TABLE child(d DEFAULT 'a', e, f DEFAULT 'c',
  2161   2215         FOREIGN KEY(f, d) REFERENCES parent ON UPDATE SET DEFAULT
................................................................................
  2182   2236   } {ONE two three}
  2183   2237   do_test e_fkey-49.4 {
  2184   2238     catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' }
  2185   2239   } {1 {foreign key constraint failed}}
  2186   2240   
  2187   2241   
  2188   2242   #-------------------------------------------------------------------------
  2189         -# /* EV: R-07065-59588 */
  2190         -# /* EV: R-28220-46694 */
         2243  +# EVIDENCE-OF: R-07065-59588
  2191   2244   #
  2192   2245   # Test an example from the "ON DELETE and ON UPDATE Actions" section 
  2193   2246   # of foreignkeys.html. This example shows that adding an "ON DELETE DEFAULT"
  2194   2247   # clause does not abrogate the need to satisfy the foreign key constraint
  2195   2248   # (R-28220-46694).
         2249  +#
         2250  +# EVIDENCE-OF: R-28220-46694 For example, if an "ON DELETE SET DEFAULT"
         2251  +# action is configured, but there is no row in the parent table that
         2252  +# corresponds to the default values of the child key columns, deleting a
         2253  +# parent key while dependent child keys exist still causes a foreign key
         2254  +# violation.
  2196   2255   #
  2197   2256   drop_all_tables
  2198   2257   do_test e_fkey-50.1 {
  2199   2258     execsql {
  2200   2259       CREATE TABLE artist(
  2201   2260         artistid    INTEGER PRIMARY KEY, 
  2202   2261         artistname  TEXT
................................................................................
  2223   2282     execsql { SELECT * FROM artist }
  2224   2283   } {0 {Unknown Artist}}
  2225   2284   do_test e_fkey-50.5 {
  2226   2285     execsql { SELECT * FROM track }
  2227   2286   } {14 {Mr. Bojangles} 0}
  2228   2287   
  2229   2288   #-------------------------------------------------------------------------
  2230         -# /* EV: R-09564-22170 */
         2289  +# EVIDENCE-OF: R-09564-22170
  2231   2290   #
  2232   2291   # Check that the order of steps in an UPDATE or DELETE on a parent 
  2233   2292   # table is as follows:
  2234   2293   #
  2235   2294   #   1. Execute applicable BEFORE trigger programs,
  2236   2295   #   2. Check local (non foreign key) constraints,
  2237   2296   #   3. Update or delete the row in the parent table,
................................................................................
  2275   2334       UPDATE parent SET x = 22;
  2276   2335       SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child;
  2277   2336     }
  2278   2337   } {22 23 21 xxx 23}
  2279   2338   
  2280   2339   
  2281   2340   #-------------------------------------------------------------------------
  2282         -# /* EV: R-27383-10246 */
  2283         -#
  2284   2341   # Verify that ON UPDATE actions only actually take place if the parent key
  2285   2342   # is set to a new value that is distinct from the old value. The default
  2286   2343   # collation sequence and affinity are used to determine if the new value
  2287   2344   # is 'distinct' from the old or not.
         2345  +#
         2346  +# EVIDENCE-OF: R-27383-10246 An ON UPDATE action is only taken if the
         2347  +# values of the parent key are modified so that the new parent key
         2348  +# values are not equal to the old.
  2288   2349   #
  2289   2350   drop_all_tables
  2290   2351   do_test e_fkey-52.1 {
  2291   2352     execsql {
  2292   2353       CREATE TABLE zeus(a INTEGER COLLATE NOCASE, b, PRIMARY KEY(a, b));
  2293   2354       CREATE TABLE apollo(c, d, 
  2294   2355         FOREIGN KEY(c, d) REFERENCES zeus ON UPDATE CASCADE
................................................................................
  2329   2390     execsql {
  2330   2391       UPDATE zeus SET b = NULL;
  2331   2392       SELECT typeof(c), c, typeof(d), d FROM apollo;
  2332   2393     }
  2333   2394   } {integer 1 null {}}
  2334   2395   
  2335   2396   #-------------------------------------------------------------------------
  2336         -# /* EV: R-58589-50781 */
         2397  +# EVIDENCE-OF: R-58589-50781
  2337   2398   #
  2338   2399   # Test an example from the "ON DELETE and ON UPDATE Actions" section 
  2339   2400   # of foreignkeys.html. This example demonstrates that ON UPDATE actions
  2340   2401   # only take place if at least one parent key column is set to a value 
  2341   2402   # that is distinct from its previous value.
  2342   2403   #
  2343   2404   drop_all_tables
................................................................................
  2363   2424   } {null}
  2364   2425   
  2365   2426   ###########################################################################
  2366   2427   ### SECTION 5: CREATE, ALTER and DROP TABLE commands
  2367   2428   ###########################################################################
  2368   2429   
  2369   2430   #-------------------------------------------------------------------------
  2370         -# /* EV: R-36018-21755 */
  2371         -# /* EV: R-25384-39337 */
  2372         -# 
  2373   2431   # Test that parent keys are not checked when tables are created.
         2432  +#
         2433  +# EVIDENCE-OF: R-36018-21755 The parent key definitions of foreign key
         2434  +# constraints are not checked when a table is created.
         2435  +#
         2436  +# EVIDENCE-OF: R-25384-39337 There is nothing stopping the user from
         2437  +# creating a foreign key definition that refers to a parent table that
         2438  +# does not exist, or to parent key columns that do not exist or are not
         2439  +# collectively bound by a PRIMARY KEY or UNIQUE constraint.
  2374   2440   #
  2375   2441   # Child keys are checked to ensure all component columns exist. If parent
  2376   2442   # key columns are explicitly specified, SQLite checks to make sure there
  2377   2443   # are the same number of columns in the child and parent keys. (TODO: This
  2378   2444   # is tested but does not correspond to any testable statement.)
  2379   2445   #
  2380         -# /* EV: R-08908-23439 */
  2381         -#
  2382   2446   # Also test that the above statements are true regardless of whether or not
  2383   2447   # foreign keys are enabled:  "A CREATE TABLE command operates the same whether
  2384   2448   # or not foreign key constraints are enabled."
         2449  +#
         2450  +# EVIDENCE-OF: R-08908-23439 A CREATE TABLE command operates the same
         2451  +# whether or not foreign key constraints are enabled.
  2385   2452   # 
  2386   2453   foreach {tn zCreateTbl lRes} {
  2387   2454     1 "CREATE TABLE t1(a, b REFERENCES t1)"                            {0 {}}
  2388   2455     2 "CREATE TABLE t1(a, b REFERENCES t2)"                            {0 {}}
  2389   2456     3 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1)"          {0 {}}
  2390   2457     4 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)"          {0 {}}
  2391   2458     5 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)"          {0 {}}
................................................................................
  2406   2473       drop_all_tables
  2407   2474       execsql {PRAGMA foreign_keys = ON}
  2408   2475       catchsql $zCreateTbl
  2409   2476     } $lRes
  2410   2477   }
  2411   2478   
  2412   2479   #-------------------------------------------------------------------------
  2413         -# /* EV: R-47952-62498 */
         2480  +# EVIDENCE-OF: R-47952-62498 It is not possible to use the "ALTER TABLE
         2481  +# ... ADD COLUMN" syntax to add a column that includes a REFERENCES
         2482  +# clause, unless the default value of the new column is NULL. Attempting
         2483  +# to do so returns an error.
  2414   2484   #
  2415   2485   proc test_efkey_6 {tn zAlter isError} {
  2416   2486     drop_all_tables 
  2417   2487   
  2418   2488     do_test e_fkey-56.$tn.1 "
  2419   2489       execsql { CREATE TABLE tbl(a, b) }
  2420   2490       [list catchsql $zAlter]
................................................................................
  2423   2493   }
  2424   2494   
  2425   2495   test_efkey_6 1 "ALTER TABLE tbl ADD COLUMN c REFERENCES xx" 0
  2426   2496   test_efkey_6 2 "ALTER TABLE tbl ADD COLUMN c DEFAULT NULL REFERENCES xx" 0
  2427   2497   test_efkey_6 3 "ALTER TABLE tbl ADD COLUMN c DEFAULT 0 REFERENCES xx" 1
  2428   2498   
  2429   2499   #-------------------------------------------------------------------------
  2430         -# /* EV: R-47080-02069 */
  2431         -#
  2432   2500   # Test that ALTER TABLE adjusts REFERENCES clauses when the parent table
  2433   2501   # is RENAMED.
  2434   2502   #
  2435         -# /* EV: R-63827-54774 */
         2503  +# EVIDENCE-OF: R-47080-02069 If an "ALTER TABLE ... RENAME TO" command
         2504  +# is used to rename a table that is the parent table of one or more
         2505  +# foreign key constraints, the definitions of the foreign key
         2506  +# constraints are modified to refer to the parent table by its new name
  2436   2507   #
  2437   2508   # Test that these adjustments are visible in the sqlite_master table.
         2509  +#
         2510  +# EVIDENCE-OF: R-63827-54774 The text of the child CREATE TABLE
         2511  +# statement or statements stored in the sqlite_master table are modified
         2512  +# to reflect the new parent table name.
  2438   2513   #
  2439   2514   do_test e_fkey-56.1 {
  2440   2515     drop_all_tables
  2441   2516     execsql {
  2442   2517       CREATE TABLE 'p 1 "parent one"'(a REFERENCES 'p 1 "parent one"', b, PRIMARY KEY(b));
  2443   2518   
  2444   2519       CREATE TABLE c1(c, d REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE);
................................................................................
  2471   2546     {CREATE TABLE "p"(a REFERENCES "p", b, PRIMARY KEY(b))}                   \
  2472   2547     {CREATE TABLE c1(c, d REFERENCES "p" ON UPDATE CASCADE)}                  \
  2473   2548     {CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES "p" ON UPDATE CASCADE)}  \
  2474   2549     {CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES "p" ON UPDATE CASCADE)} \
  2475   2550   ]
  2476   2551   
  2477   2552   #-------------------------------------------------------------------------
  2478         -# /* EV: R-14208-23986 */
  2479         -# /* EV: R-11078-03945 */
  2480         -#
  2481   2553   # Check that a DROP TABLE does an implicit DELETE FROM. Which does not
  2482   2554   # cause any triggers to fire, but does fire foreign key actions.
         2555  +#
         2556  +# EVIDENCE-OF: R-14208-23986 If foreign key constraints are enabled when
         2557  +# it is prepared, the DROP TABLE command performs an implicit DELETE to
         2558  +# remove all rows from the table before dropping it.
         2559  +#
         2560  +# EVIDENCE-OF: R-11078-03945 The implicit DELETE does not cause any SQL
         2561  +# triggers to fire, but may invoke foreign key actions or constraint
         2562  +# violations.
  2483   2563   #
  2484   2564   do_test e_fkey-57.1 {
  2485   2565     drop_all_tables
  2486   2566     execsql {
  2487   2567       CREATE TABLE p(a, b, PRIMARY KEY(a, b));
  2488   2568   
  2489   2569       CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET NULL);
................................................................................
  2537   2617         DELETE FROM p;
  2538   2618         SELECT * FROM log;
  2539   2619       ROLLBACK;
  2540   2620     }
  2541   2621   } {{delete 1}}
  2542   2622   
  2543   2623   #-------------------------------------------------------------------------
  2544         -# /* EV: R-32768-47925 */
  2545         -#
  2546   2624   # If an IMMEDIATE foreign key fails as a result of a DROP TABLE, the
  2547   2625   # DROP TABLE command fails.
  2548   2626   #
         2627  +# EVIDENCE-OF: R-32768-47925 If an immediate foreign key constraint is
         2628  +# violated, the DROP TABLE statement fails and the table is not dropped.
         2629  +#
  2549   2630   do_test e_fkey-58.1 {
  2550   2631     execsql { 
  2551   2632       DELETE FROM c1;
  2552   2633       DELETE FROM c2;
  2553   2634       DELETE FROM c3;
  2554   2635     }
  2555   2636     execsql { INSERT INTO c5 VALUES('a', 'b') }
................................................................................
  2569   2650       SELECT * FROM p;
  2570   2651       SELECT * FROM c5;
  2571   2652       ROLLBACK;
  2572   2653     }
  2573   2654   } {a b a b}
  2574   2655   
  2575   2656   #-------------------------------------------------------------------------
  2576         -# /* EV: R-05903-08460 */
  2577         -#
  2578   2657   # If a DEFERRED foreign key fails as a result of a DROP TABLE, attempting
  2579   2658   # to commit the transaction fails unless the violation is fixed.
         2659  +#
         2660  +# EVIDENCE-OF: R-05903-08460 If a deferred foreign key constraint is
         2661  +# violated, then an error is reported when the user attempts to commit
         2662  +# the transaction if the foreign key constraint violations still exist
         2663  +# at that point.
  2580   2664   #
  2581   2665   do_test e_fkey-59.1 {
  2582   2666     execsql { 
  2583   2667       DELETE FROM c1 ; DELETE FROM c2 ; DELETE FROM c3 ;
  2584   2668       DELETE FROM c4 ; DELETE FROM c5 ; DELETE FROM c6 ;
  2585   2669       DELETE FROM c7 
  2586   2670     }
................................................................................
  2601   2685   } {1 {foreign key constraint failed}}
  2602   2686   do_test e_fkey-59.5 {
  2603   2687     execsql { INSERT INTO p VALUES('a', 'b') }
  2604   2688     execsql COMMIT
  2605   2689   } {}
  2606   2690   
  2607   2691   #-------------------------------------------------------------------------
  2608         -# /* EV: R-57242-37005 */
  2609         -#
  2610   2692   # Any "foreign key mismatch" errors encountered while running an implicit
  2611   2693   # "DELETE FROM tbl" are ignored.
         2694  +#
         2695  +# EVIDENCE-OF: R-57242-37005 Any "foreign key mismatch" errors
         2696  +# encountered as part of an implicit DELETE are ignored.
  2612   2697   #
  2613   2698   drop_all_tables
  2614   2699   do_test e_fkey-60.1 {
  2615   2700     execsql {
  2616   2701       PRAGMA foreign_keys = OFF;
  2617   2702   
  2618   2703       CREATE TABLE p(a PRIMARY KEY, b REFERENCES nosuchtable);
................................................................................
  2648   2733   } {1 {foreign key mismatch}}
  2649   2734   do_test e_fkey-60.6 {
  2650   2735     execsql { DROP TABLE c2 }
  2651   2736     execsql { DELETE FROM p }
  2652   2737   } {}
  2653   2738   
  2654   2739   #-------------------------------------------------------------------------
  2655         -# /* EV: R-54142-41346 */
  2656         -#
  2657   2740   # Test that the special behaviours of ALTER and DROP TABLE are only
  2658   2741   # activated when foreign keys are enabled. Special behaviours are:
  2659   2742   #
  2660   2743   #   1. ADD COLUMN not allowing a REFERENCES clause with a non-NULL 
  2661   2744   #      default value.
  2662   2745   #   2. Modifying foreign key definitions when a parent table is RENAMEd.
  2663   2746   #   3. Running an implicit DELETE FROM command as part of DROP TABLE.
  2664   2747   #
         2748  +# EVIDENCE-OF: R-54142-41346 The properties of the DROP TABLE and ALTER
         2749  +# TABLE commands described above only apply if foreign keys are enabled.
         2750  +#
  2665   2751   do_test e_fkey-61.1.1 {
  2666   2752     drop_all_tables
  2667   2753     execsql { CREATE TABLE t1(a, b) }
  2668   2754     catchsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 }
  2669   2755   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
  2670   2756   do_test e_fkey-61.1.2 {
  2671   2757     execsql { PRAGMA foreign_keys = OFF }
................................................................................
  2723   2809   } {}
  2724   2810   
  2725   2811   ###########################################################################
  2726   2812   ### SECTION 6: Limits and Unsupported Features
  2727   2813   ###########################################################################
  2728   2814   
  2729   2815   #-------------------------------------------------------------------------
  2730         -# /* EV: R-24728-13230 */
  2731         -# /* EV: R-24450-46174 */
  2732         -#
  2733   2816   # Test that MATCH clauses are parsed, but SQLite treats every foreign key
  2734   2817   # constraint as if it were "MATCH SIMPLE".
         2818  +#
         2819  +# EVIDENCE-OF: R-24728-13230 SQLite parses MATCH clauses (i.e. does not
         2820  +# report a syntax error if you specify one), but does not enforce them.
         2821  +#
         2822  +# EVIDENCE-OF: R-24450-46174 All foreign key constraints in SQLite are
         2823  +# handled as if MATCH SIMPLE were specified.
  2735   2824   #
  2736   2825   foreach zMatch [list SIMPLE PARTIAL FULL Simple parTIAL FuLL ] {
  2737   2826     drop_all_tables
  2738   2827     do_test e_fkey-62.$zMatch.1 {
  2739   2828       execsql "
  2740   2829         CREATE TABLE p(a, b, c, PRIMARY KEY(b, c));
  2741   2830         CREATE TABLE c(d, e, f, FOREIGN KEY(e, f) REFERENCES p MATCH $zMatch);
................................................................................
  2756   2845       # Check that the FK is enforced properly if there are no NULL values 
  2757   2846       # in the child key columns.
  2758   2847       catchsql { INSERT INTO c VALUES('a', 2, 4) }
  2759   2848     } {1 {foreign key constraint failed}}
  2760   2849   }
  2761   2850   
  2762   2851   #-------------------------------------------------------------------------
  2763         -# /* EV: R-21599-16038 */
  2764         -#
  2765   2852   # Test that SQLite does not support the SET CONSTRAINT statement. And
  2766   2853   # that it is possible to create both immediate and deferred constraints.
         2854  +#
         2855  +# EVIDENCE-OF: R-21599-16038 In SQLite, a foreign key constraint is
         2856  +# permanently marked as deferred or immediate when it is created.
  2767   2857   #
  2768   2858   drop_all_tables
  2769   2859   do_test e_fkey-62.1 {
  2770   2860     catchsql { SET CONSTRAINTS ALL IMMEDIATE }
  2771   2861   } {1 {near "SET": syntax error}}
  2772   2862   do_test e_fkey-62.2 {
  2773   2863     catchsql { SET CONSTRAINTS ALL DEFERRED }
................................................................................
  2796   2886     execsql { 
  2797   2887       DELETE FROM cd;
  2798   2888       COMMIT;
  2799   2889     }
  2800   2890   } {}
  2801   2891   
  2802   2892   #-------------------------------------------------------------------------
  2803         -# /* EV: R-42264-30503 */
  2804         -#
  2805   2893   # Test that the maximum recursion depth of foreign key action programs is
  2806   2894   # governed by the SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH
  2807   2895   # settings.
         2896  +#
         2897  +# EVIDENCE-OF: R-42264-30503 The SQLITE_MAX_TRIGGER_DEPTH and
         2898  +# SQLITE_LIMIT_TRIGGER_DEPTH settings determine the maximum allowable
         2899  +# depth of trigger program recursion. For the purposes of these limits,
         2900  +# foreign key actions are considered trigger programs.
  2808   2901   #
  2809   2902   proc test_on_delete_recursion {limit} {
  2810   2903     drop_all_tables
  2811   2904     execsql { 
  2812   2905       BEGIN;
  2813   2906       CREATE TABLE t0(a PRIMARY KEY, b);
  2814   2907       INSERT INTO t0 VALUES('x0', NULL);
................................................................................
  2879   2972     test_on_update_recursion 6
  2880   2973   } {1 {too many levels of trigger recursion}}
  2881   2974   do_test e_fkey-63.2.5 {
  2882   2975     sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000
  2883   2976   } {5}
  2884   2977   
  2885   2978   #-------------------------------------------------------------------------
  2886         -# /* EV: R-51769-32730 */
  2887         -#
  2888   2979   # The setting of the recursive_triggers pragma does not affect foreign
  2889   2980   # key actions.
         2981  +#
         2982  +# EVIDENCE-OF: R-51769-32730 The PRAGMA recursive_triggers setting does
         2983  +# not not affect the operation of foreign key actions.
  2890   2984   #
  2891   2985   foreach recursive_triggers_setting [list 0 1 ON OFF] {
  2892   2986     drop_all_tables
  2893   2987     execsql "PRAGMA recursive_triggers = $recursive_triggers_setting"
  2894   2988   
  2895   2989     do_test e_fkey-64.$recursive_triggers_setting.1 {
  2896   2990       execsql {