1.创建外键
2013-10-31 11:59:25.384 testDatabase[23225:70b] DB Query: DELETE FROM province WHERE valuekey=10102000
2013-10-31 11:59:25.384 testDatabase[23225:70b] Unknown error finalizing or resetting statement (19: foreign key constraint failed)
CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
打开外键支持
PRAGMA foreign_keys = ON;
这一端是拷贝的别人的,不过经过我测试发现,不需要这么麻烦的,只需要执行一句就可以了
NSString* dbPath = [(NSArray*)NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
dbPath = [dbPath stringByAppendingPathComponent:@"test.db"];
db = [FMDatabase databaseWithPath:dbPath];
if ([db open]) {
NSLog(@"Database %@ opened", dbPath);
//check for foreign_key
NSString* sql = @"PRAGMA foreign_keys";
FMResultSet *rs = [db executeQuery:sql];
int enabled;
if ([rs next]) {
enabled = [rs intForColumnIndex:0];
}
[rs close];
if (!enabled) {
// enable foreign_key
sql = @"PRAGMA foreign_keys = ON;";
[db executeUpdate:sql];
// check if successful
sql = @"PRAGMA foreign_keys";
FMResultSet *rs = [db executeQuery:sql];
if ([rs next]) {
enabled = [rs intForColumnIndex:0];
}
[rs close];
}
// do your stuff here, or just cache the connection
} else {
NSLog(@"Failed to open %@", dbPath);
}
table1 is the parent table having id1 as primary key.
CREATE TABLE "table1" ("id1" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)
table2 is the child table having id2 as a foreign key with reference to id1 of table1.
CREATE TABLE table2 (
id2 INTEGER,
parent_id INTEGER,
description TEXT,
FOREIGN KEY (id2) REFERENCES table1(id1)
)
Foreign keys are disabled by default. You have to enable them separately for each connection. The setting isn't "sticky". You have to do this every time you connect to a SQLite database. PRAGMA foreign_keys = ON;
在它下面看到
CREATE TABLE artist(
artistid INTEGER PRIMARY KEY,
artistname TEXT
)
CREATE TABLE "track" (
"trackid" INTEGER PRIMARY KEY AUTOINCREMENT,
"trackname" TEXT,
"trackartist" INTEGER,
CONSTRAINT "trackartist" FOREIGN KEY ("trackartist") REFERENCES "artist" ("artistid") ON DELETE CASCADE ON UPDATE CASCADE)
作者也使用的是FMDB这个库
当我自己在使用的时候,原本以为很简单,但是经过测试发现,当使用外键后,进行删除操作,总是会提示错误
使用的方式如下:
[db
executeUpdate
:[
NSString
stringWithFormat
:
@"create table provincecity (valuekey text PRIMARY KEY, value text,province text,CONSTRAINT province FOREIGN KEY(province) REFERENCES province(valuekey) )"
]];
删除使用的是
[db
executeUpdate
:[
NSString
stringWithFormat
:
@"DELETE FROM province WHERE valuekey=10102000"
]];
会出现错误提示:
2013-10-31 11:59:25.380 testDatabase[23225:70b] Unknown error calling sqlite3_step (19: foreign key constraint failed) eu2013-10-31 11:59:25.384 testDatabase[23225:70b] DB Query: DELETE FROM province WHERE valuekey=10102000
2013-10-31 11:59:25.384 testDatabase[23225:70b] Unknown error finalizing or resetting statement (19: foreign key constraint failed)
2013-10-31 11:59:25.385 testDatabase[23225:70b] DB Query: DELETE FROM province WHERE valuekey=10102000
看到一篇文章说,打开外键需要执行一大堆代码,其实在ios中,只需要执行下面一句就可以打开外键支持了
[db
executeUpdate
:
@"PRAGMA foreign_keys=ON;
”
];
但是问题依旧......
那文章最后说 ,要把删除的语句 换成使用
executeQuery,即
[db
executeQuery
:[
NSString
stringWithFormat
:
@"DELETE FROM province WHERE valuekey=10102000"
]];
虽然不提示错误了,不过根本就没有删除成功啊,不仅主表中没有删除,含有外键的子表也没有删除......
看到上面那段,抱着试试看的心理,进行同样的设置
[db
executeUpdate
:[
NSString
stringWithFormat
:
@"create table provincecity (valuekey text PRIMARY KEY, value text,province text,CONSTRAINT province FOREIGN KEY(province) REFERENCES province(valuekey) ON DELETE CASCADE ON UPDATE CASCADE)"
]];
再执行
哈哈,这下成功了