SQLite外键

sqlite外键 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,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,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行为不会执行

SQLite外键

猜你在找的Sqlite相关文章