概览
Overview

本文介绍了SQLite 3.6.19引入的SQL外键约束支持。
This document describes the support for SQL foreign key constraints introduced in SQLite version 3.6.19.

本文的第一部分通过示例和文档后续所需使用的术语定义来引入SQLite外键的概念。 第二部分介绍应用程序启用外键约束所需的步奏（默认时禁用的）。第三部分使用外键约束必须创建的一些索引，以及一些为了提高外键约束运行性能所创建的索引。第四部分介绍SQLite支持的外键的一些高级特性。第五部分如何增强ALTER 和 DROP TABLE命令，以便支持外键约束。最后第六章列举当前实现中缺失的特性和限制。
The first section introduces the concept of an SQL foreign key by example and defines the terminology used for the remainder of the document. Section 2 describes the steps an application must take in order to enable foreign key constraints in SQLite (it is disabled by default). The next section, section 3, describes the indexes that the user must create in order to use foreign key constraints, and those that should be created in order for foreign key constraints to function efficiently. Section 4 describes the advanced foreign key related features supported by SQLite and section 5 describes the way the ALTER and DROP TABLE commands are enhanced to support foreign key constraints. Finally, section 6 enumerates the missing features and limits of the current implementation.

本文档没有包含SQLite中创建外键约束语法的完整描述，这些内容可以在CREATE TABLE语句的文档中找到。
This document does not contain a full description of the syntax used to create foreign key constraints in SQLite. This may be found as part of the documentation for the CREATE TABLE statement.

1. 介绍外键约束
1. Introduction to Foreign Key Constraints

SQLite外键约束用来强制表之间的“存在”关系。例如，考虑一下使用下面SQLite命令创建的数据库结构：
SQL foreign key constraints are used to enforce "exists" relationships between tables. For example, consider a database schema created using the following SQL commands:

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT, 
  trackartist INTEGER     -- 必须映射到一个artist.artistid！  Must map to an artist.artistid!
);

使用这个数据库的应用应当可以假设track表中的每一行记录都在artist表中存在一条相对应的记录。毕竟定义中的备注是这么说的。 不幸的是，如果一个用户使用一个外部工具来编辑数据库，或者应用中存在bug，当向track表中插入一条记录之后没能在artist表中插入相应的记录。又或者从artist表中删除了数据，在track表中遗留下了一条在artist表中没有任何相应记录的孤立记录。这就可能导致之后的应用出现故障，或者使得应用程序的编码变得更加困难。
The applications using this database are entitled to assume that for each row in the track table there exists a corresponding row in the artist table. After all, the comment in the declaration says so. Unfortunately, if a user edits the database using an external tool or if there is a bug in an application, rows might be inserted into the track table that do not correspond to any row in the artist table. Or rows might be deleted from the artist table, leaving orphaned rows in the track table that do not correspond to any of the remaining rows in artist. This might cause the application or applications to malfunction later on, or at least make coding the application more difficult.

一个解决方案是在数据库中添加一个SQLite外键约束，以此来强制artist 和 track两表之间的关系。这样一来，通过修改track表的定义来增加一个外键约束：
One solution is to add an SQL foreign key constraint to the database schema to enforce the relationship between the artist and track table. 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)
);

这样子，由SQLite来执行约束。当向track表中插入一条记录，而这条记录在artist表中没有对应记录的使用，会直接失败，同样的，当从artist表中删除一条记录，而track表中还存在一条依赖的记录时，也一样会失败。有一个例外：如果表track中加了外键的列是一个NULL，那么不需要在artist表中存在对应的记录。用SQL来表示就是，对于track表中的每一行记录，下面的表达式都是true：
This way, the constraint is enforced by SQLite. Attempting to insert a row into the track table that does not correspond to any row in the artist table will fail, as will attempting to delete a row from the artist table when there exist dependent rows in the track table 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. 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)

贴士：如果应用程序需要在artist 和 track之间建立一个严格的关系，即trackartist列不允许出现NULL值，那么只需要简单的在定义中加入“NOT NULL”约束就可以了。
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.

在CREATE TABLE语句中还有几个不同的方式来添加等价的外键定义。具体内容参见CREATE TABLE 文档。
There are several other ways to add an equivalent foreign key declaration to a CREATE TABLE statement. Refer to the CREATE TABLE documentation for details.

