转载:SQLite 外键

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_KEYSQLITE_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 DELETEON UPDATE行为

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

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

ON DELETEON 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 行为不会执行。

发表回复

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