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);
@H_162_403@}
@H_162_403@
@H_162_403@
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.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
[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)"
]];
再执行
哈哈,这下成功了