下面的SQLite命令行回话列举了track表上所加的外键约束带来的影响：
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> -- 这条会失败，因为插入到trackartist列的值3在artist表中没有相应的记录。
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> -- 这条会成功，因为trackartist中插入的是一个NULL。这时候并不需要artist表中存在相应的记录。
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> -- 修改已经插入的记录的trackartist字段也无法成功，因为新值3在artist表中没有下相应的记录。
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> -- 在artist表中插入所需的记录，然后就可以将已经插入的记录的trackartist更新为3了。这是因为artist表中存在相对应的记录。
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> -- 现在"Sammy Davis Jr." (artistid = 3)已经插入到数据库中，
sqlite> -- 这时候就可以在不违反外键约束的情况下使用这个artist插入一条新的track。
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);

正如你所期望的，删除或者更新artist表中的记录也同样不能违反外键约束：
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> -- 删除artist中的"Frank Sinatra"记录失败，因为track表中包含一条引用该记录的记录。
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> -- 删除track表中所有引用artist "Frank Sinatra"的记录，只有这时才能删除artist记录。
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> -- 修改artist中一条被track表引用的记录的artistid会失败。
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> -- 一旦引用artist中记录的所有记录都删除了，就可以修改这行的artistid了。
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';

SQLite使用下列术语：
SQLite uses the following terminology:

• 父表就是外键约束引用的表。在这章的例子中，父表就是artist表。其它一些书籍和文章将这称之为被引用表，这也算是对的，但是这可能会导致混淆。
  The parent table is the table that a foreign key constraint refers to. The parent table in the example in this section is the artist table. Some books and articles refer to this as the referenced table, which is arguably more correct, but tends to lead to confusion.

• 子表就是使用了外键约束，包含REFERENCES子句的表。在示例中track表就是子表。其它一些数据和文章将这称之为引用表。
  The child table is the table that a foreign key constraint is applied to and the table that contains the REFERENCES clause. The example in this section uses the track table as the child table. Other books and articles refer to this as the referencing table.

• 父键是外键约束所引用的父表中的一列或者一组列。这通常是父表的主键，但不是必须的。父键必须是父表中命名的列，不能是rowid。
  The parent key is the column or set of columns in the parent table that the foreign key constraint refers to. This is normally, but not always, the primary key of the parent table. The parent key must be a named column or columns in the parent table, not the rowid.

• 子键时子表中包含外键约束、加有REFERENCES子句的列或列集合。
  The child key is the column or set of columns in the child table that are constrained by the foreign key constraint and which hold the REFERENCES clause.

如果子表中每行记录都有一个或多个子键时NULL则符合外键约束。或者是父表中存在一条记录，其中每个父键列的值都与对应的子键列的值相等，则符合外键约束。
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.

在上一段中，“相等”是指使用这里指定的规则进行比较相等。遵循下面说明：
In the above paragraph, the term "equal" means equal when values are compared using the rules specified here. The following clarifications apply:

• 当比较文本值时，使用父键关联的排序器。
  When comparing text values, the collating sequence associated with the parent key column is always used.

• 当比较值时，如果父键列有亲和性，那么在执行比较前将这个亲和性应用于子键值上。
  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.

2. 启用外键支持
Enabling Foreign Key Support

如果要在SQLite中使用外键，那么编译库的时候必须不能定义SQLITE_OMIT_FOREIGN_KEY和SQLITE_OMIT_TRIGGER。如果定义了SQLITE_OMIT_TRIGGER而没有定义SQLITE_OMIT_FOREIGN_KEY，那么在SQLite 3.6.19版之前的行为是——会解析外键定义，也可以使用PRAGMA foreign_key_list查询，但是不会执行外键约束。在这种配置中PRAGMA foreign_keys命令是一个空操作。如果定义了SQLITE_OMIT_FOREIGN_KEY，那么外键定义都不会被解析（指定一个外键约束会导致语法错误）。
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. 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 - foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list, but foreign key constraints are not enforced. The PRAGMA foreign_keys command is a no-op in this configuration. 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).

假设编译库时启用了外键约束，还需要在应用运行时使用PRAGMA foreign_keys启用。例如：
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. For example:

sqlite> PRAGMA foreign_keys = ON;

