*** DRAFT ***

SQLite Requirement Matrix Details
foreignkeys.html

Index Summary Markup Original


R-04042-24825-64259-53667-46708-22366-11905-57675 tcl slt th3 src

To do so, a foreign key definition may be added by modifying the declaration of the track table to the following:

CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

tcl/e_fkey.test:241   th3/req1/foreignkeys01.test:11

/* IMP: R-04042-24825 */
# EVIDENCE-OF: R-04042-24825 To do so, a foreign key definition may be
# added by modifying the declaration of the track table to the
# following: CREATE TABLE track( trackid INTEGER, trackname TEXT,
# trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES
# artist(artistid) );

R-61362-32087-29784-51692-52137-61704-15338-45644 tcl slt th3 src

Attempting to insert a row into the track table that does not correspond to any row in the artist table will fail,

tcl/e_fkey.test:263   th3/req1/foreignkeys01.test:29

/* IMP: R-61362-32087 */
# EVIDENCE-OF: R-61362-32087 Attempting to insert a row into the track
# table that does not correspond to any row in the artist table will
# fail,

R-24401-52400-49114-60456-18821-28705-54480-01087 tcl slt th3 src

as will attempting to delete a row from the artist table when there exist dependent rows in the track table

tcl/e_fkey.test:282   th3/req1/foreignkeys01.test:52

/* IMP: R-24401-52400 */
# EVIDENCE-OF: R-24401-52400 as will attempting to delete a row from the
# artist table when there exist dependent rows in the track table

R-23980-48859-56951-38852-38346-56715-55568-02993 tcl slt th3 src

There is one exception: if the foreign key column in the track table is NULL, then no corresponding entry in the artist table is required.

tcl/e_fkey.test:299   th3/req1/foreignkeys01.test:60

/* IMP: R-23980-48859 */
# EVIDENCE-OF: R-23980-48859 There is one exception: if the foreign key
# column in the track table is NULL, then no corresponding entry in the
# artist table is required.

R-52486-21352-15520-42418-18389-29845-28843-27004 tcl slt th3 src

Expressed in SQL, this means that for every row in the track table, the following expression evaluates to true:

trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)

tcl/e_fkey.test:336   th3/req1/foreignkeys01.test:100   th3/req1/foreignkeys01.test:133   th3/req1/foreignkeys01.test:165   th3/req1/foreignkeys01.test:41   th3/req1/foreignkeys01.test:70

/* IMP: R-52486-21352 */
# EVIDENCE-OF: R-52486-21352 Expressed in SQL, this means that for every
# row in the track table, the following expression evaluates to true:
# trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE
# artistid=trackartist)

R-42412-59321-17229-61863-37154-26359-45547-07737 tcl slt th3 src

Tip: If the application requires a stricter relationship between artist and track, where NULL values are not permitted in the trackartist column, simply add the appropriate "NOT NULL" constraint to the schema.

tcl/e_fkey.test:391   th3/req1/foreignkeys02.test:11

/* IMP: R-42412-59321 */
# EVIDENCE-OF: R-42412-59321 Tip: If the application requires a stricter
# relationship between artist and track, where NULL values are not
# permitted in the trackartist column, simply add the appropriate "NOT
# NULL" constraint to the schema.

R-16127-35442-25332-57824-03233-02546-37060-55461 tcl slt th3 src

The following SQLite command-line session illustrates the effect of the foreign key constraint added to the track table:

sqlite> SELECT * FROM artist;
artistid  artistname       
--------  -----------------
1         Dean Martin      
2         Frank Sinatra    

sqlite> SELECT * FROM track;
trackid  trackname          trackartist
-------  -----------------  -----------
11       That's Amore       1  
12       Christmas Blues    1  
13       My Way             2  

sqlite> -- This fails because the value inserted into the trackartist column (3)
sqlite> -- does not correspond to row in the artist table.
sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
SQL error: foreign key constraint failed

sqlite> -- This succeeds because a NULL is inserted into trackartist. A
sqlite> -- corresponding row in the artist table is not required in this case.
sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL);

sqlite> -- Trying to modify the trackartist field of the record after it has 
sqlite> -- been inserted does not work either, since the new value of trackartist (3)
sqlite> -- Still does not correspond to any row in the artist table.
sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
SQL error: foreign key constraint failed

sqlite> -- Insert the required row into the artist table. It is then possible to
sqlite> -- update the inserted row to set trackartist to 3 (since a corresponding
sqlite> -- row in the artist table now exists).
sqlite> INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';

sqlite> -- Now that "Sammy Davis Jr." (artistid = 3) has been added to the database,
sqlite> -- it is possible to INSERT new tracks using this artist without violating
sqlite> -- the foreign key constraint:
sqlite> INSERT INTO track VALUES(15, 'Boogie Woogie', 3);

tcl/e_fkey.test:416   th3/req1/foreignkeys01.test:111   th3/req1/foreignkeys01.test:81

/* IMP: R-16127-35442 */
# EVIDENCE-OF: R-16127-35442 The following SQLite command-line session
# illustrates the effect of the foreign key constraint added to the
# track table: sqlite> SELECT * FROM artist; artistid artistname
# -------- ----------------- 1 Dean Martin 2 Frank Sinatra sqlite>
# SELECT * FROM track; trackid trackname trackartist -------
# ----------------- ----------- 11 That's Amore 1 12 Christmas Blues 1
# 13 My Way 2 sqlite> -- This fails because the value inserted into
# the trackartist column (3) sqlite> -- does not correspond to row in
# the artist table. sqlite> INSERT INTO track VALUES(14, 'Mr.
# Bojangles', 3); SQL error: foreign key constraint failed sqlite> --
# This succeeds because a NULL is inserted into trackartist. A
# sqlite> -- corresponding row in the artist table is not required in
# this case. sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles',
# NULL); sqlite> -- Trying to modify the trackartist field of the
# record after it has sqlite> -- been inserted does not work either,
# since the new value of trackartist (3) sqlite> -- Still does not
# correspond to any row in the artist table. sqlite> UPDATE track SET
# trackartist = 3 WHERE trackname = 'Mr. Bojangles'; SQL error: foreign
# key constraint failed sqlite> -- Insert the required row into the
# artist table. It is then possible to sqlite> -- update the inserted
# row to set trackartist to 3 (since a corresponding sqlite> -- row
# in the artist table now exists). sqlite> INSERT INTO artist
# VALUES(3, 'Sammy Davis Jr.'); sqlite> UPDATE track SET trackartist
# = 3 WHERE trackname = 'Mr. Bojangles'; sqlite> -- Now that "Sammy
# Davis Jr." (artistid = 3) has been added to the database, sqlite>
# -- it is possible to INSERT new tracks using this artist without
# violating sqlite> -- the foreign key constraint: sqlite> INSERT
# INTO track VALUES(15, 'Boogie Woogie', 3);

R-15958-50233-58629-08203-04879-28676-03150-11250 tcl slt th3 src

As you would expect, it is not possible to manipulate the database to a state that violates the foreign key constraint by deleting or updating rows in the artist table either:

sqlite> -- Attempting to delete the artist record for "Frank Sinatra" fails, since
sqlite> -- the track table contains a row that refer to it.
sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra';
SQL error: foreign key constraint failed

sqlite> -- Delete all the records from the track table that refer to the artist
sqlite> -- "Frank Sinatra". Only then is it possible to delete the artist.
sqlite> DELETE FROM track WHERE trackname = 'My Way';
sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra';

sqlite> -- Try to update the artistid of a row in the artist table while there
sqlite> -- exists records in the track table that refer to it. 
sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
SQL error: foreign key constraint failed

sqlite> -- Once all the records that refer to a row in the artist table have
sqlite> -- been deleted, it is possible to modify the artistid of the row.
sqlite> DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';

tcl/e_fkey.test:460   th3/req1/foreignkeys01.test:144

