转载: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:

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

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

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

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

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

在 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行,对应的字段的值会被自动修改成同新的父键匹配。

举例:

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

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

举例:

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

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

这些都很类似于 SQLite 触发器(triggers)的 ON DELETE SET DEFAULT 行为,在效果上同下面的 AFTER DELETE 触发器是类似的:

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

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

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

hosiet

一个学生。May be neither too young nor simple, but sometimes can be naive.

发表评论

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

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据