外键约束默认时禁用的（为了向后兼容性），所以每个数据库连接都需要单独启用。（如意，在未来的SQLite发行版中，可能会修改为默认启用外键约束。认真的开发者不应该假定默认情况外键是否启用，而应该把启用或者禁用当做是必须的事情。）应用程序还可以使用PRAGMA foreign_keys语句来检查当前是否启用了外键。下面的命令行会话展示了这一点：
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.) The application can can also use a PRAGMA foreign_keys statement to determine if foreign keys are currently enabled. 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

贴士：如果"PRAGMA foreign_keys"命令没有返回数据（即没有返回一行包含“0”或“1”的记录），那么就是你使用的SQLite版本不支持外键（可能是因为版本低于3.6.19或编译时定义了SQLITE_OMIT_FOREIGN_KEY 或 SQLITE_OMIT_TRIGGER）。
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).

在执行多条语句的事务的过程中时无法启用或者禁止外键约束的（这时SQLite没有位于自动提交模式）。如果这么去做了，不会返回错误，只是没有任何效果。
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.

3. 必须的和推荐的数据库索引
3. Required and Suggested Database Indexes

通常，外键约束中的父键时父表的主键。如果不是主键，那么所有父键列必须使用UNIQUE约束或者有UNIQUE索引。如果父键列有UNIQUE索引，那么这个索引必须使用父表的CREATE TABLE语句指定的排序器。例如：
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. 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. 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!

创建包含外键约束的表child1、child2 和 child3都是可行的。创建包含外建索引的表child4会产生错误，这是因为虽然父键加了索引，但是不是UNIQUE索引。为表child5创建外键会产生错误是因为虽然父键列有唯一索引，但是索引使用了不同的排序器。表child6和child7的出错时因为虽然父键都有UNIQUE索引，但是父键都不是精确匹配一个单独的UNIQUE索引。
The foreign key constraints created as part of tables child1, child2 and child3 are all fine. 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. 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. 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.

如果数据库结构包含一个需哟啊查看多个表的定义才能确定的外键错误，那么这个错误在创建完表以后也不会被检查到。这种错误会组织应用程序通过预编译SQL语句修改子表或父表的内容。当修改内容时报告的错误是“DML错误”，当修改结构时报告的错误是“DDL 错误”。所以，换句话说，需要查看父子两表才能确定的外键约束配置错误时DML错误。对于外键DML错误，通常英语错误信息一般是"foreign key mismatch"，不过如果父表不存在，则也可能是"no such table"。当遇到下面情况会报告外键DML错误：
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. 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. Errors reported when content is changed are "DML errors" and errors reported when the schema is changed are "DDL errors". So, in other words, misconfigured foreign key constraints that require looking at both the child and parent are DML errors. 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. Foreign key DML errors are may be reported if:

• 父表不存在，或者
  The parent table does not exist, or
• 外键约束中指定的父键列不存在，或者
  The parent key columns named in the foreign key constraint do not exist, or
• 外键约束中指定的父键列不是父表的主键，也没有使用CREATE TABLE中指定的排序器限制的唯一约束，或者
  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.

下面说明一下上面最后一个情况：
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!

相比之下，如果外键错误可以简单的通过检查子表定义就能确定，而无需查看父表定义，那么，子表的CREATE TABLE语句就会失败。因为这个错误是发生在修改结构的时候，所以这是一个DDL错误。不管在创建表时是否启用了外键约束，外键DDL错误都会报告出来。
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. Because the error occurs during a schema change, this is a DDL error. Foreign key DDL errors are reported regardless of whether or not foreign key constraints are enabled when the table is created.

子键列上的索引不是必须的，但是通常总是有益的。回到第一节的示例中，每次应用程序从artist表（父表）中删除一条记录时，总要执行一条SELECT语句来搜索track表（子表）中引用的记录。
Indices are not required for child key columns but they are almost always beneficial. 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 = ?

上面问号的位置会替换为要从artist表中删除的记录的artistid值（回忆一下，trackartist列时子键，artistid列是父键）。或者更常见的是：
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). Or, more generally:

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

如果SELECT返回了记录，那么SQLite就可以确定从父表上删除的记录违反了外键约束，并且返回一个错误。同样的，如果修改了父键的内容，或者父表插入一条新记录时，这个查询都需要运行。如果这个查询不能使用索引，那么就会强制对子表全表扫描。在一个较大的数据库上，这个的代价就会很高了。
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. Similar queries may be run if the content of the parent key is modified or a new row is inserted into the parent table. If these queries cannot use an index, they are forced to do a linear scan of the entire child table. In a non-trivial database, this may be prohibitively expensive.

