SQLite Foreign Key Support

SQLite外键支持

1.外键约束简介

2.启用外键支持

3.必需和建议的数据库索引

4.高级外键约束特征

4.1.复合外键约束

4.2.延期的外键约束

4.3.ON DELETE和ON UPDATE操作

5. CREATE，ALTER和DROP TABLE命令

6.限制和不支持的功能

概观

本文档描述了SQLite 版本3.6.19（2009-10-14）中引入的对SQL外键约束的支持。

第一部分通过示例介绍了SQL外键的概念，并定义了用于文档其余部分的术语。第2节描述了应用程序为了在SQLite中启用外键约束而必须执行的步骤（默认情况下它是禁用的）。下一节第3节描述了用户必须创建的索引，以便使用外键约束，以及为了使外键约束有效运行而创建的索引。第4节介绍SQLite支持的高级外键相关功能，第5节介绍ALTER和DROP TABLE命令的增强方式以支持外键约束。最后，第6节列举了当前实施的缺失特征和限制。

本文档不包含用于在SQLite中创建外键约束的语法的完整描述。这可以作为CREATE TABLE语句文档的一部分。

1.外键约束简介

SQL外键约束用于强制表之间的“存在”关系。例如，考虑使用以下SQL命令创建的数据库模式：

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT

CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT, 
  trackartist INTEGER     -- Must map to an artist.artistid!

使用此数据库的应用程序有权假定对于轨道表中的每一行，艺术家表中都存在相应的行。毕竟，宣言中的评论是这样说的。不幸的是，如果用户编辑使用外部工具的数据库，或者如果在一个应用程序中的错误，行可能会被插入到轨道不对应任何行的表的艺术家表。或者可能会从艺术家表格中删除行，从而在轨道表中留下不符合艺术家中剩余行的孤行行。这可能会导致应用程序或应用程序稍后发生故障，或者至少会使应用程序更难编码。

一种解决方案是将SQL外键约束添加到数据库模式以强制执行艺术家和轨道表之间的关系。为此，可以通过将轨道表的声明修改为以下内容来添加外键定义：

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

这样，约束是由SQLite强制执行的。尝试向与艺术家表中的任何行不对应的轨道表中插入一行将失败，因为当轨道表中存在相关行时，试图从艺术家表中删除行有一个例外：如果轨道表中的外键列为NULL，则艺术家表中不需要对应的条目。在SQL中表示，这意味着对于轨道表中的每一行，以下表达式的计算结果为true：

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

提示：如果应用程序需要艺术家和曲目之间的更严格的关系，那么在trackartist列中不允许使用NULL值，只需在模式中添加适当的“NOT NULL”约束即可。

还有其他几种将等效外键声明添加到CREATE TABLE语句的方法。有关详细信息，请参阅CREATE TABLE文档。

以下SQLite命令行会话说明了添加到轨道表的外键约束的影响：

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

正如您所期望的那样，通过删除或更新艺术家表中的行，无法将数据库操作为违反外键约束的状态：

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';

SQLite使用以下术语：

该父表是一个外键约束是指表。本节中示例中的父表是艺术家表。一些书和文章将此称为被引用的表格，这可能更准确，但往往会导致混淆。

该子表是一个外键约束应用于表和包含参考条款表。本节中的示例使用轨道表作为子表。其他书籍和文章将其称为引用表。

该父键是列或设置外键约束是指父表列。这通常是（但不总是）父表的主键。父键必须是父表中的一个或多个命名列，而不是rowid。

该子键是列或一组由外键约束的约束和保持references子句中的子表列。

如果对于子表中的每一行，一个或多个子键列都为NULL，或者在父表中存在一行，每个父键列包含一个等于其值中的值的外键约束关联的子键列。

在上述段落中，当使用此处指定的规则比较值时，术语“相等”意味着相等。以下说明适用：

在比较文本值时，始终使用与父键列关联的排序顺序。

在比较值时，如果父键列具有亲和性，那么在执行比较之前，将亲和性应用于子键值。

2.启用外键支持

为了在SQLite中使用外键约束，必须编译该库时既没有定义SQLITE_OMIT_FOREIGN_KEY也没有SQLITE_OMIT_TRIGGER。如果定义了SQLITE_OMIT_TRIGGER，但SQLITE_OMIT_FOREIGN_KEY不是，那么SQLite的行为与之前版本3.6.19（2009-10-14）相同 - 外键定义被解析，可以使用PRAGMA foreign_key_list查询，但外键约束不被强制执行。PRAGMA foreign_keys命令在此配置中是无操作的。如果定义了OMIT_FOREIGN_KEY，则甚至不能分析外键定义（试图指定外键定义是语法错误）。