/* IMP: R-15958-50233 */
# EVIDENCE-OF: R-15958-50233 As you would expect, it is not possible to
# manipulate the database to a state that violates the foreign key
# constraint by deleting or updating rows in the artist table either:
# sqlite> -- Attempting to delete the artist record for "Frank
# Sinatra" fails, since sqlite> -- the track table contains a row
# that refer to it. sqlite> DELETE FROM artist WHERE artistname =
# 'Frank Sinatra'; SQL error: foreign key constraint failed sqlite>
# -- Delete all the records from the track table that refer to the
# artist sqlite> -- "Frank Sinatra". Only then is it possible to
# delete the artist. sqlite> DELETE FROM track WHERE trackname = 'My
# Way'; sqlite> DELETE FROM artist WHERE artistname = 'Frank
# Sinatra'; sqlite> -- Try to update the artistid of a row in the
# artist table while there sqlite> -- exists records in the track
# table that refer to it. sqlite> UPDATE artist SET artistid=4 WHERE
# artistname = 'Dean Martin'; SQL error: foreign key constraint failed
# sqlite> -- Once all the records that refer to a row in the artist
# table have sqlite> -- been deleted, it is possible to modify the
# artistid of the row. sqlite> DELETE FROM track WHERE trackname
# IN('That''s Amore', 'Christmas Blues'); sqlite> UPDATE artist SET
# artistid=4 WHERE artistname = 'Dean Martin';

R-32809-14620-63808-12852-40065-57653-46928-34414 tcl slt th3 src

The parent key must be a named column or columns in the parent table, not the rowid.

th3/req1/foreignkeys03.test:11

/* IMP: R-32809-14620 */
# EVIDENCE-OF: R-32809-14620 The parent key must be a named column or
# columns in the parent table, not the rowid.

R-56032-24923-44084-62651-61742-23069-00366-25575 tcl slt th3 src

The foreign key constraint is satisfied if for each row in the child table either one or more of the child key columns are NULL, or there exists a row in the parent table for which each parent key column contains a value equal to the value in its associated child key column.

tcl/e_fkey.test:489   th3/req1/foreignkeys03.test:45

/* IMP: R-56032-24923 */
# EVIDENCE-OF: R-56032-24923 The foreign key constraint is satisfied if
# for each row in the child table either one or more of the child key
# columns are NULL, or there exists a row in the parent table for which
# each parent key column contains a value equal to the value in its
# associated child key column.

R-57765-12380-65473-27490-60483-53059-23435-27202 tcl slt th3 src

In the above paragraph, the term "equal" means equal when values are compared using the rules specified here.

tcl/e_fkey.test:498   th3/req1/foreignkeys03.test:88

/* IMP: R-57765-12380 */
# EVIDENCE-OF: R-57765-12380 In the above paragraph, the term "equal"
# means equal when values are compared using the rules specified here.

R-15796-47513-29721-16055-11683-21050-32207-60873 tcl slt th3 src

When comparing text values, the collating sequence associated with the parent key column is always used.

tcl/e_fkey.test:540   th3/req1/foreignkeys03.test:120

/* IMP: R-15796-47513 */
# EVIDENCE-OF: R-15796-47513 When comparing text values, the collating
# sequence associated with the parent key column is always used.

R-04240-13860-65376-64948-20761-30630-14399-44960 tcl slt th3 src

When comparing values, if the parent key column has an affinity, then that affinity is applied to the child key value before the comparison is performed.

tcl/e_fkey.test:571   th3/req1/foreignkeys03.test:141

/* IMP: R-04240-13860 */
# EVIDENCE-OF: R-04240-13860 When comparing values, if the parent key
# column has an affinity, then that affinity is applied to the child key
# value before the comparison is performed.

R-33710-56344-23353-36559-53018-17291-08260-37097 tcl slt th3 src

In order to use foreign key constraints in SQLite, the library must be compiled with neither SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined.

tcl/e_fkey.test:34   th3/req1/foreignkeys05.test:30

/* IMP: R-33710-56344 */
# EVIDENCE-OF: R-33710-56344 In order to use foreign key constraints in
# SQLite, the library must be compiled with neither
# SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined.

R-10109-20452-53276-41872-63667-11760-57644-25670 tcl slt th3 src

If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to version 3.6.19 (2009-10-14) - foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list, but foreign key constraints are not enforced.

tcl/e_fkey.test:55   th3/req1/foreignkeys05.test:34

/* IMP: R-10109-20452 */
# EVIDENCE-OF: R-10109-20452 If SQLITE_OMIT_TRIGGER is defined but
# SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to
# version 3.6.19 (2009-10-14) - foreign key definitions are parsed and
# may be queried using PRAGMA foreign_key_list, but foreign key
# constraints are not enforced.

R-22567-44039-44321-46904-30045-11816-43845-22039 tcl slt th3 src

The PRAGMA foreign_keys command is a no-op in this configuration.

tcl/e_fkey.test:64   th3/req1/foreignkeys05.test:40

/* IMP: R-22567-44039 */
# EVIDENCE-OF: R-22567-44039 The PRAGMA foreign_keys command is a no-op
# in this configuration.

R-58428-36660-63931-61833-12651-58158-26648-39835 tcl slt th3 src

If OMIT_FOREIGN_KEY is defined, then foreign key definitions cannot even be parsed (attempting to specify a foreign key definition is a syntax error).

tcl/e_fkey.test:98   th3/req1/foreignkeys05.test:53

/* IMP: R-58428-36660 */
# EVIDENCE-OF: R-58428-36660 If OMIT_FOREIGN_KEY is defined, then
# foreign key definitions cannot even be parsed (attempting to specify a
# foreign key definition is a syntax error).

R-07280-60510-42023-64617-40990-52816-04160-12840 tcl slt th3 src

Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command.

tcl/e_fkey.test:132   th3/req1/foreignkeys04.test:11

/* IMP: R-07280-60510 */
# EVIDENCE-OF: R-07280-60510 Assuming the library is compiled with
# foreign key constraints enabled, it must still be enabled by the
# application at runtime, using the PRAGMA foreign_keys command.

R-44261-39702-04875-31790-36524-45169-57836-16173 tcl slt th3 src

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.

tcl/e_fkey.test:138   th3/req1/foreignkeys04.test:15

/* IMP: R-44261-39702 */
# EVIDENCE-OF: R-44261-39702 Foreign key constraints are disabled by
# default (for backwards compatibility), so must be enabled separately
# for each database connection.

R-08013-37737-04179-14029-08100-20980-22992-19882 tcl slt th3 src

The application can also use a PRAGMA foreign_keys statement to determine if foreign keys are currently enabled.

tcl/e_fkey.test:166   th3/req1/foreignkeys04.test:52

/* IMP: R-08013-37737 */
# EVIDENCE-OF: R-08013-37737 The application can also use a PRAGMA
# foreign_keys statement to determine if foreign keys are currently
# enabled.

R-11255-19907-56460-39957-30226-19999-22429-23256 tcl slt th3 src

The following command-line session demonstrates this:

sqlite> PRAGMA foreign_keys;
0
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite> PRAGMA foreign_keys = OFF;
sqlite> PRAGMA foreign_keys;
0

tcl/e_fkey.test:173   th3/req1/foreignkeys04.test:56

/* IMP: R-11255-19907 */
# EVIDENCE-OF: R-11255-19907 The following command-line session
# demonstrates this: sqlite> PRAGMA foreign_keys; 0 sqlite> PRAGMA
# foreign_keys = ON; sqlite> PRAGMA foreign_keys; 1 sqlite> PRAGMA
# foreign_keys = OFF; sqlite> PRAGMA foreign_keys; 0

R-41784-13339-39195-36557-35011-50589-62735-17221 tcl slt th3 src

Tip: If the command "PRAGMA foreign_keys" returns no data instead of a single row containing "0" or "1", then the version of SQLite you are using does not support foreign keys (either because it is older than 3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).

tcl/e_fkey.test:67   th3/req1/foreignkeys05.test:11

/* IMP: R-41784-13339 */
# EVIDENCE-OF: R-41784-13339 Tip: If the command "PRAGMA foreign_keys"
# returns no data instead of a single row containing "0" or "1", then
# the version of SQLite you are using does not support foreign keys
# (either because it is older than 3.6.19 or because it was compiled
# with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).

R-46649-58537-03180-36291-48705-13434-30415-43306 tcl slt th3 src

It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.

tcl/e_fkey.test:196   th3/req1/foreignkeys06.test:11

/* IMP: R-46649-58537 */
# EVIDENCE-OF: R-46649-58537 It is not possible to enable or disable
# foreign key constraints in the middle of a multi-statement transaction
# (when SQLite is not in autocommit mode). Attempting to do so does not
# return an error; it simply has no effect.

R-13435-26311-61176-01189-39199-58705-21930-17615 tcl slt th3 src

Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index.

tcl/e_fkey.test:606   th3/req1/foreignkeys07.test:11

/* IMP: R-13435-26311 */
# EVIDENCE-OF: R-13435-26311 Usually, the parent key of a foreign key
# constraint is the primary key of the parent table. If they are not the
# primary key, then the parent key columns must be collectively subject
# to a UNIQUE constraint or have a UNIQUE index.