所以，在大多数真实的系统中，每个外键索引的子键上也应当建立一个索引。子键上的索引不需要（通常也不是）一个UNIQUE索引。再次回到第一节的示例，高效实行外键约束的完整数据库定义可能是：
So, in most real systems, an index should be created on the child key columns of each foreign key constraint. The child key index does not have to be (and usually will not be) a UNIQUE index. Returning again to the example in section 1, 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);

上面的代码中使用了一个创建外键约束的简写形式。在列定义里附加一个 "REFERENCES <parent-table>"子句可以创建一个引用<parent-table>主键的外键约束。更多信息参见CREATE TABLE文档。
The block above uses a shorthand form to create the foreign key constraint. 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>. Refer to the CREATE TABLE documentation for further details.

4. 高级外键约束特性
4. Advanced Foreign Key Constraint Features

4.1 复合外键约束
4.1. Composite Foreign Key Constraints

当子键和父键都是复合键的时候就是一个复合外键约束。例如，下面的数据库结构：
A composite foreign key constraint is one where the child and parent keys are both composite keys. 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)
);

在这个系统中，song表中的每个记录都需要映射到一个album表的记录，映射条件是相同的artist和album组合。
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.

父键和子键的基数必须相同。在SQLite中，如果子键列中任何一个（这个例子中的songartist和songalbum）是NULL，那么就不需要有对应的父表记录。
Parent and child keys must have the same cardinality. 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.

4.2 延时外键约束
4.2. Deferred Foreign Key Constraints

SQLite中的每个外键约束都会分为立即的还是延时的。外键约束默认时立即的。目前为止所有提出的外键示例都是立即外键约束。
Each foreign key constraint in SQLite is classified as either immediate or deferred. Foreign key constraints are immediate by default. All the foreign key examples presented so far have been of immediate foreign key constraints.

如果一条语句修改数据库的内容，一个立即外建约束会在语句的末尾处发生违约，并抛出一个异常，语句的影响会恢复。相比而言，如果一条语句修改数据库的内容违反了延时外键约束，这个违约不会立即报告。延时外键约束会等到事务COMMIT的时候才做检查。所以只要用户有一个打开的事务，那么数据库就允许存在违反任意数量延时外键约束的状态存在。但是如果在COMMIT时依然违反外键约束，那么提交就会失败。
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. 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. Deferred foreign key constraints are not checked until the transaction tries to COMMIT. 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. However, COMMIT will fail as long as foreign key constraints remain in violation.

如果当前语句不是位于一个明确的事务中（一个BEGIN/COMMIT/ROLLBACK块），那么一旦语句执行完成，隐式的事务就会提交。这种情况下，延时约束的行为和立即约束是一样的。
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.

要想创建一个延时外键约束，定义中必须包含下面的子句：
To mark a foreign key constraint as deferred, its declaration must include the following clause:

DEFERRABLE INITIALLY DEFERRED                --一个延时外键约束 A deferred foreign key constraint

外键约束的完整语法在CREATE TABLE文档中。使用下面的任何一条语句替换上面的短语就可以创建一个立即外键约束。
The full syntax for specifying foreign key constraints is available as part of the CREATE TABLE documentation. 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

使用defer_foreign_keys pragma可以临时将所有的外键约束都改为延时的，无需关系它们时如何创建的。
The defer_foreign_keys pragma can be used to temporarily change all foreign key constraints to deferred regardless of how they are declared.

下面的示例展示了使用延时外键约束的效果。
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> -- 如果是立即外键约束，这个INSERT就会导致一个错误（因为artist表中没有artistid=5的记录）。
sqlite> -- 但是，这个是一个延时约束，并且是一个打开的事务，所以不会发生错误。
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> -- 下面的COMMIT会失败，因为数据库的状态不符合延时外键约束。事务依旧打开着。
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> -- 在向artist表插入一条artistid=5的记录后，符合了延时外键约束。这时候就可以正常的提交事务了。
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;

当数据库的状态不满足延时外键约束时，会RELEASEA 嵌套的 savepoint。另一方面，一个事务savepoint（如果当前不是在打开的事务中，那么就是打开一个非嵌套savepoint），需要和COMMIT服从一样的限制——当数据库处于会失败的状态时会RELEASE它。
nested savepoint transaction may be RELEASEd while the database is in a state that does not satisfy a deferred foreign key constraint. 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.