假定该库是在启用了外键约束的情况下编译的，它仍然必须由应用程序在运行时使用PRAGMA foreign_keys命令来启用。例如：

sqlite> PRAGMA foreign_keys = ON;

默认情况下禁用外键约束（为了向后兼容），所以必须为每个数据库连接单独启用。（但是，请注意，未来的SQLite版本可能会更改，以便默认情况下启用外键约束。仔细的开发人员不会对默认情况下是否启用外键作出任何假设，而是根据需要启用或禁用它们。）应用程序也可以使用PRAGMA foreign_keys语句来确定当前是否启用了外键。以下命令行会话演示了这一点：

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定义的）编译的。

在多语句事务过程中（当SQLite未处于自动提交模式时），无法启用或禁用外键约束。试图这样做不会返回错误; 它根本没有效果。

3.必需和建议的数据库索引

通常，外键约束的父键是父表的主键。如果它们不是主键，则父键列必须共同受到UNIQUE约束或具有UNIQUE索引。如果父键列具有UNIQUE索引，那么该索引必须使用父表的CREATE TABLE语句中指定的归类序列。例如，

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的一部分的外键是错误的，因为即使父键列被索引，索引也不是唯一的。表child5的外键是错误的，因为即使父键列具有唯一索引，索引也使用不同的整理顺序。表child6和child7不正确，因为虽然它们的父键上都有UNIQUE索引，但这些键与单个UNIQUE索引的列不完全匹配。

如果数据库模式包含需要查看多个表定义才能识别的外键错误，那么在创建表时不会检测到这些错误。相反，此类错误会阻止应用程序准备使用外键修改子表或父表内容的SQL语句。内容更改时报告的错误是“DML错误”，并且模式更改时报告的错误是“DDL错误”。换言之，错误配置的外键约束需要查看子代和父代，这些都是DML错误。外键DML错误的英文错误信息通常是“外键不匹配”，但如果父表不存在，也可以是“无此表”。在以下情况下会报告外键DML错误：

父表不存在，或

在外键约束中指定的父键列不存在，或者

在外键约束中命名的父键列不是父表的主键，并且不受使用CREATE TABLE中指定的整理顺序的唯一约束的约束，或者

子表引用父项的主键而不指定主键列，并且父项中的主键列数与子键列的数量不匹配。

上面的最后一个项目符号如下所示：

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错误。

子键列不需要索引，但它们几乎总是有益的。回到第1节中的示例，每次应用程序从艺术家表（父表）中删除一行时，它将执行与以下SELECT语句等效的操作，以在轨道表（子表）中搜索引用行。

SELECT rowid FROM track WHERE trackartist = ?

在哪里？在上面被替换为从艺术家表中删除的记录的艺术家列的值（回想到，trackartist列是子键，artistid列是父键）。或者更一般地说：

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

如果此SELECT根本返回任何行，则SQLite得出结论，从父表中删除行将违反外键约束并返回错误。如果修改父键的内容或将新行插入到父表中，可能会运行类似的查询。如果这些查询不能使用索引，则他们被迫对整个子表进行线性扫描。在一个不平凡的数据库中，这可能会非常昂贵。

因此，在大多数实际系统中，应该在每个外键约束的子键列上创建一个索引。子键索引不必是（通常不会是）UNIQUE索引。再次回到第1部分中的示例，有效实现外键约束的完整数据库模式可能是：

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文档。

4.高级外键约束特征

4.1.复合外键约束

复合外键约束是子键和父键都是组合键的情况。例如，请考虑以下数据库模式：

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)

在这个系统中，歌曲表中的每个条目都需要映射到专辑表中的一个条目，并且具有艺术家和专辑的相同组合。

父键和子键必须具有相同的基数。在SQLite中，如果任何子键列（在本例中为songartist和songalbum）为NULL，那么父表中不需要相应的行。

4.2.延期的外键约束

SQLite中的每个外键约束都被分类为即时或延迟。外键约束默认是立即生效的。迄今为止提出的所有外键例子都是直接的外键约束。

