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

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, value text,province text,CONSTRAINT province FOREIGN KEY(province) REFERENCES province(valuekey) ON DELETE CASCADE ON UPDATE CASCADE)" ]];

再执行

哈哈,这下成功了