转载：SQLite 外键

hosiet 随笔

SQLite外键(Foreign Key)支持

声明

本篇文章转载自 ITeye:y150988451 的博文，著作权归原作者所有。

由 hosiet 转载并重新排版。再次转载请保留声明。

外键的定义

从 3.6.19 版开始，SQLite 支持外键约束。（Ubuntu 10.04 的 SQLite 版本是 3.6.22，Debian 6.0 的 SQLite 版本是 3.7.0，Ubuntu 15.04 的 SQLite 版本是 3.8.7.4，供参考）

外键用来强制约束两个表之间“存在”的关系。例如，考虑下面的SQL命令建立的schema：

CREATE TABLE artist(
    artistid INTEGER PRIMARY KEY,
    artistname TEXT
    );
CREATE TABLE track(
    trackid INTEGER,
    trackname TEXT,
    trackartist INTEGER —- 必须映射到一个 artist.artistid 记录
);

使用这个数据库的应用可以假定：对于在 track 表中的每一行，都在 artist 表都存在一个对应的行。不幸的是，如果用户使用外部工具编辑数据库，或者在应用程序中存在一个 bug，那么可能在 track 表中插入一行，而在 artist 表中没有相应的记录。或者，在 artist 表中删除某些行，就会在 track 表里留下孤儿行(orphaned rows)，它们在 artist 表中剩下的记录找到任何对应的行。这可能在以后会导致应用的功能出错，或者至少让编写应用程序更复杂。

一个解决方法就是在数据库添加一个外键约束。在 artist 和 track 这两个表之间强制实施一个约束，增加外键定义的 track 表的定义如下：

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

这样，外键约束就由 SQLite 强制实施。往 track 表插入一行在 artist 表中没有对应的数据的记录的企图注定是要失败的！o(∩_∩)o

如果在 track 表还存在依赖于 artist 中的某行的记录，那么尝试从 artist 表删除该行，也会失败。

也就是说，对于在 track 表中的每一行，下面的表达式都是真：

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

在 SQLite 中启用外键支持

  1. 为了在 SQLite 中使用外键约束，编译 sqlite 库时，不能使用忽略外键和忽略触发器，也就是 SQLITE_OMIT_FOREIGN_KEY 和 SQLITE_OMIT_TRIGGER 不能被定义。
  2. 必须在运行时打开 foreign_keys PRAGMA，因为该选项默认是关闭的：PRAGMA foreign_keys = ON;

要求和建议的数据库索引

通常，外键约束的父键在父表里是主键。如果它们不是主键，那么父键栏必须受一个UNIQUE约束或者有一个UNIQUE索引。

如果数据库 schema 还有外键错误，就需要查看多个表才能找到错误。数据表创建时不会检测这些错误，但是这些错误会阻止应用程序用SQL语句来修改子表或者父表的内容。当内容被改变时，将会报告”DML errors”；当schema被改变时，将会报告”DDL errors”。

也就是说，错误地配置外键约束，要求检查子表和父表的是DML错误，一般显示 “foreign key mismatch” 或者 “no such table”。

SELECT rowid FROM WHERE = :parent_key_value

如果这个 SELECT返回数据，那么 SQLite 就断定，从父表删除某行将会违背外键约束，并返回错误。如果父键的值被修改或者在父表插入新的一行，也会产生类似的查询。如果这些查询没有使用索引，它们将强迫对整个子表做线性查找(scan)，这代价可太大了。

在大多数实际系统中，应该在子键这一栏建立索引。子键的索引不必（并且是通常都不必）有一个 UNIQUE 索引（因为在子表中的多行对应于父表中的一行）：

CREATE INDEX trackindex ON track(trackartist);

ON DELETE 和 ON UPDATE行为

外键的 ON DELETE 和 ON UPDATE 从句可以用来配置当从父表中删除某些行时发生的行为(ON DELETE)，或者修改存在的行的父键的值时发生的行为（ON UPDATE）。

单个外键约束可以为 ON DELETE 和 ON UPDATE 配置不同的行为。外键行为在很多时候类似于触发器(trigger)。