R-00376-39212-00900-22873-65373-47922-05493-41094 tcl slt th3 src

If the parent key columns have a UNIQUE index, then that index must use the collation sequences that are specified in the CREATE TABLE statement for the parent table.

tcl/e_fkey.test:616   th3/req1/foreignkeys07.test:16

/* IMP: R-00376-39212 */
# EVIDENCE-OF: R-00376-39212 If the parent key columns have a UNIQUE
# index, then that index must use the collation sequences that are
# specified in the CREATE TABLE statement for the parent table.

R-27484-01467-40660-08782-16756-57898-53117-01697 tcl slt th3 src

For example,

CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
CREATE UNIQUE INDEX i1 ON parent(c, d);
CREATE INDEX i2 ON parent(e);
CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);

CREATE TABLE child1(f, g REFERENCES parent(a));                        -- Ok
CREATE TABLE child2(h, i REFERENCES parent(b));                        -- Ok
CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d));  -- Ok
CREATE TABLE child4(l, m REFERENCES parent(e));                        -- Error!
CREATE TABLE child5(n, o REFERENCES parent(f));                        -- Error!
CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c));  -- Error!
CREATE TABLE child7(r REFERENCES parent(c));                           -- Error!

The foreign key constraints created as part of tables child1, child2 and child3 are all fine.

tcl/e_fkey.test:654   th3/req1/foreignkeys07.test:22

/* IMP: R-27484-01467 */
# EVIDENCE-OF: R-27484-01467 For example, CREATE TABLE parent(a PRIMARY
# KEY, b UNIQUE, c, d, e, f); CREATE UNIQUE INDEX i1 ON parent(c, d);
# CREATE INDEX i2 ON parent(e); CREATE UNIQUE INDEX i3 ON parent(f
# COLLATE nocase); CREATE TABLE child1(f, g REFERENCES parent(a)); -- Ok
# CREATE TABLE child2(h, i REFERENCES parent(b)); -- Ok CREATE TABLE
# child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); -- Ok CREATE
# TABLE child4(l, m REFERENCES parent(e)); -- Error! CREATE TABLE
# child5(n, o REFERENCES parent(f)); -- Error! CREATE TABLE child6(p, q,
# FOREIGN KEY(p, q) REFERENCES parent(b, c)); -- Error! CREATE TABLE
# child7(r REFERENCES parent(c)); -- Error! The foreign key constraints
# created as part of tables child1, child2 and child3 are all fine.

R-51039-44840-65130-09775-42940-11077-05048-04287 tcl slt th3 src

The foreign key declared as part of table child4 is an error because even though the parent key column is indexed, the index is not UNIQUE.

tcl/e_fkey.test:660   th3/req1/foreignkeys07.test:57

/* IMP: R-51039-44840 */
# EVIDENCE-OF: R-51039-44840 The foreign key declared as part of table
# child4 is an error because even though the parent key column is
# indexed, the index is not UNIQUE.

R-01060-48788-51013-07543-20121-01838-30879-37400 tcl slt th3 src

The foreign key for table child5 is an error because even though the parent key column has a unique index, the index uses a different collating sequence.

tcl/e_fkey.test:666   th3/req1/foreignkeys07.test:66

/* IMP: R-01060-48788 */
# EVIDENCE-OF: R-01060-48788 The foreign key for table child5 is an
# error because even though the parent key column has a unique index,
# the index uses a different collating sequence.

R-63088-37469-41348-62985-35702-04852-27256-05198 tcl slt th3 src

Tables child6 and child7 are incorrect because while both have UNIQUE indices on their parent keys, the keys are not an exact match to the columns of a single UNIQUE index.

tcl/e_fkey.test:672   th3/req1/foreignkeys07.test:74

/* IMP: R-63088-37469 */
# EVIDENCE-OF: R-63088-37469 Tables child6 and child7 are incorrect
# because while both have UNIQUE indices on their parent keys, the keys
# are not an exact match to the columns of a single UNIQUE index.

R-45488-08504-64399-03603-60147-65359-06274-60393 tcl slt th3 src

If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created.

tcl/e_fkey.test:719   th3/req1/foreignkeys07.test:85

/* IMP: R-45488-08504 */
# EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key
# errors that require looking at more than one table definition to
# identify, then those errors are not detected when the tables are
# created.

R-48391-38472-46406-48874-31358-29787-61398-48182 tcl slt th3 src

Instead, such errors prevent the application from preparing SQL statements that modify the content of the child or parent tables in ways that use the foreign keys.

tcl/e_fkey.test:724   th3/req1/foreignkeys07.test:91

/* IMP: R-48391-38472 */
# EVIDENCE-OF: R-48391-38472 Instead, such errors prevent the
# application from preparing SQL statements that modify the content of
# the child or parent tables in ways that use the foreign keys.

R-03108-63659-37525-03634-22905-61973-06931-63842 tcl slt th3 src

The English language error message for foreign key DML errors is usually "foreign key mismatch" but can also be "no such table" if the parent table does not exist.

tcl/e_fkey.test:728   th3/req1/foreignkeys07.test:97

/* IMP: R-03108-63659 */
# EVIDENCE-OF: R-03108-63659 The English language error message for
# foreign key DML errors is usually "foreign key mismatch" but can also
# be "no such table" if the parent table does not exist.

R-35763-48267-24179-20441-32559-50420-39179-45580 tcl slt th3 src

Foreign key DML errors are reported if:

tcl/e_fkey.test:732   th3/req1/foreignkeys07.test:109

/* IMP: R-35763-48267 */
# EVIDENCE-OF: R-35763-48267 Foreign key DML errors are reported if: The
# parent table does not exist, or The parent key columns named in the
# foreign key constraint do not exist, or The parent key columns named
# in the foreign key constraint are not the primary key of the parent
# table and are not subject to a unique constraint using collating
# sequence specified in the CREATE TABLE, or The child table references
# the primary key of the parent without specifying the primary key
# columns and the number of primary key columns in the parent do not
# match the number of child key columns.

R-19353-43643-11289-35798-30332-46906-60350-15505 tcl slt th3 src

The last bullet above is illustrated by the following:

CREATE TABLE parent2(a, b, PRIMARY KEY(a,b));

CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2);        -- Ok
CREATE TABLE child9(x REFERENCES parent2);                             -- Error!
CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2);    -- Error!

tcl/e_fkey.test:801   th3/req1/foreignkeys07.test:121

/* IMP: R-19353-43643 */
# EVIDENCE-OF: R-19353-43643 The last bullet above is illustrated by the
# following: CREATE TABLE parent2(a, b, PRIMARY KEY(a,b)); CREATE TABLE
# child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2); -- Ok CREATE TABLE
# child9(x REFERENCES parent2); -- Error! CREATE TABLE child10(x,y,z,
# FOREIGN KEY(x,y,z) REFERENCES parent2); -- Error!

R-23682-59820-57074-21944-19735-61329-41044-60872 tcl slt th3 src

By contrast, if foreign key errors can be recognized simply by looking at the definition of the child table and without having to consult the parent table definition, then the CREATE TABLE statement for the child table fails.

tcl/e_fkey.test:849   th3/req1/foreignkeys08.test:11

/* IMP: R-23682-59820 */
# EVIDENCE-OF: R-23682-59820 By contrast, if foreign key errors can be
# recognized simply by looking at the definition of the child table and
# without having to consult the parent table definition, then the CREATE
# TABLE statement for the child table fails.

R-33883-28833-60318-48575-53923-47015-06615-40154 tcl slt th3 src

Foreign key DDL errors are reported regardless of whether or not foreign key constraints are enabled when the table is created.

tcl/e_fkey.test:856   th3/req1/foreignkeys08.test:16

/* IMP: R-33883-28833 */
# EVIDENCE-OF: R-33883-28833 Foreign key DDL errors are reported
# regardless of whether or not foreign key constraints are enabled when
# the table is created.

R-15417-28014-30557-42534-17582-00197-65121-60035 tcl slt th3 src

Indices are not required for child key columns

tcl/e_fkey.test:914   th3/req1/foreignkeys07.test:20

/* IMP: R-15417-28014 */
# EVIDENCE-OF: R-15417-28014 Indices are not required for child key
# columns

R-00279-52283-49814-15354-28909-18424-55374-18430 tcl slt th3 src

Returning to the example in section 1, each time an application deletes a row from the artist table (the parent table), it performs the equivalent of the following SELECT statement to search for referencing rows in the track table (the child table).

