android Sqlite 外键默认是关闭的,需要在SQLiteOpenHelper的子类里面打开,代码如下
@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
if (!db.isReadOnly()) {
// Enable foreign key constraints 开启外键约束
db.execSQL("PRAGMA foreign_keys=ON;");
}
}
1介绍外键约束
SQL外键约束用于强制表之间的“存在”关系。例如，考虑使用以下SQL命令创建的数据库模式
CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT);
CREATE TABLE track(trackid INTEGER,trackname TEXT,trackartist INTEGER -- Must map to an artist.artistid!);
使用此数据库的应用程序有权假定跟踪表中的每一行都有一个对应的行。毕竟，声明中的评论是这样说的。不幸的是，如果用户使用外部工具编辑数据库，或者应用程序中存在bug，则行可能插入到不符合artist表中任何行的跟踪表中。或者从artist表中删除行，在track表中留下不符合artist中剩余行的孤立行。这可能导致应用程序或应用程序稍后出现故障，或者至少使应用程序编码更加困难。
一种解决方案是在数据库模式中添加一个SQL外键约束，以强制artist和track之间的关系。为此，可以通过将track的声明修改为以下来添加外键定义：
CREATE TABLE track(trackid INTEGER,trackname TEXT,trackartist INTEGER,
FOREIGN KEY(trackartist) REFERENCES artist(artistid));
通过这种方式,Sqlite被强制添加约束,当尝试向track表插入一条和artist表中没有关联的数据时候会失败.当从artist表中删除一条和track表有关联的数据会触发异常(Foreign Key Constraint Exception).
sqlite> SELECT * FROM artist;
artistid artistname
-------- -----------------
1 Dean Martin
2 Frank Sinatra
sqlite> SELECT * FROM track;
trackid trackname trackartist
------- ----------------- -----------
11 That's Amore 1
12 Christmas Blues 1
13 My Way 2
sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
SQL error: foreign key constraint failed
//track表中trackartist=3有问题,track(trackartist) 关联artist(artistid),artistid只有俩条数据
//(artistid=1和artistid=2) ，应该首先给artist 插入一条artistid=3的数据就没问题了.如下
INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
正如所愿,不能通过删除／更新artist表中和artist表有关联的数据
DELETE FROM artist WHERE artistname = 'Frank Sinatra';
SQL error: foreign key constraint failed
artist表artistname = 'Frank Sinatra'这个数据和track表trackname = 'My Way'存在关联,不可以删除,如果想删除这条数据首先删除track表中关联的数据,在执行这个删除命令即可如下。
DELETE FROM track WHERE trackname = 'My Way';
DELETE FROM artist WHERE artistname = 'Frank Sinatra';
UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
SQL error: foreign key constraint failed
artist表中artistname = 'Dean Martin'数据和track表有关联,如果想删除,如下
DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
2.ON DELETE and ON UPDATE操作
外建On Delete和On Update 有五种配置类型,No Action/Restrict/Set Null/Set Default/Cascade默认是No Action
no action. 父表删除或者更新(外建所关联的数据库字段)时候,会报foreign key constrain错误.
restrict.和no action类似
set null.父表删除或者更新(外建所关联的数据库字段)时候,子表foreign key关联的列数据重置为null.
set default.与set null类似,父表删除或者更新(外建所关联的数据库字段)时候,子表foreign key关联的列数据重置为创建表时该列的default value.
cascade.父表与子表关联,删除夫表会把子表里面与父表外建关联的数据都删除,更新父表里数据(被子表关联的foreign key列)会同步更新子表外建列的值。
如下
CREATE TABLE artist(artistid INTEGER PRIMARY KEY,artistname TEXT);
CREATE TABLE track(trackid INTEGER,trackname TEXT,trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE);
sqlite> SELECT * FROM artist;
artistid artistname
-------- -----------------
1 Dean Martin
2 Frank Sinatra
sqlite> SELECT * FROM track;
trackid trackname trackartist
------- -------------- -----------
11 That's Amore 1
12 Christmas Blues 1
13 My Way 2
//track表和artist表关联外建使用ON UPDATE CASCADE,修改夫表,会同步修改子表所有与父表关联列的值
sqlite> UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin';
sqlite> SELECT * FROM artist;
artistid artistname
-------- -----------------
2 Frank Sinatra
100 Dean Martin
sqlite> SELECT * FROM track;
trackid trackname trackartist
------- ----------------- -----------
11 That's Amore 100
12 Christmas Blues 100
13 My Way 2
使用On Update或On Delete,如果配置ON DELETE SET DEFAULT,删除夫表数据(与子表有关联的数据),子表foreign key数据会重置为创建表时候default value.如果default value在夫表没有关联,报foreign key constraint failed错误。如下
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);
sqlite> SELECT * FROM artist;
artistid artistname
-------- -----------------
3 Sammy Davis Jr.
sqlite> SELECT * FROM track;
trackid trackname trackartist
------- --------------- -----------
14 Mr. Bojangles 3
//删除父表数据,子表trackartist=0,没有与父表匹配报SQL error: foreign key constraint failed
sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
如下修改,在夫表插入一条default的数据即可
sqlite> INSERT INTO artist VALUES(0, 'Unknown Artist');
sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
