sqlite语句,关于使用FMDB操作带有外键的数据库

发布时间：2020-12-12 20:07:14 所属栏目：百科来源：网络整理

导读：1.创建外键 CREATE TABLE track( trackid INTEGER,trackname TEXT,trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid) ); 打开外键支持 PRAGMA foreign_keys = ON; 这一端是拷贝的别人的,不过经过我测试发现,不需要这么麻烦的,只需

1.创建外键

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)

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

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) 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
看到一篇文章说,打开外键需要执行一大堆代码,其实在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,CONSTRAINT province FOREIGN KEY(province) REFERENCES province(valuekey) ON DELETE CASCADE ON UPDATE CASCADE)" ]];

再执行哈哈,这下成功了

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!