SELECT rowid FROM track WHERE trackartist = ?

where ? in the above is replaced with the value of the artistid column of the record being deleted from the artist table (recall that the trackartist column is the child key and the artistid column is the parent key).

tcl/e_fkey.test:948   th3/req1/foreignkeys10.test:11

/* IMP: R-00279-52283 */
# EVIDENCE-OF: R-00279-52283 Returning to the example in section 1, each
# time an application deletes a row from the artist table (the parent
# table), it performs the equivalent of the following SELECT statement
# to search for referencing rows in the track table (the child table).
# SELECT rowid FROM track WHERE trackartist = ? where ? in the above is
# replaced with the value of the artistid column of the record being
# deleted from the artist table (recall that the trackartist column is
# the child key and the artistid column is the parent key).

R-47936-10044-12721-09990-49763-27309-53307-48514 tcl slt th3 src

Or, more generally:

SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value

tcl/e_fkey.test:1017   th3/req1/foreignkeys10.test:47

/* IMP: R-47936-10044 */
# EVIDENCE-OF: R-47936-10044 Or, more generally: SELECT rowid FROM
# <child-table> WHERE <child-key> = :parent_key_value

R-23302-30956-02464-62581-38265-62787-52147-51200 tcl slt th3 src

If this SELECT returns any rows at all, then SQLite concludes that deleting the row from the parent table would violate the foreign key constraint and returns an error.

tcl/e_fkey.test:955   th3/req1/foreignkeys10.test:65

/* IMP: R-23302-30956 */
# EVIDENCE-OF: R-23302-30956 If this SELECT returns any rows at all,
# then SQLite concludes that deleting the row from the parent table
# would violate the foreign key constraint and returns an error.

R-61616-46700-48571-17878-11289-09346-32706-45031 tcl slt th3 src

Similar queries may be run if the content of the parent key is modified or a new row is inserted into the parent table.

tcl/e_fkey.test:1033   th3/req1/foreignkeys10.test:87

/* IMP: R-61616-46700 */
# EVIDENCE-OF: R-61616-46700 Similar queries may be run if the content
# of the parent key is modified or a new row is inserted into the parent
# table.

R-15741-50893-04622-59701-05174-07929-61978-12539 tcl slt th3 src

The child key index does not have to be (and usually will not be) a UNIQUE index.

tcl/e_fkey.test:920   th3/req1/foreignkeys09.test:11

/* IMP: R-15741-50893 */
# EVIDENCE-OF: R-15741-50893 The child key index does not have to be
# (and usually will not be) a UNIQUE index.

R-14553-34013-61751-37329-34569-35054-62588-04416 tcl slt th3 src

the complete database schema for efficient implementation of the foreign key constraint might be:

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT, 
  trackartist INTEGER REFERENCES artist
);
CREATE INDEX trackindex ON track(trackartist);

tcl/e_fkey.test:1076   th3/req1/foreignkeys09.test:14

/* IMP: R-14553-34013 */
# EVIDENCE-OF: R-14553-34013 the complete database schema for efficient
# implementation of the foreign key constraint might be: CREATE TABLE
# artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE
# track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES
# artist ); CREATE INDEX trackindex ON track(trackartist);

R-43879-08025-52526-08970-15750-21422-30684-37873 tcl slt th3 src

Attaching a "REFERENCES <parent-table>" clause to a column definition creates a foreign key constraint that maps the column to the primary key of <parent-table>.

tcl/e_fkey.test:884   th3/req1/foreignkeys09.test:32

/* IMP: R-43879-08025 */
# EVIDENCE-OF: R-43879-08025 Attaching a "REFERENCES
# <parent-table>" clause to a column definition creates a foreign
# key constraint that maps the column to the primary key of
# <parent-table>.

R-24676-09859-25258-06679-18327-03281-24955-00264 tcl slt th3 src

For example, consider the following database schema:

CREATE TABLE album(
  albumartist TEXT,
  albumname TEXT,
  albumcover BINARY,
  PRIMARY KEY(albumartist, albumname)
);

CREATE TABLE song(
  songid     INTEGER,
  songartist TEXT,
  songalbum TEXT,
  songname   TEXT,
  FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)
);

In this system, each entry in the song table is required to map to an entry in the album table with the same combination of artist and album.

tcl/e_fkey.test:1168   th3/req1/foreignkeys11.test:11

/* IMP: R-24676-09859 */
# EVIDENCE-OF: R-24676-09859 For example, consider the following
# database schema: CREATE TABLE album( albumartist TEXT, albumname TEXT,
# albumcover BINARY, PRIMARY KEY(albumartist, albumname) ); CREATE TABLE
# song( songid INTEGER, songartist TEXT, songalbum TEXT, songname TEXT,
# FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist,
# albumname) ); In this system, each entry in the song table is required
# to map to an entry in the album table with the same combination of
# artist and album.

R-41062-34431-24503-40012-23472-27163-07838-01416 tcl slt th3 src

Parent and child keys must have the same cardinality.

tcl/e_fkey.test:1122   th3/req1/foreignkeys11.test:70

/* IMP: R-41062-34431 */
# EVIDENCE-OF: R-41062-34431 Parent and child keys must have the same
# cardinality.

R-33626-48418-46855-58211-38849-29467-54588-01790 tcl slt th3 src

In SQLite, if any of the child key columns (in this case songartist and songalbum) are NULL, then there is no requirement for a corresponding row in the parent table.

tcl/e_fkey.test:1207   th3/req1/foreignkeys11.test:53

/* IMP: R-33626-48418 */
# EVIDENCE-OF: R-33626-48418 In SQLite, if any of the child key columns
# (in this case songartist and songalbum) are NULL, then there is no
# requirement for a corresponding row in the parent table.

R-30323-21917-40561-21411-57538-63013-10240-64875 tcl slt th3 src

Each foreign key constraint in SQLite is classified as either immediate or deferred.

src/build.c:2751   src/fkey.c:1406   src/sqliteInt.h:2001   tcl/e_fkey.test:1372   th3/req1/foreignkeys12.test:12

/* IMP: R-30323-21917 */
# EVIDENCE-OF: R-30323-21917 Each foreign key constraint in SQLite is
# classified as either immediate or deferred.

R-35290-16460-32571-10958-08768-45451-01146-58774 tcl slt th3 src

Foreign key constraints are immediate by default.

tcl/e_fkey.test:1369   th3/req1/foreignkeys12.test:15

/* IMP: R-35290-16460 */
# EVIDENCE-OF: R-35290-16460 Foreign key constraints are immediate by
# default.

R-09323-30470-62004-36090-30809-61331-17692-32313 tcl slt th3 src

If a statement modifies the contents of the database so that an immediate foreign key constraint is in violation at the conclusion the statement, an exception is thrown and the effects of the statement are reverted.

tcl/e_fkey.test:1228   th3/req1/foreignkeys12.test:63

/* IMP: R-09323-30470 */
# EVIDENCE-OF: R-09323-30470 If a statement modifies the contents of the
# database so that an immediate foreign key constraint is in violation
# at the conclusion the statement, an exception is thrown and the
# effects of the statement are reverted.

R-49178-21358-58489-31319-06651-33455-27209-45773 tcl slt th3 src

By contrast, if a statement modifies the contents of the database such that a deferred foreign key constraint is violated, the violation is not reported immediately.

tcl/e_fkey.test:1283   th3/req1/foreignkeys12.test:79

/* IMP: R-49178-21358 */
# EVIDENCE-OF: R-49178-21358 By contrast, if a statement modifies the
# contents of the database such that a deferred foreign key constraint
# is violated, the violation is not reported immediately.

R-39692-12488-54491-44352-04912-13609-39585-46004 tcl slt th3 src

Deferred foreign key constraints are not checked until the transaction tries to COMMIT.

tcl/e_fkey.test:1287   th3/req1/foreignkeys12.test:87   th3/req1/foreignkeys14.test:33

/* IMP: R-39692-12488 */
# EVIDENCE-OF: R-39692-12488 Deferred foreign key constraints are not
# checked until the transaction tries to COMMIT.

R-55147-47664-01653-03142-36497-30903-54815-06427 tcl slt th3 src

For as long as the user has an open transaction, the database is allowed to exist in a state that violates any number of deferred foreign key constraints.

tcl/e_fkey.test:1290   th3/req1/foreignkeys12.test:90   th3/req1/foreignkeys14.test:36

/* IMP: R-55147-47664 */
# EVIDENCE-OF: R-55147-47664 For as long as the user has an open
# transaction, the database is allowed to exist in a state that violates
# any number of deferred foreign key constraints.