ON DELETE 和 ON UPDATE 的行为可以指定为 NO ACTION, RESTRICT, SET NULL, SET DEFAULT 或者 CASCADE。如果没有明确指定行为，那么默认就是 NO ACTION。

  • NO ACTION: 当父键被修改或者删除时，没有特别的行为发生。
  • RESTRICT: 存在一个或者多个子键对应于相应的父键时，应用程序禁止删除(ON DELETE RESTRICT)或者修改(ON UPDATE RESTRICT) 父键RESTRICT 与普通的外键约束的区别是，当字段(field)更新时，RESTRICT 行为立即发生
  • SET NULL: 父键被删除(ON DELETE SET NULL) 或者修改 (ON UPDATE SET NULL)
  • SET DEFAULT: 类似于 SET NULL
  • CASCADE: 将实施在父键上的删除或者更新操作，传播给与之关联的子键。

对于 ON DELETE CASCADE，同被删除的父表中的行相关联的子表中的每一行也会被删除。对于 ON UPDATE CASCADE，存储在子表中的每1行，对应的字段的值会被自动修改成同新的父键匹配。

举例:

CREATE TABLE artist(
    artistid INTEGER PRIMARY KEY,
    artistname TEXT
);
CREATE TABLE track(
    trackid INTEGER,
    trackname TEXT,
    trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE
);
INSERT INTO artist VALUES(1, ‘Dean Martin’);
INSERT INTO artist VALUES(2, ‘Frank Sinatra’);
INSERT INTO track VALUES(14, ‘Mr. Bojangles’, 2);
INSERT INTO track VALUES(15, “That’s Amore”, 2);
INSERT INTO track VALUES(12, ‘Christmas Blues’, 1);
INSERT INTO track VALUES(13, ‘My Way’, 2);

sqlite> PRAGMA foreign_keys = ON; --默认是关闭的, 要在运行时打开
sqlite> SELECT * FROM artist;
1|Dean Martin
2|Frank Sinatra
sqlite> SELECT * FROM track;
14|Mr. Bojangles|2
15|That’s Amore|2
12|Christmas Blues|1
13|My Way|2
sqlite> UPDATE artist SET artistid = 999 WHERE artistname = ‘Dean Martin’;

为 Dean Martin 更改 artist 表中的 artistid 栏目。
一般情况下，这将产生一个约束，因为会让 track 表中的一条记录成为孤儿记录。但对外键定义使用了 ON UPDATE CASCADE 从句后，会把这个更新传给子表，从而让外键约束不被打破。

sqlite> SELECT * FROM artist;
2|Frank Sinatra
999|Dean Martin
sqlite> SELECT * FROM track;
14|Mr. Bojangles|2
15|That’s Amore|2
12|Christmas Blues|999
13|My Way|2

配置一个 ON UPDATE 或者 ON DELETE 行为并不意味着外键约束并不必要满足。
举例来说, 如果 配置了 ON DELETE SET DEFAULT 行为，如果在父表中没有与子表栏目中默认值相对应的行记录，当依赖的子键存在于子表中时，删除父键，会破坏外键。

举例:

sqlite> PRAGMA foreign_keys = ON;
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
);
INSERT INTO artist VALUES(3, ‘Sammy Davis Jr.’);
INSERT INTO track VALUES(14, ‘Mr. Bojangles’, 3);
sqlite> DELETE FROM artist WHERE artistname = ‘Sammy Davis Jr.’;
Error: foreign key constraint failed

从父表中删除一行，会引起子表中相关的子键被设置成整数0。然而，这个值不对应于父表中的任何一行数据。所以，外键约束被打破，就抛出了异常。

sqlite> INSERT INTO artist VALUES(0, ‘Unknown Artist’);

往父表中添加一行，其主键为0，这样删除记录就不会打破外键约束了。

sqlite> DELETE FROM artist WHERE artistname = ‘Sammy Davis Jr.’;
sqlite> SELECT * FROM artist;
0|Unknown Artist
sqlite> SELECT * FROM track;
14|Mr. Bojangles|0

这些都很类似于 SQLite 触发器（triggers）的 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;

外键约束的父表中的某行被删除，或者存储在父键中的值被修改时，时间的逻辑顺序是：

  1. 执行 BEFORE 触发器程序
  2. 检查本地(非外键)约束
  3. 在父表中 更新或者删除行
  4. 执行要求的外键行为
  5. 执行 AFTER 触发器程序

在 ON UPDATE 外键行为和 SQL 触发器之间一个重要区别就是，ON UPDATE 行为只有在父键的值被修改，并且父键的值修改得跟原来不一样时才执行。如果下 UPDATE SET 语句修改的值，跟原来一样，ON UPDATE 行为不会执行。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注