如果语句修改数据库的内容，以致在语句结束时立即导致外键约束违规，则抛出异常并且语句的影响被还原。相比之下，如果语句修改了数据库的内容，从而违反了延迟外键约束，则不会立即报告违规。在事务尝试执行COMMIT之前，不会检查延迟的外键约束。只要用户有一个打开的事务，就允许数据库存在一个违反任何数量的延迟外键约束的状态。但是，只要外键约束违反，COMMIT就会失败。

如果当前语句不在显式事务（BEGIN / COMMIT / ROLLBACK块）内，则在语句执行完成后立即提交隐式事务。在这种情况下，延迟约束的行为与直接约束的行为相同。

要将外键约束标记为延迟，其声明必须包含以下子句：

DEFERRABLE INITIALLY DEFERRED                -- A deferred foreign key constraint

用于指定外键约束的完整语法可作为CREATE TABLE文档的一部分。用以下任何一个替换上面的短语会创建一个直接的外键约束。

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编译指示可以用于暂时将所有外键约束更改为延迟，而不管它们是如何声明的。

以下示例说明了使用延迟外键约束的效果。

-- 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;

当数据库处于不满足延期外键约束的状态时，嵌套的保存点事务可能被释放。另一方面，事务保存点（当前没有打开的事务时打开的非嵌套保存点）受到与COMMIT相同的限制 - 试图在数据库处于这种状态时将其释放失败。

如果COMMIT语句（或事务SAVEPOINT的RELEASE）因数据库当前处于违反延迟外键约束的状态而失败，并且当前有嵌套保存点，嵌套保存点保持打开状态。

4.3.ON DELETE和ON UPDATE操作

外键ON DELETE和ON UPDATE子句用于配置从父表中删除行（ON DELETE）或修改现有行的父键值（ON UPDATE）时发生的操作。单个外键约束可能会为ON DELETE和ON UPDATE配置不同的操作。外键操作在很多方面与触发器相似。

与SQLite数据库中的每个外键关联的ON DELETE和ON UPDATE操作是“NO ACTION”，“RESTRICT”，“SET NULL”，“SET DEFAULT”或“CASCADE”之一。如果某个操作未明确指定，则默认为“无操作”。

NO ACTION：配置“NO ACTION”意味着：当从数据库修改或删除父密钥时，不会采取任何特殊操作。

RESTRICT：“RESTRICT”操作意味着应用程序在存在一个或多个映射到的子键时禁止删除（用于ON DELETE RESTRICT）或修改（用于ON UPDATE RESTRICT）父键。RESTRICT动作和正常外键约束执行的影响之间的区别在于RESTRICT动作处理在字段更新后立即发生 - 不是在当前语句的末尾，因为它具有即时约束，或者在末尾当前交易与延期约束一样。即使附加的外键约束被延迟，配置RESTRICT操作也会导致SQLite在删除或修改具有相关子键的父键时立即返回错误。

SET NULL：如果配置的操作是“SET NULL”，那么当父键被删除（对于ON DELETE SET NULL）或修改（对于ON UPDATE SET NULL）时，映射的子表中的所有行的子键列到父键被设置为包含SQL NULL值。

SET DEFAULT：“SET DEFAULT”操作类似于“SET NULL”，除了每个子键列都设置为包含列默认值而不是NULL。有关如何将默认值分配给表列的详细信息，请参阅CREATE TABLE文档。

CASCADE：“CASCADE”操作将父键上的删除或更新操作传播给每个相关子键。对于“ON DELETE CASCADE”操作，这意味着与已删除的父行相关联的子表中的每一行也会被删除。对于“ON UPDATE CASCADE”操作，这意味着存储在每个相关子键中的值将被修改以匹配新的父键值。

例如，如下所示向外键添加“ON UPDATE CASCADE”子句可增强第1部分中的示例模式，以允许用户在不中断参照完整性的情况下更新artistid（外键约束的父键）列：

-- 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

配置ON UPDATE或ON DELETE操作并不意味着不需要满足外键约束。例如，如果配置了“ON DELETE SET DEFAULT”操作，但父表中没有与子键列的默认值相对应的行，则在依赖子键存在时删除父键仍会导致外键违反。例如：

-- 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

那些熟悉SQLite触发器的人会注意到，上例中演示的“ON DELETE SET DEFAULT”操作与以下AFTER DELETE触发器类似：

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

每当外键约束的父表中的行被删除时，或者存储在父键列中的值被修改时，事件的逻辑顺序为：