R-29604-30395-11418-33165-02079-38209-02467-57208 tcl slt th3 src

However, COMMIT will fail as long as foreign key constraints remain in violation.

tcl/e_fkey.test:1294   th3/req1/foreignkeys12.test:94   th3/req1/foreignkeys14.test:40

/* IMP: R-29604-30395 */
# EVIDENCE-OF: R-29604-30395 However, COMMIT will fail as long as
# foreign key constraints remain in violation.

R-56844-61705-36939-38835-21077-60167-01566-10120 tcl slt th3 src

If the current statement is not inside an explicit transaction (a BEGIN/COMMIT/ROLLBACK block), then an implicit transaction is committed as soon as the statement has finished executing. In this case deferred constraints behave the same as immediate constraints.

tcl/e_fkey.test:1321   th3/req1/foreignkeys12.test:105

/* IMP: R-56844-61705 */
# EVIDENCE-OF: R-56844-61705 If the current statement is not inside an
# explicit transaction (a BEGIN/COMMIT/ROLLBACK block), then an implicit
# transaction is committed as soon as the statement has finished
# executing. In this case deferred constraints behave the same as
# immediate constraints.

R-12782-61841-54063-57500-26369-54791-20214-34360 tcl slt th3 src

To mark a foreign key constraint as deferred, its declaration must include the following clause:

DEFERRABLE INITIALLY DEFERRED                -- A deferred foreign key constraint

tcl/e_fkey.test:1348   th3/req1/foreignkeys12.test:17

/* IMP: R-12782-61841 */
# EVIDENCE-OF: R-12782-61841 To mark a foreign key constraint as
# deferred, its declaration must include the following clause:
# DEFERRABLE INITIALLY DEFERRED -- A deferred foreign key constraint

R-09005-28791-60027-45340-35703-17892-19441-18649 tcl slt th3 src

Replacing the phrase above with any of the following creates an immediate foreign key constraint.

NOT DEFERRABLE INITIALLY DEFERRED            -- An immediate foreign key constraint
NOT DEFERRABLE INITIALLY IMMEDIATE           -- An immediate foreign key constraint
NOT DEFERRABLE                               -- An immediate foreign key constraint
DEFERRABLE INITIALLY IMMEDIATE               -- An immediate foreign key constraint
DEFERRABLE                                   -- An immediate foreign key constraint

tcl/e_fkey.test:1355   th3/req1/foreignkeys12.test:21

/* IMP: R-09005-28791 */
# EVIDENCE-OF: R-09005-28791 Replacing the phrase above with any of the
# following creates an immediate foreign key constraint. NOT DEFERRABLE
# INITIALLY DEFERRED -- An immediate foreign key constraint NOT
# DEFERRABLE INITIALLY IMMEDIATE -- An immediate foreign key constraint
# NOT DEFERRABLE -- An immediate foreign key constraint DEFERRABLE
# INITIALLY IMMEDIATE -- An immediate foreign key constraint DEFERRABLE
# -- An immediate foreign key constraint

R-24499-57071-23351-26477-16690-35822-52475-36418 tcl slt th3 src

The following example illustrates the effect of using a deferred foreign key constraint.

-- Database schema. Both tables are initially empty. 
CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT, 
  trackartist INTEGER REFERENCES artist(artistid) DEFERRABLE INITIALLY DEFERRED
);

sqlite3> -- If the foreign key constraint were immediate, this INSERT would
sqlite3> -- cause an error (since as there is no row in table artist with
sqlite3> -- artistid=5). But as the constraint is deferred and there is an
sqlite3> -- open transaction, no error occurs.
sqlite3> BEGIN;
sqlite3>   INSERT INTO track VALUES(1, 'White Christmas', 5);

sqlite3> -- The following COMMIT fails, as the database is in a state that
sqlite3> -- does not satisfy the deferred foreign key constraint. The
sqlite3> -- transaction remains open.
sqlite3> COMMIT;
SQL error: foreign key constraint failed

sqlite3> -- After inserting a row into the artist table with artistid=5, the
sqlite3> -- deferred foreign key constraint is satisfied. It is then possible
sqlite3> -- to commit the transaction without error.
sqlite3>   INSERT INTO artist VALUES(5, 'Bing Crosby');
sqlite3> COMMIT;

tcl/e_fkey.test:1470   th3/req1/foreignkeys13.test:11

/* IMP: R-24499-57071 */
# EVIDENCE-OF: R-24499-57071 The following example illustrates the
# effect of using a deferred foreign key constraint. -- Database schema.
# Both tables are initially empty. CREATE TABLE artist( artistid INTEGER
# PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER,
# trackname TEXT, trackartist INTEGER REFERENCES artist(artistid)
# DEFERRABLE INITIALLY DEFERRED ); sqlite3> -- If the foreign key
# constraint were immediate, this INSERT would sqlite3> -- cause an
# error (since as there is no row in table artist with sqlite3> --
# artistid=5). But as the constraint is deferred and there is an
# sqlite3> -- open transaction, no error occurs. sqlite3> BEGIN;
# sqlite3> INSERT INTO track VALUES(1, 'White Christmas', 5);
# sqlite3> -- The following COMMIT fails, as the database is in a
# state that sqlite3> -- does not satisfy the deferred foreign key
# constraint. The sqlite3> -- transaction remains open. sqlite3>
# COMMIT; SQL error: foreign key constraint failed sqlite3> -- After
# inserting a row into the artist table with artistid=5, the sqlite3>
# -- deferred foreign key constraint is satisfied. It is then possible
# sqlite3> -- to commit the transaction without error. sqlite3>
# INSERT INTO artist VALUES(5, 'Bing Crosby'); sqlite3> COMMIT;

R-07223-48323-28604-04641-30689-62184-47326-29698 tcl slt th3 src

A nested savepoint transaction may be RELEASEd while the database is in a state that does not satisfy a deferred foreign key constraint.

tcl/e_fkey.test:1507   th3/req1/foreignkeys15.test:32

/* IMP: R-07223-48323 */
# EVIDENCE-OF: R-07223-48323 A nested savepoint transaction may be
# RELEASEd while the database is in a state that does not satisfy a
# deferred foreign key constraint.

R-44295-13823-09058-27289-44091-57467-52153-35683 tcl slt th3 src

A transaction savepoint (a non-nested savepoint that was opened while there was not currently an open transaction), on the other hand, is subject to the same restrictions as a COMMIT - attempting to RELEASE it while the database is in such a state will fail.

tcl/e_fkey.test:1546   th3/req1/foreignkeys15.test:47

/* IMP: R-44295-13823 */
# EVIDENCE-OF: R-44295-13823 A transaction savepoint (a non-nested
# savepoint that was opened while there was not currently an open
# transaction), on the other hand, is subject to the same restrictions
# as a COMMIT - attempting to RELEASE it while the database is in such a
# state will fail.

R-37736-42616-04169-53390-00856-45033-53695-63004 tcl slt th3 src

If a COMMIT statement (or the RELEASE of a transaction SAVEPOINT) fails because the database is currently in a state that violates a deferred foreign key constraint and there are currently nested savepoints, the nested savepoints remain open.

tcl/e_fkey.test:1588   th3/req1/foreignkeys15.test:64

/* IMP: R-37736-42616 */
# EVIDENCE-OF: R-37736-42616 If a COMMIT statement (or the RELEASE of a
# transaction SAVEPOINT) fails because the database is currently in a
# state that violates a deferred foreign key constraint and there are
# currently nested savepoints, the nested savepoints remain open.

R-48270-44282-22791-02682-41212-30485-35514-10094 tcl slt th3 src

Foreign key ON DELETE and ON UPDATE clauses are used to configure actions that take place when deleting rows from the parent table (ON DELETE), or modifying the parent key values of existing rows (ON UPDATE).

tcl/e_fkey.test:1652   th3/req1/pragma02.test:12

/* IMP: R-48270-44282 */
# EVIDENCE-OF: R-48270-44282 Foreign key ON DELETE and ON UPDATE clauses
# are used to configure actions that take place when deleting rows from
# the parent table (ON DELETE), or modifying the parent key values of
# existing rows (ON UPDATE).

R-48124-63225-09560-63618-32598-12840-01456-15525 tcl slt th3 src

A single foreign key constraint may have different actions configured for ON DELETE and ON UPDATE.

tcl/e_fkey.test:1660   th3/req1/pragma02.test:46   th3/req1/pragma02.test:77

