1.创建外键

CREATE TABLE track(
 trackid INTEGER,trackname TEXT,trackartist INTEGER,FOREIGN KEY(trackartist) REFERENCES artist(artistid)

);
@H_301_12@

@H_301_12@

打开外键支持 @H_301_12@

PRAGMA foreign_keys = ON; 这一端是拷贝的别人的,不过经过我测试发现,不需要这么麻烦的,只需要执行一句就可以了 @H_301_12@

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);

@H_695_403@}
@H_301_12@

@H_695_403@
@H_301_12@

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)

)
@H_301_12@

在网上看到的一段对于外键的说明: http://s6453.socode.info/question/5081350e4f1eba38a42cbbbc @H_695_403@ @H_301_12@

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;

在它下面看到 @H_301_12@ CREATE TABLE artist(

artistid INTEGER PRIMARY KEY,@H_301_12@

artistname TEXT @H_301_12@

) @H_301_12@

@H_301_12@

CREATE TABLE "track" ( @H_301_12@

"trackid" INTEGER PRIMARY KEY AUTOINCREMENT,@H_301_12@

"trackname" TEXT,@H_301_12@

"trackartist" INTEGER,@H_301_12@

CONSTRAINT "trackartist" FOREIGN KEY ("trackartist") REFERENCES "artist" ("artistid") ON DELETE CASCADE ON UPDATE CASCADE) @H_301_12@

@H_301_12@

作者也使用的是FMDB这个库 @H_301_12@

@H_301_12@

当我自己在使用的时候,原本以为很简单,但是经过测试发现,当使用外键后,进行删除操作,总是会提示错误 @H_301_12@

使用的方式如下: @H_301_12@

[db executeUpdate :[ NSString stringWithFormat : @"create table provincecity (valuekey text PRIMARY KEY,value text,province text,CONSTRAINT province FOREIGN KEY(province) REFERENCES province(valuekey) )" ]]; @H_301_12@

@H_301_12@

删除使用的是 @H_301_12@

[db executeUpdate :[ NSString stringWithFormat : @"DELETE FROM province WHERE valuekey=10102000" ]]; @H_301_12@

@H_301_12@

会出现错误提示: @H_301_12@ 2013-10-31 11:59:25.380 testDatabase[23225:70b] Unknown error calling sqlite3_step (19: foreign key constraint Failed) eu
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)

2013-10-31 11:59:25.385 testDatabase[23225:70b] DB Query: DELETE FROM province WHERE valuekey=10102000 @H_301_12@

@H_301_12@

看到一篇文章说,打开外键需要执行一大堆代码,其实在ios中,只需要执行下面一句就可以打开外键支持了 @H_301_12@

[db executeUpdate : @"PRAGMA foreign_keys=ON; ” ]; @H_301_12@

但是问题依旧...... @H_301_12@

那文章最后说,要把删除的语句换成使用 executeQuery,即 [db executeQuery :[ NSString stringWithFormat : @"DELETE FROM province WHERE valuekey=10102000" ]]; @H_301_12@

虽然不提示错误了,不过根本就没有删除成功啊,不仅主表中没有删除,含有外键的子表也没有删除...... @H_301_12@

@H_301_12@

看到上面那段,抱着试试看的心理,进行同样的设置
@H_301_12@

[db executeUpdate :[ NSString stringWithFormat : @"create table provincecity (valuekey text PRIMARY KEY,CONSTRAINT province FOREIGN KEY(province) REFERENCES province(valuekey) ON DELETE CASCADE ON UPDATE CASCADE)" ]];
@H_301_12@

@H_301_12@

再执行 @H_301_12@

哈哈,这下成功了 @H_301_12@