执行适用的BEFORE触发程序，

检查本地（非外键）限制，

2. 更新或删除父表中的行，

3. 执行任何所需的外键操作，

4. 执行适用的AFTER触发程序。

ON UPDATE外键操作和SQL触发器之间有一个重要区别。ON UPDATE操作仅在父键的值被修改时才会采用，以便新的父键值不等于旧键值。例如：

-- 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

5. CREATE，ALTER和DROP TABLE命令

本节介绍CREATE TABLE，ALTER TABLE和DROP TABLE命令与SQLite外键交互的方式。

无论是否启用外键约束，CREATE TABLE命令都会执行相同操作。创建表时，不会检查外键约束的父键定义。没有任何东西阻止用户创建引用不存在的父表的外键定义，也不会阻止不存在或不由PRIMARY KEY或UNIQUE约束共同绑定的父键列。

当启用外键约束时，ALTER TABLE命令在两个方面的工作方式不同：

不可能使用“ALTER TABLE ... ADD COLUMN”语法添加包含REFERENCES子句的列，除非新列的默认值为NULL。试图这样做会返回一个错误。

如果使用“ALTER TABLE ... RENAME TO”命令重命名作为一个或多个外键约束的父表的表，则将外键约束的定义修改为通过其新名称引用父表。CREATE TABLE语句或存储在sqlite_master表中的语句的文本被修改以反映新的父表名。

如果外键约束在准备好时启用，则DROP TABLE命令将执行隐式DELETE，以在删除表之前从表中删除所有行。隐式DELETE不会导致触发任何SQL触发器，但可能会调用外键操作或约束违规。如果违反了直接的外键约束，则DROP TABLE语句将失败，并且该表不会被删除。如果违反了延迟外键约束，那么当用户尝试提交事务时，如果外键约束违规在该点仍然存在，则会报告错误。作为隐式DELETE的一部分遇到的任何“外键不匹配”错误都将被忽略。

对ALTER TABLE和DROP TABLE命令的这些增强功能的目的是确保它们不能用于创建包含外键违例的数据库，至少在启用外键约束的情况下。这条规则有一个例外。如果父键不受作为父表定义的一部分创建的PRIMARY KEY或UNIQUE约束的约束，但由于使用CREATE INDEX命令创建的索引而受到UNIQUE约束，则可以填充子表导致“外键不匹配”错误。如果UNIQUE索引从数据库模式中删除，则父表本身将被删除，不会报告错误。但是，数据库可能处于外键约束的子表包含未引用任何父表行的行的状态。

上述DROP TABLE和ALTER TABLE命令的属性仅适用于启用了外键的情况。如果用户认为它们不合需要，则解决方法是在执行DROP或ALTER TABLE命令之前使用PRAGMA foreign_keys禁用外键约束。当然，虽然外键约束被禁用，但没有什么可以阻止用户违反外键约束，从而创建内部不一致的数据库。

6.限制和不支持的功能

本节列出了其他地方未提及的一些限制和省略功能。

不支持MATCH子句。根据SQL92，可以将MATCH子句附加到组合外键定义，以修改处理子键中出现的NULL值的方式。如果指定了“MATCH SIMPLE”，那么如果一个或多个子键值为NULL，则子键不需要对应于父表的任何行。如果指定了“MATCH FULL”，那么如果任何子键值为NULL，则父表中不需要相应的行，但所有子键值都必须为NULL。最后，如果将外键约束声明为“MATCH PARTIAL”，并且其中一个子键值为NULL，则父表中必须至少存在一行，非NULL子键值与其父键值匹配。SQLite解析MATCH子句（即，如果指定了一个，则不报告语法错误），但不强制执行它们。SQLite中的所有外键约束都像指定了MATCH SIMPLE一样处理。

不支持在延迟模式和即时模式之间切换约束。许多系统允许用户在运行时（例如，使用Oracle“SET CONSTRAINT”命令）在延迟和即时模式之间切换各个外键约束。SQLite不支持这一点。在SQLite中，外键约束在创建时永久标记为延迟或立即。

对外键操作的递归限制。SQLITE_MAX_TRIGGER_DEPTH和SQLITE_LIMIT_TRIGGER_DEPTH设置确定触发程序递归的最大可允许深度。为了达到这些限制，外键行为被视为触发程序。PRAGMA recursive_triggers设置不影响外键操作的操作。无法禁用递归外键操作。

 SQLite is in the Public Domain.