如果一个COMMIT语句（或者SAVEPOINT事务中的RELEASE）失败了，由于数据库目前的状态违反了延时外键约束，并且当前时嵌套 savepoints，那么嵌套savepoint会继续打开。
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.

4.3 ON DELETE 和 ON UPDATE 行为
4.3. ON DELETE and ON UPDATE Actions

外键的ON DELETE 和 ON UPDATE 子句用来配置在从父表删除记录时（ON DELETE）或更新已有记录中父键值时（ON UPDATE）的行为。一个外键约束可以为ON DELETE和ON UPDATE配置不同的行为。在很多方面，外键行为类似于触发器。
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). A single foreign key constraint may have different actions configured for ON DELETE and ON UPDATE. Foreign key actions are similar to triggers in many ways.

在一个SQLite数据库中，每个外键关联的ON DELETE和ON UPDATE行为可以是"NO ACTION"、"RESTRICT"、"SET NULL"、"SET DEFAULT"或"CASCADE"之一。如果没有明确指定行为，那么默认时“NO ACTION”。
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". If an action is not explicitly specified, it defaults to "NO ACTION".

• NO ACTION：配置“NO ACTION”的意义只是当父键修改或删除后不会有行为发生。
  : Configuring "NO ACTION" means just that: when a parent key is modified or deleted from the database, no special action is taken.

• RESTRICT：“RESTRICT”行为表示当存在一个或多个子键指向这个父键的时候会禁止应用删除（对于ON DELETE RESTRICT）或修改（对于ON UPDATE RESTRICT）父键。RESTRICT行为和正常的外键约束行为的区别是RESTRICT行为会在字段一更新后就立即执行，而不是像立即约束中等到当前语句的末尾，或延时约束中等到当前事务的末尾才执行。即使这个外键约束时延时约束，如果配置了RESTRICT行为，只要修改或删除了子键依赖的父键，SQLite都会立即返回一个错误。
  : 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. 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. 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.

• SET NULL：如果配置了“SET NULL”行为，那么当删除（对于ON DELETE SET NULL）或修改（对于ON UPDATE SET NULL）父键时，子表中所有映射到这个父键的记录的子键都会被设置为NULL值。
  : 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.

• SET DEFAULT：“SET DEFAULT”行为与“SET NULL”类似，只是每个子键列会设置成列默认值，而不是NULL。如何为表中的列指定默认值请参见CREATE TABLE文档。
  : 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. Refer to the CREATE TABLE documentation for details on how default values are assigned to table columns.

• CASCADE：“CASCADE”行为会将父键上的删除或更新行为传播到依赖的子键上。对于一个“ON DELETE CASCADE”行为，这意味着子表中所有与删除的父键相关联的记录都会被删除掉。对于“ON UPDATE CASCADE”行为，这意味着，存储在所有依赖的子键中的值都会被修改为新父键的值。
  : A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key. 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. 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.

例如，在第一章的示例中，按下面所示在外间上加一个“ON UPDATE CASCADE”子句，这样就允许用户在不破坏引用完整性的情况下更新artistid列（外键约束中的父键）：
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> -- 更新artist中“Dean Martin”记录的artistid列。通常这回触发约束，
sqlite> -- 因为这回孤立track表中的两条依赖记录。但是在外键约束定义后加上ON UPDATE CASCADE子句后，
sqlite> -- 会使更新“叠加”到子表上，防止违反外间约束。
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  

配置了ON UPDATE 或 ON DELETE行为并不意味着就不需要满足外键约束了。例如，如果配置了一个“ON DELETE SET DEFAULT”行为，但是父表中没有符合子键列默认值的记录，那么删除一个存在依赖子键的父键依然会导致外键约束。例如：
Configuring an ON UPDATE or ON DELETE action does not mean that the foreign key constraint does not need to be satisfied. 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. 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> -- 从父表中删除一条记录会使依赖其的子键记录被设置为默认值整数0。
qlite> -- 但是，这个值在父表中没有任何对应记录。这就违反了外键约束，也就会抛出一个异常。
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> -- 这一次，值0对应了父表中的一条记录，因此，DELETE语句就不会违反外键约束了，也就不会抛出异常了。
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

