SQLite外键(Foreign Key)支持

转自：http://y150988451.iteye.com/blog/952584

SQLite外键(Foreign Key)支持

从SQLite 3.6.19 开始支持 外键约束. (Ubuntu 10.04 的SQLite版本是 3.6.22,Debian 6.0 的SQLite版本是 3.7.0)

外键约束用来强制 两个表之间”存在”的关系. 比如,考虑下面的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)必须在运行时打开,因为 默认是关闭的

PRAGMA foreign_keys = ON;

要求和建议的数据库索引

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

如果数据库schema还有外键错误,就需要查看多个表才能找到错误. 数据表创建时不会检测这些错误,

这些错误会阻止应用程序 用SQL语句来修改子表或者父表的内容. 当内容被改变时,报告”DML errors”;当schema被改变时报告”DDL errors”

SELECT rowid FROM <child-table> WHERE <child-key> = :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,同被删除的父表中的行 相关联的子表中的每1行,也会被删除.

对于ON UPDATE CASCADE,存储在子表中的每1行,对应的字段的值会被自动修改成同新的父键匹配

举例:

CREATE TABLE artist(

artistid INTEGER PRIMARY KEY,sans-serif; font-size:14px; line-height:25px"> artistname TEXT

);

CREATE TABLE track(

trackid INTEGER,sans-serif; font-size:14px; line-height:25px"> trackname TEXT,sans-serif; font-size:14px; line-height:25px"> 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”,sans-serif; font-size:14px; line-height:25px"> INSERT INTO track VALUES(12,‘Christmas Blues’,1);

INSERT INTO track VALUES(13,‘My Way’,sans-serif; font-size:14px; line-height:25px"> 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从句后,会把这个更新传给 子表,从而让外键约束不被打破)

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,
trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT
);
INSERT INTO artist VALUES(3,‘Sammy Davis Jr.’);
INSERT INTO track VALUES(14,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行为不会执行

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!