`
y150988451
  • 浏览: 194515 次
  • 性别: Icon_minigender_2
  • 来自: 北京
社区版块
存档分类
最新评论
阅读更多

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

 

 

 

分享到:
评论

相关推荐

    用触发器实现SQLite的外键约束

    最近在做数码相框上的嵌入式开发,开发过程中使用的SQLite数据库,但是编码的过程中,遇到个问题,SQLite不支持外键约束,外键约束会被解析但不会被执行。

    默认开启外键约System.Data.SQLite.dll束重新编译版1.0.85.0

    System.Data.SQLite.dll默认开启外键约束重新编译版,文件版本1.0.85.0,官网源码,除了开启外键约束,未作任何修改,内含.net2.0、.net4.0的32bit和64bit的release,以及源码修改说明。

    SQL 级联删除与级联更新的方法

    当你更新或删除主键表时,那么外键表也会跟随一起更新或删除,需要在建表时设置级联属性 CREATE TABLE Countries(CountryId INT PRIMARY KEY) INSERT INTO Countries (CountryId) VALUES (1) INSERT INTO Countries ...

    C#从实体类映射到数据库(sqlite)

    开发过程中经常会遇到实体类保存到数据库的需求。通常就是建好数据库,映射到项目中。这个代码是从代码实体类到数据库(C#+sqlite),其他语言和数据库可用来参考

    SQLite Expert 非常好用的SQLite管理软件(含注册)

    表列,索引,外键,触发器,独特和检查约束的可视化编辑器。 任何复杂的表,而不会丢失数据重组。 任何重组操作被包裹在一个嵌套事务回滚,如果发生任何错误更改应用时。 导入和导出数据 从ADO数据源,CSV...

    SQLiteExpert 带破解

    -表列,索引,外键,触发器,独特和检查约束的可视化编辑器。 -任何复杂的表,而不会丢失数据重组。 -任何重组操作被包裹在一个嵌套事务回滚,如果发生任何错误更改应用时。 导入和导出数据: -从ADO数据源,CSV...

    SQLite 中文帮助pdf

    select * from sqlite_temp_master --获取临时表集合 select * from sqlite_master --获取表集合 select CURRENT_DATE --获取电脑日期 select sqlite_version()--获取SQLite的版本号 PRAGMA table_info('keys');--...

    SQLite Expert Professional 3[3.5.39.2459] 最新版

    表列,索引,外键,触发器,独特和检查约束的可视化编辑器。 任何复杂的表,而不会丢失数据重组。 任何重组操作被包裹在一个嵌套事务回滚,如果发生任何错误更改应用时。 导入和导出数据 从ADO数据源,CSV文件...

    SQLiteExpertPro5.2.3_x64.rar

    -表列,索引,外键,触发器,独特和检查约束的可视化编辑器。 -任何复杂的表,而不会丢失数据重组。 -任何重组操作被包裹在一个嵌套事务回滚,如果发生任何错误更改应用时。 导入和导出数据: -从ADO数据源,CSV...

    SQLite数据库管理工具 SQLiteExpert免费官方正版

    表列,索引,外键,触发器,独特和检查约束的可视化编辑器。 任何复杂的表,而不会丢失数据重组。 任何重组操作被包裹在一个嵌套事务回滚,如果发生任何错误更改应用时。 导入和导出数据 从ADO数据源,CSV...

    Adminers MYSQL/SQLite管理工具.rar

    具体的Mysql/SQLite管理功能如下:  1:创建,修改,删除索引/外键/视图/存储过程和函数。  2:查询,合计,排序数据。  3:新增/修改/删除记录。  4:支持所有数据类型,包括大字段。  5:能够批量...

    sqlite3-3.26.0.zip

    这是 SQLite3 V3.26.0 的重新编译版本,加入了默认外键约束,使用时无需手动打开即可支持外键约束。

    asorm1.0beta android Sqlite3 ORM 框架;

    asorm1.0beta android Sqlite3 ORM 框架; 一个安卓上面的数据sql与对象之间的映射框架; 支持自动事务,自动创建表;不支持外键,存储过程;

    将外键助手添加到迁移中,并将外键正确地转储到schema.rb-Ruby开发

    支持以下适配器:mysql2 postgres sqlite(f Foreigner Foreigner为迁移添加和删除外键约束引入了一些方法。它还将外键转储到schema.rb。支持以下适配器:mysql2 postgres sqlite(外键方法是无操作的)在Rails 4.2...

    CRUD_EF_SqLite_Migrations:使用实体框架,SqLite,迁移在C#中进行分类

    CRUD_EF_SqLite_Migrations 使用Entity Framework,SqLite,Migrations迁移C#。移民创建一个迁移。 生成可以更新数据库的代码,以使其与模型的一组更改同步。 更新数据库。 应用挂起的迁移以更新数据库架构。 ...

    AndroidSqlite数据库操作封装SQLiteUtils.zip

    缺点和不足:还没支持多对一或者一多的关系没支持联合主键没支持表的外键设计其他...实例:SqliteLookup(Android内查看Sqlite数据库利器): https://github.com/YeDaxia/SqliteLookup 标签:SQLiteUtils

    Sqlite2CoreData:将标准 Sqlite 数据库迁移到核心数据的工具

    外键信息用于生成关系信息。 有关更多详细信息和示例用法,请访问 这是什么? 命令行工具 Xcdatamodeld 生成器 数据迁移器到与核心数据兼容的 Sqlite 它不是什么? 核心数据版本更新器 核心数据替换 核心数据...

    swift-用于sqlite数据库差量升级

    用于sqlite数据库差量升级。支持的升级能力包括 1,新建表、删除表、重命名表 2,新增字段(新增的字段只支持如下约束:default、pk、not null) 3,新建索引、删除索引 不过还不支持有外键约束存在的升级。

    mysql2sqlite:使用 php、pdo 或 exe 将 mysql 数据库转换为 sqlite 数据库-开源

    包括两个项目:A) 一个 php 脚本 B) 一个基于 python 脚本的 Windows 可执行文件 转换数据、索引和外键约束。 关于 php 脚本:-不需要很多内存-良好的速度 预计在 VDS/VPS 上使用。 用法:wget ...

Global site tag (gtag.js) - Google Analytics