/* IMP: R-48124-63225 */
# EVIDENCE-OF: R-48124-63225 A single foreign key constraint may have
# different actions configured for ON DELETE and ON UPDATE.

R-33326-45252-38720-14593-54555-59144-23018-05599 tcl slt th3 src

The ON DELETE and ON UPDATE action associated with each foreign key in an SQLite database is one of "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".

src/parse.y:341   src/parse.y:342   src/parse.y:343   src/parse.y:344   src/parse.y:345   tcl/e_fkey.test:1705   th3/req1/foreignkeys16.test:12

/* IMP: R-33326-45252 */
# EVIDENCE-OF: R-33326-45252 The ON DELETE and ON UPDATE action
# associated with each foreign key in an SQLite database is one of "NO
# ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".

R-19803-45884-64369-17915-53923-43638-05068-02663 tcl slt th3 src

If an action is not explicitly specified, it defaults to "NO ACTION".

src/parse.y:333   tcl/e_fkey.test:1711   th3/req1/foreignkeys16.test:199   th3/req1/foreignkeys16.test:39

/* IMP: R-19803-45884 */
# EVIDENCE-OF: R-19803-45884 If an action is not explicitly specified,
# it defaults to "NO ACTION".

R-19971-54976-54556-37696-16686-39038-59563-20085 tcl slt th3 src

Configuring "NO ACTION" means just that: when a parent key is modified or deleted from the database, no special action is taken.

tcl/e_fkey.test:1756   th3/req1/foreignkeys16.test:18   th3/req1/foreignkeys16.test:184

/* IMP: R-19971-54976 */
# EVIDENCE-OF: R-19971-54976 Configuring "NO ACTION" means just that:
# when a parent key is modified or deleted from the database, no special
# action is taken.

R-04272-38653-14303-01704-38451-58113-36784-29823 tcl slt th3 src

The "RESTRICT" action means that the application is prohibited from deleting (for ON DELETE RESTRICT) or modifying (for ON UPDATE RESTRICT) a parent key when there exists one or more child keys mapped to it.

tcl/e_fkey.test:1796   th3/req1/foreignkeys16.test:221   th3/req1/foreignkeys16.test:54

/* IMP: R-04272-38653 */
# EVIDENCE-OF: R-04272-38653 The "RESTRICT" action means that the
# application is prohibited from deleting (for ON DELETE RESTRICT) or
# modifying (for ON UPDATE RESTRICT) a parent key when there exists one
# or more child keys mapped to it.

R-37997-42187-51725-59091-37072-50205-56712-26602 tcl slt th3 src

The difference between the effect of a RESTRICT action and normal foreign key constraint enforcement is that the RESTRICT action processing happens as soon as the field is updated - not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint.

tcl/e_fkey.test:1834   th3/req1/foreignkeys16.test:214   th3/req1/foreignkeys16.test:238   th3/req1/foreignkeys16.test:71

/* IMP: R-37997-42187 */
# EVIDENCE-OF: R-37997-42187 The difference between the effect of a
# RESTRICT action and normal foreign key constraint enforcement is that
# the RESTRICT action processing happens as soon as the field is updated
# - not at the end of the current statement as it would with an
# immediate constraint, or at the end of the current transaction as it
# would with a deferred constraint.

R-24179-60523-60164-40067-19292-39680-45687-45747 tcl slt th3 src

Even if the foreign key constraint it is attached to is deferred, configuring a RESTRICT action causes SQLite to return an error immediately if a parent key with dependent child keys is deleted or modified.

tcl/e_fkey.test:1923   th3/req1/foreignkeys16.test:100   th3/req1/foreignkeys16.test:253

/* IMP: R-24179-60523 */
# EVIDENCE-OF: R-24179-60523 Even if the foreign key constraint it is
# attached to is deferred, configuring a RESTRICT action causes SQLite
# to return an error immediately if a parent key with dependent child
# keys is deleted or modified.

R-03353-05327-21191-28288-58549-30161-65035-16573 tcl slt th3 src

If the configured action is "SET NULL", then when a parent key is deleted (for ON DELETE SET NULL) or modified (for ON UPDATE SET NULL), the child key columns of all rows in the child table that mapped to the parent key are set to contain SQL NULL values.

tcl/e_fkey.test:1999   th3/req1/foreignkeys16.test:125   th3/req1/foreignkeys16.test:275

/* IMP: R-03353-05327 */
# EVIDENCE-OF: R-03353-05327 If the configured action is "SET NULL",
# then when a parent key is deleted (for ON DELETE SET NULL) or modified
# (for ON UPDATE SET NULL), the child key columns of all rows in the
# child table that mapped to the parent key are set to contain SQL NULL
# values.

R-43054-54832-47023-01210-19130-31903-59197-27649 tcl slt th3 src

The "SET DEFAULT" actions are similar to "SET NULL", except that each of the child key columns is set to contain the columns default value instead of NULL.

tcl/e_fkey.test:2042   th3/req1/foreignkeys16.test:142   th3/req1/foreignkeys16.test:292

/* IMP: R-43054-54832 */
# EVIDENCE-OF: R-43054-54832 The "SET DEFAULT" actions are similar to
# "SET NULL", except that each of the child key columns is set to
# contain the columns default value instead of NULL.

R-61376-57267-60176-57380-17573-65142-26150-27865 tcl slt th3 src

A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key.

tcl/e_fkey.test:2084   tcl/e_fkey.test:2129   th3/req1/foreignkeys16.test:159   th3/req1/foreignkeys16.test:309

/* IMP: R-61376-57267 */
# EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or
# update operation on the parent key to each dependent child key.

R-61809-62207-01008-46404-05799-53014-10578-37094 tcl slt th3 src

For an "ON DELETE CASCADE" action, this means that each row in the child table that was associated with the deleted parent row is also deleted.

tcl/e_fkey.test:2087   th3/req1/foreignkeys16.test:162

/* IMP: R-61809-62207 */
# EVIDENCE-OF: R-61809-62207 For an "ON DELETE CASCADE" action, this
# means that each row in the child table that was associated with the
# deleted parent row is also deleted.

R-13877-64542-39833-15687-62784-10394-38782-49019 tcl slt th3 src

For an "ON UPDATE CASCADE" action, it means that the values stored in each dependent child key are modified to match the new parent key values.

tcl/e_fkey.test:2125   th3/req1/foreignkeys16.test:312

/* IMP: R-13877-64542 */
# EVIDENCE-OF: R-13877-64542 For an "ON UPDATE CASCADE" action, it means
# that the values stored in each dependent child key are modified to
# match the new parent key values.

R-65058-57158-61886-52055-64360-05204-52569-52918 tcl slt th3 src

For example, adding an "ON UPDATE CASCADE" clause to the foreign key as shown below enhances the example schema from section 1 to allow the user to update the artistid (the parent key of the foreign key constraint) column without breaking referential integrity:

-- Database schema
CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT, 
  trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE
);

sqlite> SELECT * FROM artist;
artistid  artistname       
--------  -----------------
1         Dean Martin      
2         Frank Sinatra    

sqlite> SELECT * FROM track;
trackid  trackname          trackartist
-------  -----------------  -----------
11       That's Amore       1
12       Christmas Blues    1
13       My Way             2  

sqlite> -- Update the artistid column of the artist record for "Dean Martin".
sqlite> -- Normally, this would raise a constraint, as it would orphan the two
sqlite> -- dependent records in the track table. However, the ON UPDATE CASCADE clause
sqlite> -- attached to the foreign key definition causes the update to "cascade"
sqlite> -- to the child table, preventing the foreign key constraint violation.
sqlite> UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin';

sqlite> SELECT * FROM artist;
artistid  artistname       
--------  -----------------
2         Frank Sinatra    
100       Dean Martin      

sqlite> SELECT * FROM track;
trackid  trackname          trackartist
-------  -----------------  -----------
11       That's Amore       100
12       Christmas Blues    100  
13       My Way             2  

tcl/e_fkey.test:2169   th3/req1/foreignkeys17.test:11