熟悉SQLite 触发器的用户会注意到，上面示例中所演示的“ON DELETE SET DEFAULT”的效果与下面的AFTER DELETE触发器很相似：
Those familiar with SQLite triggers will have noticed that the "ON DELETE SET DEFAULT" action demonstrated in the example above is similar in effect to the following AFTER DELETE trigger:

CREATE TRIGGER on_delete_set_default AFTER DELETE ON artist BEGIN
  UPDATE child SET trackartist = 0 WHERE trackartist = old.artistid;
END;

当一个外键约束的父表中的记录被删除了，或者父键列中存储的值被修改了，触发的事件的逻辑顺序如下：
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. 执行应用程序的BEFORE触发器程序，
   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. 执行应用程序的AFTER 触发器程序。
   Execute applicable AFTER trigger programs.

在ON UPDATE 外键行为和SQL触发器之间有一个重要的区别时，只有当父键的值修改时才会执行ON UPDATE 行为，所以，新的父键值与老值不相同。例如：
There is one important difference between ON UPDATE foreign key actions and SQL triggers. 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. 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> -- 由于下面的UPDATE语句没有真正修改父键值，所以ON UPDATE行为不会执行，子键值也不会设置为NULL。
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> -- 这一次，因为UPDATE语句修改了父键值，所以就会执行ON UPDATE行为，子键会被设置为NULL。
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

5. CREATE、ALTER和DROP TABLE命令
5. CREATE, ALTER and DROP TABLE commands

这一节主要讲述CREATE TABLE、ALTER TABLE和DROP TABLE命令与SQLite的外键之间相互作用的方法。
This section describes the way the CREATE TABLE, ALTER TABLE, and DROP TABLE commands interact with SQLite's foreign keys.

无论是否启用了外键约束，CREATE TABLE命令都是一样的运行。当创建表的时候，并不会检查外键约束的父键定义。无法阻止用户创建一个父表不存在或父键列不存在或父键没有遵守PRIMARY KEY或UNIQUE约束的外键定义。
A CREATE TABLE command operates the same whether or not foreign key constraints are enabled. The parent key definitions of foreign key constraints are not checked when a table is created. 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.

ALTER TABLE命令根据是否启用了外键约束会使用两种不同的方式：
The ALTER TABLE command works differently in two respects when foreign key constraints are enabled:

• 除非新列的默认值时NULL，否则无法使用“ALTER TABLE ... ADD COLUMN”语法来增加一个包含REFERENCES子句的列。如果这么去执行会返回一个错误。
  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.

• 如果使用“ALTER TABLE ... RENAME TO”命令来重命名一个外键约束中的父表名，那么会修改外键约束定义使用新名字指向父表。存储在sqlite_master表中的子表的CREATE TABLE语句文本也会更新新父表的名字。
  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. 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.

当启用了外键约束后，DROP TABLE命令会隐式执行DELETE在删除表之前移除表中的所有记录。隐式的DELETE不会触发任何触发器，但是会执行外键行为和约束验证。如果违反了一个立即外键约束，DROP TABLE语句会失败，表也不会被卸载。如果违反了延时外键约束，那么在提交事务的时候，如果依然违反外键约束，那么会报告一个错误。隐式DELETE中遇到的"foreign key mismatch"错误都会被忽略。
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. The implicit DELETE does not cause any SQL triggers to fire, but may invoke foreign key actions or constraint violations. If an immediate foreign key constraint is violated, the DROP TABLE statement fails and the table is not dropped. 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. Any "foreign key mismatch" errors encountered as part of an implicit DELETE are ignored.

对ALTER TABLE和DROP TABLE命令进行这些增加的目的时确保这些命令不会产生一个违反外键约束的数据库，至少是在启用外键的时候。但是这个规则有一个例外。 如果一个父键在父表定义中没有受限于PRIMARY KEY或UNIQUE约束，但是借助于一个CREATE INDEX命令创建的索引受限于UNIQUE约束。那么，就可以建立子表，而不会触发“foreign key mismatch”错误。如果从数据库中卸载了这个UNIQUE索引，那么父表自身也会卸载，而不会报出任何错误。但是，这样会导致数据库中外键约束的子表中存在没有指向任何外键表记录的记录。这种情况是可以被避免的，只要父键的PRIMARY KEY或UNIQUE约束都是在父表定义中添加的，而不是使用额外的UNIQUE索引就可以了。
The intent of these enhancements to the ALTER TABLE and DROP TABLE commands is to ensure that they cannot be used to create a database that contains foreign key violations, at least while foreign key constraints are enabled. There is one exception to this rule though. If a parent key is not subject to a PRIMARY KEY or UNIQUE constraint created as part of the parent table definition, but is subject to a UNIQUE constraint by virtue of an index created using the CREATE INDEX command, then the child table may be populated without causing a "foreign key mismatch" error. If the UNIQUE index is dropped from the database schema, then the parent table itself is dropped, no error will be reported. However the database may be left in a state where the child table of the foreign key constraint contains rows that do not refer to any parent table row. This case can be avoided if all parent keys in the database schema are constrained by PRIMARY KEY or UNIQUE constraints added as part of the parent table definition, not by external UNIQUE indexes.

