SQLite外键(Foreign Key)支持 博客分类： android SQLiteSinatraUbuntuDebianSQL
程序员文章站
2024-02-14 11:51:46
...
[size=x-large]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”
也就是说,错误地配置外键约束,要求检查子表和父表的是DML错误, 一般显示”foreign key mismatch” 或者 “no such table”
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,
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行为不会执行[/size]
从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”
也就是说,错误地配置外键约束,要求检查子表和父表的是DML错误, 一般显示”foreign key mismatch” 或者 “no such table”
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,
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行为不会执行[/size]