/* IMP: R-65058-57158 */
# EVIDENCE-OF: R-65058-57158 For example, adding an "ON UPDATE CASCADE"
# clause to the foreign key as shown below enhances the example schema
# from section 1 to allow the user to update the artistid (the parent
# key of the foreign key constraint) column without breaking referential
# integrity: -- Database schema CREATE TABLE artist( artistid INTEGER
# PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER,
# trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) ON
# UPDATE CASCADE ); sqlite> SELECT * FROM artist; artistid artistname
# -------- ----------------- 1 Dean Martin 2 Frank Sinatra sqlite>
# SELECT * FROM track; trackid trackname trackartist -------
# ----------------- ----------- 11 That's Amore 1 12 Christmas Blues 1
# 13 My Way 2 sqlite> -- Update the artistid column of the artist
# record for "Dean Martin". sqlite> -- Normally, this would raise a
# constraint, as it would orphan the two sqlite> -- dependent records
# in the track table. However, the ON UPDATE CASCADE clause sqlite>
# -- attached to the foreign key definition causes the update to
# "cascade" sqlite> -- to the child table, preventing the foreign key
# constraint violation. sqlite> UPDATE artist SET artistid = 100
# WHERE artistname = 'Dean Martin'; sqlite> SELECT * FROM artist;
# artistid artistname -------- ----------------- 2 Frank Sinatra 100
# Dean Martin sqlite> SELECT * FROM track; trackid trackname
# trackartist ------- ----------------- ----------- 11 That's Amore 100
# 12 Christmas Blues 100 13 My Way 2

R-53968-51642-29002-32833-59790-62444-61146-02233 tcl slt th3 src

Configuring an ON UPDATE or ON DELETE action does not mean that the foreign key constraint does not need to be satisfied.

tcl/e_fkey.test:2211   th3/req1/foreignkeys18.test:12

/* IMP: R-53968-51642 */
# EVIDENCE-OF: R-53968-51642 Configuring an ON UPDATE or ON DELETE
# action does not mean that the foreign key constraint does not need to
# be satisfied.

R-28220-46694-36270-42674-38857-47778-22952-56853 tcl slt th3 src

For example, if an "ON DELETE SET DEFAULT" action is configured, but there is no row in the parent table that corresponds to the default values of the child key columns, deleting a parent key while dependent child keys exist still causes a foreign key violation.

tcl/e_fkey.test:2255   th3/req1/foreignkeys18.test:15

/* IMP: R-28220-46694 */
# EVIDENCE-OF: R-28220-46694 For example, if an "ON DELETE SET DEFAULT"
# action is configured, but there is no row in the parent table that
# corresponds to the default values of the child key columns, deleting a
# parent key while dependent child keys exist still causes a foreign key
# violation.

R-11856-19836-24274-28109-29706-17487-59516-28752 tcl slt th3 src

For example:

-- Database schema
CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT, 
  trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT
);

sqlite> SELECT * FROM artist;
artistid  artistname       
--------  -----------------
3         Sammy Davis Jr.

sqlite> SELECT * FROM track;
trackid  trackname          trackartist
-------  -----------------  -----------
14       Mr. Bojangles      3

sqlite> -- Deleting the row from the parent table causes the child key
sqlite> -- value of the dependent row to be set to integer value 0. However, this
sqlite> -- value does not correspond to any row in the parent table. Therefore
sqlite> -- the foreign key constraint is violated and an is exception thrown.
sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
SQL error: foreign key constraint failed

sqlite> -- This time, the value 0 does correspond to a parent table row. And
sqlite> -- so the DELETE statement does not violate the foreign key constraint
sqlite> -- and no exception is thrown.
sqlite> INSERT INTO artist VALUES(0, 'Unknown Artist');
sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';

sqlite> SELECT * FROM artist;
artistid  artistname       
--------  -----------------
0         Unknown Artist

sqlite> SELECT * FROM track;
trackid  trackname          trackartist
-------  -----------------  -----------
14       Mr. Bojangles      0

tcl/e_fkey.test:2248   th3/req1/foreignkeys18.test:20

/* IMP: R-11856-19836 */
# EVIDENCE-OF: R-11856-19836 For example: -- Database schema CREATE
# TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE
# TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER
# DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT );
# sqlite> SELECT * FROM artist; artistid artistname --------
# ----------------- 3 Sammy Davis Jr. sqlite> SELECT * FROM track;
# trackid trackname trackartist ------- ----------------- ----------- 14
# Mr. Bojangles 3 sqlite> -- Deleting the row from the parent table
# causes the child key sqlite> -- value of the dependent row to be
# set to integer value 0. However, this sqlite> -- value does not
# correspond to any row in the parent table. Therefore sqlite> -- the
# foreign key constraint is violated and an is exception thrown.
# sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
# SQL error: foreign key constraint failed sqlite> -- This time, the
# value 0 does correspond to a parent table row. And sqlite> -- so
# the DELETE statement does not violate the foreign key constraint
# sqlite> -- and no exception is thrown. sqlite> INSERT INTO
# artist VALUES(0, 'Unknown Artist'); sqlite> DELETE FROM artist
# WHERE artistname = 'Sammy Davis Jr.'; sqlite> SELECT * FROM artist;
# artistid artistname -------- ----------------- 0 Unknown Artist
# sqlite> SELECT * FROM track; trackid trackname trackartist -------
# ----------------- ----------- 14 Mr. Bojangles 0

R-09564-22170-27375-05763-54488-45369-57253-27733 tcl slt th3 src

Whenever a row in the parent table of a foreign key constraint is deleted, or when the values stored in the parent key column or columns are modified, the logical sequence of events is:

  1. Execute applicable BEFORE trigger programs,
  2. Check local (non foreign key) constraints,
  3. Update or delete the row in the parent table,
  4. Perform any required foreign key actions,
  5. Execute applicable AFTER trigger programs.

tcl/e_fkey.test:2294   th3/req1/foreignkeys19.test:12

/* IMP: R-09564-22170 */
# EVIDENCE-OF: R-09564-22170 Whenever a row in the parent table of a
# foreign key constraint is deleted, or when the values stored in the
# parent key column or columns are modified, the logical sequence of
# events is: Execute applicable BEFORE trigger programs, Check local
# (non foreign key) constraints, Update or delete the row in the parent
# table, Perform any required foreign key actions, Execute applicable
# AFTER trigger programs.

R-27383-10246-01655-25271-51755-59417-27078-62241 tcl slt th3 src

An ON UPDATE action is only taken if the values of the parent key are modified so that the new parent key values are not equal to the old.

tcl/e_fkey.test:2351   th3/req1/foreignkeys20.test:12

/* IMP: R-27383-10246 */
# EVIDENCE-OF: R-27383-10246 An ON UPDATE action is only taken if the
# values of the parent key are modified so that the new parent key
# values are not equal to the old.

R-35129-58141-36157-07998-30656-14308-25733-01656 tcl slt th3 src

For example:

-- Database schema
CREATE TABLE parent(x PRIMARY KEY);
CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL);

sqlite> SELECT * FROM parent;
x
----
key

sqlite> SELECT * FROM child;
y
----
key

sqlite> -- Since the following UPDATE statement does not actually modify
sqlite> -- the parent key value, the ON UPDATE action is not performed and
sqlite> -- the child key value is not set to NULL.
sqlite> UPDATE parent SET x = 'key';
sqlite> SELECT IFNULL(y, 'null') FROM child;
y
----
key

sqlite> -- This time, since the UPDATE statement does modify the parent key
sqlite> -- value, the ON UPDATE action is performed and the child key is set
sqlite> -- to NULL.
sqlite> UPDATE parent SET x = 'key2';
sqlite> SELECT IFNULL(y, 'null') FROM child;
y
----
null

tcl/e_fkey.test:2402   th3/req1/foreignkeys20.test:16

/* IMP: R-35129-58141 */
# EVIDENCE-OF: R-35129-58141 For example: -- Database schema CREATE
# TABLE parent(x PRIMARY KEY); CREATE TABLE child(y REFERENCES parent ON
# UPDATE SET NULL); sqlite> SELECT * FROM parent; x ---- key
# sqlite> SELECT * FROM child; y ---- key sqlite> -- Since the
# following UPDATE statement does not actually modify sqlite> -- the
# parent key value, the ON UPDATE action is not performed and sqlite>
# -- the child key value is not set to NULL. sqlite> UPDATE parent
# SET x = 'key'; sqlite> SELECT IFNULL(y, 'null') FROM child; y ----
# key sqlite> -- This time, since the UPDATE statement does modify
# the parent key sqlite> -- value, the ON UPDATE action is performed
# and the child key is set sqlite> -- to NULL. sqlite> UPDATE
# parent SET x = 'key2'; sqlite> SELECT IFNULL(y, 'null') FROM child;
# y ---- null

R-08908-23439-37383-27763-09062-14229-58252-49666 tcl slt th3 src

A CREATE TABLE command operates the same whether or not foreign key constraints are enabled.

tcl/e_fkey.test:2455   th3/req1/foreignkeys21.test:12