上面介绍的DROP TABLE和ALTER TABLE命令属性只有当启用了外键才会使用。如果用户认为这些时不合适的，那么变通方案就是在执行DROP或ALTER TABLE命令之前使用PRAGMA foreign_keys 来禁用外键约束。当然，一旦禁用了外键约束，就没有任何办法来组织用户破坏外键约束，这也会导致出现不一致的数据库。
The properties of the DROP TABLE and ALTER TABLE commands described above only apply if foreign keys are enabled. If the user considers them undesirable, then the workaround is to use PRAGMA foreign_keys to disable foreign key constraints before executing the DROP or ALTER TABLE command. Of course, while foreign key constraints are disabled, there is nothing to stop the user from violating foreign key constraints and thus creating an internally inconsistent database.

6. 限制和不支持的特性
6. Limits and Unsupported Features

本节罗列了少量别处没有提及的限制和忽略的特性。
This section lists a few limitations and omitted features that are not mentioned elsewhere.

1. 不支持MATCH子句。依照SQL92，在一个复合外键定义后面可以附加一个MATCH子句，用来修改子键中出现NULL值时的处理方式。如果指定了“MATCH SIMPLE”，那么如果子键之中存在一个或多个NULL时不需要有与之对应的父表记录。如果指定了“MATCH FULL”，那么如果所有的子键值都是NULL，那么不需要在父表有对应的记录，但是所有子键值都必须时NULL。最后，如果外键约束指定为“MATCH PARTIAL”并且有一个子键值是NULL，那么父表中必须至少有一行记录能匹配子键的非NULL值。
   No support for the MATCH clause. According to SQL92, a MATCH clause may be attached to a composite foreign key definition to modify the way NULL values that occur in child keys are handled. If "MATCH SIMPLE" is specified, then a child key is not required to correspond to any row of the parent table if one or more of the child key values are NULL. If "MATCH FULL" is specified, then if any of the child key values is NULL, no corresponding row in the parent table is required, but all child key values must be NULL. Finally, if the foreign key constraint is declared as "MATCH PARTIAL" and one of the child key values is NULL, there must exist at least one row in the parent table for which the non-NULL child key values match the parent key values.

   SQLite解析MATCH子句（如果你指定了不会报语法错误），但是不会实施。在SQLite中所有的外键约束都指定为MATCH SIMPLE。
   SQLite parses MATCH clauses (i.e. does not report a syntax error if you specify one), but does not enforce them. All foreign key constraints in SQLite are handled as if MATCH SIMPLE were specified.

2. 不支持立即约束和延时约束之间切换。许多系统允许用户在运行时将某个外键约束在延时和立即之间进行切换（例如使用ORACLE的“SET CONSTRAINT”命令）。SQLite不支持这个，在SQLite中，外键约束在创建时就被永久指定了是延时的还是立即的。
   No support for switching constraints between deferred and immediate mode. Many systems allow the user to toggle individual foreign key constraints between deferred and immediate mode at runtime (for example using the Oracle "SET CONSTRAINT" command). SQLite does not support this. In SQLite, a foreign key constraint is permanently marked as deferred or immediate when it is created.

3. 外键行为中的递归限制。SQLITE_MAX_TRIGGER_DEPTH和SQLITE_LIMIT_TRIGGER_DEPTH 设置决定了触发器程序的最大递归升读。由于这些限制，外键行为被看做时一个触发器程序。PRAGMA recursive_triggers设置对于外键行为是无效的。也不可能禁用递归外键行为。
   Recursion limit on foreign key actions. 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. The PRAGMA recursive_triggers setting does not not affect the operation of foreign key actions. It is not possible to disable recursive foreign key actions.