/* IMP: R-08908-23439 */
# EVIDENCE-OF: R-08908-23439 A CREATE TABLE command operates the same
# whether or not foreign key constraints are enabled.

R-36018-21755-34988-08386-62029-36954-65009-29361 tcl slt th3 src

The parent key definitions of foreign key constraints are not checked when a table is created.

tcl/e_fkey.test:2438   th3/req1/foreignkeys21.test:15

/* IMP: R-36018-21755 */
# EVIDENCE-OF: R-36018-21755 The parent key definitions of foreign key
# constraints are not checked when a table is created.

R-25384-39337-58415-17102-37697-27977-14467-00416 tcl slt th3 src

There is nothing stopping the user from creating a foreign key definition that refers to a parent table that does not exist, or to parent key columns that do not exist or are not collectively bound by a PRIMARY KEY or UNIQUE constraint.

tcl/e_fkey.test:2441   th3/req1/foreignkeys21.test:18

/* IMP: R-25384-39337 */
# EVIDENCE-OF: R-25384-39337 There is nothing stopping the user from
# creating a foreign key definition that refers to a parent table that
# does not exist, or to parent key columns that do not exist or are not
# collectively bound by a PRIMARY KEY or UNIQUE constraint.

R-47952-62498-54054-51429-18050-07399-51898-49231 tcl slt th3 src

It is not possible to use the "ALTER TABLE ... ADD COLUMN" syntax to add a column that includes a REFERENCES clause, unless the default value of the new column is NULL. Attempting to do so returns an error.

tcl/e_fkey.test:2485   th3/req1/foreignkeys21.test:46

/* IMP: R-47952-62498 */
# EVIDENCE-OF: R-47952-62498 It is not possible to use the "ALTER TABLE
# ... ADD COLUMN" syntax to add a column that includes a REFERENCES
# clause, unless the default value of the new column is NULL. Attempting
# to do so returns an error.

R-47080-02069-19289-28177-02416-33406-46082-01364 tcl slt th3 src

If an "ALTER TABLE ... RENAME TO" command is used to rename a table that is the parent table of one or more foreign key constraints, the definitions of the foreign key constraints are modified to refer to the parent table by its new name

tcl/e_fkey.test:2508   th3/req1/foreignkeys21.test:62

/* IMP: R-47080-02069 */
# EVIDENCE-OF: R-47080-02069 If an "ALTER TABLE ... RENAME TO" command
# is used to rename a table that is the parent table of one or more
# foreign key constraints, the definitions of the foreign key
# constraints are modified to refer to the parent table by its new name

R-63827-54774-55681-65216-20145-09613-22847-12086 tcl slt th3 src

The text of the child CREATE TABLE statement or statements stored in the sqlite_master table are modified to reflect the new parent table name.

tcl/e_fkey.test:2515   th3/req1/foreignkeys21.test:67

/* IMP: R-63827-54774 */
# EVIDENCE-OF: R-63827-54774 The text of the child CREATE TABLE
# statement or statements stored in the sqlite_master table are modified
# to reflect the new parent table name.

R-14208-23986-02817-62350-58798-64603-05852-18001 tcl slt th3 src

If foreign key constraints are enabled when it is prepared, the DROP TABLE command performs an implicit DELETE to remove all rows from the table before dropping it.

tcl/e_fkey.test:2561   th3/req1/foreignkeys16.test:82   th3/req1/foreignkeys22.test:12

/* IMP: R-14208-23986 */
# EVIDENCE-OF: R-14208-23986 If foreign key constraints are enabled when
# it is prepared, the DROP TABLE command performs an implicit DELETE to
# remove all rows from the table before dropping it.

R-11078-03945-63030-33506-24033-34573-31697-17195 tcl slt th3 src

The implicit DELETE does not cause any SQL triggers to fire, but may invoke foreign key actions or constraint violations.

tcl/e_fkey.test:2565   th3/req1/foreignkeys16.test:86   th3/req1/foreignkeys22.test:16   th3/req1/foreignkeys22.test:45

/* IMP: R-11078-03945 */
# EVIDENCE-OF: R-11078-03945 The implicit DELETE does not cause any SQL
# triggers to fire, but may invoke foreign key actions or constraint
# violations.

R-32768-47925-14881-49153-08372-50935-16619-06911 tcl slt th3 src

If an immediate foreign key constraint is violated, the DROP TABLE statement fails and the table is not dropped.

tcl/e_fkey.test:2632   th3/req1/foreignkeys16.test:89   th3/req1/foreignkeys22.test:19

/* IMP: R-32768-47925 */
# EVIDENCE-OF: R-32768-47925 If an immediate foreign key constraint is
# violated, the DROP TABLE statement fails and the table is not dropped.

R-05903-08460-38037-43122-07089-54739-62474-34030 tcl slt th3 src

If a deferred foreign key constraint is violated, then an error is reported when the user attempts to commit the transaction if the foreign key constraint violations still exist at that point.

tcl/e_fkey.test:2665   th3/req1/foreignkeys22.test:22

/* IMP: R-05903-08460 */
# EVIDENCE-OF: R-05903-08460 If a deferred foreign key constraint is
# violated, then an error is reported when the user attempts to commit
# the transaction if the foreign key constraint violations still exist
# at that point.

R-57242-37005-56028-08872-40154-45403-20109-09312 tcl slt th3 src

Any "foreign key mismatch" errors encountered as part of an implicit DELETE are ignored.

tcl/e_fkey.test:2700   th3/req1/foreignkeys22.test:27

/* IMP: R-57242-37005 */
# EVIDENCE-OF: R-57242-37005 Any "foreign key mismatch" errors
# encountered as part of an implicit DELETE are ignored.

R-54142-41346-32014-56293-25636-63110-30822-28140 tcl slt th3 src

The properties of the DROP TABLE and ALTER TABLE commands described above only apply if foreign keys are enabled.

tcl/e_fkey.test:2753   th3/req1/foreignkeys22.test:98

/* IMP: R-54142-41346 */
# EVIDENCE-OF: R-54142-41346 The properties of the DROP TABLE and ALTER
# TABLE commands described above only apply if foreign keys are enabled.

R-24728-13230-17441-17828-27985-11598-34826-53003 tcl slt th3 src

SQLite parses MATCH clauses (i.e. does not report a syntax error if you specify one), but does not enforce them.

tcl/e_fkey.test:2824   th3/req1/foreignkeys23.test:13

/* IMP: R-24728-13230 */
# EVIDENCE-OF: R-24728-13230 SQLite parses MATCH clauses (i.e. does not
# report a syntax error if you specify one), but does not enforce them.

R-24450-46174-44304-25763-63471-62798-12121-54973 tcl slt th3 src

All foreign key constraints in SQLite are handled as if MATCH SIMPLE were specified.

tcl/e_fkey.test:2827   th3/req1/foreignkeys23.test:16

/* IMP: R-24450-46174 */
# EVIDENCE-OF: R-24450-46174 All foreign key constraints in SQLite are
# handled as if MATCH SIMPLE were specified.

R-21599-16038-23557-29825-04044-13537-53990-06867 tcl slt th3 src

In SQLite, a foreign key constraint is permanently marked as deferred or immediate when it is created.

tcl/e_fkey.test:2860   th3/req1/foreignkeys23.test:58

/* IMP: R-21599-16038 */
# EVIDENCE-OF: R-21599-16038 In SQLite, a foreign key constraint is
# permanently marked as deferred or immediate when it is created.

R-42264-30503-59734-27182-09604-54439-24686-19729 tcl slt th3 src

The SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH settings determine the maximum allowable depth of trigger program recursion. For the purposes of these limits, foreign key actions are considered trigger programs.

tcl/e_fkey.test:2902   th3/req1/foreignkeys24.test:12

/* IMP: R-42264-30503 */
# EVIDENCE-OF: R-42264-30503 The SQLITE_MAX_TRIGGER_DEPTH and
# SQLITE_LIMIT_TRIGGER_DEPTH settings determine the maximum allowable
# depth of trigger program recursion. For the purposes of these limits,
# foreign key actions are considered trigger programs.

R-44355-00270-30749-42094-52510-11222-59749-40119 tcl slt th3 src

The PRAGMA recursive_triggers setting does not affect the operation of foreign key actions.

tcl/e_fkey.test:2994   th3/req1/foreignkeys24.test:57

/* IMP: R-44355-00270 */
# EVIDENCE-OF: R-44355-00270 The PRAGMA recursive_triggers setting does
# not affect the operation of foreign key actions.