SQLite外键 [英] SQLite Foreign Key
问题描述
我按照 http://www.sqlite.org/foreignkeys.html但是我尝试添加一个外键失败。这里是我的创建语句:
$ pre $ codeREATE TABLE $ b $ checklist(
_id INTEGER PRIMARY KEY AUTOINCREMENT,
checklist_title TEXT,
description TEXT,
created_on INTEGER,
modified_on INTEGER
);
CREATE TABLE
item(
_id INTEGER PRIMARY KEY AUTOINCREMENT,
FOREIGN KEY(checklist_id)REFERENCES checklist(_id),
item_text TEXT,item_hint TEXT ,
item_order INTEGER,
created_on INTEGER,
modified_on INTEGER
);
第一个表格没有问题。第二条语句发生错误。我已经尝试了在事务中包装这两个查询,而没有。这里是错误:
在外键定义(代码1)中未知的列checklist_id:,编译时:CREATE TABLE item(_id INTEGER PRIMARY KEY AUTOINCREMENT,FOREIGN KEY (checklist_id)REFERENCES checklist(_id),item_text TEXT,item_hint TEXT,item_order INTEGER,created_on INTEGER,modified_on INTEGER)
在将它添加为外键之前,仍然需要创建该列。
所以这将是:
<$ p $ b $ CREATE TABLE $ b $ checklist(
_id INTEGER PRIMARY KEY AUTOINCREMENT,
checklist_title TEXT,
description TEXT,
created_on INTEGER,
modified_on INTEGER
);
CREATE TABLE
item(
_id INTEGER PRIMARY KEY AUTOINCREMENT,
checklist_id INTEGER,
item_text TEXT,
item_hint TEXT,
item_order INTEGER,
created_on INTEGER,
modified_on INTEGER,
FOREIGN KEY(checklist_id)REFERENCES checklist(_id)
);
I'm following the instructions from the SQLite documentation at http://www.sqlite.org/foreignkeys.html however my attempt to add a foreign key is failing. Here are my create statements:
CREATE TABLE
checklist (
_id INTEGER PRIMARY KEY AUTOINCREMENT,
checklist_title TEXT,
description TEXT,
created_on INTEGER,
modified_on INTEGER
);
CREATE TABLE
item (
_id INTEGER PRIMARY KEY AUTOINCREMENT,
FOREIGN KEY(checklist_id) REFERENCES checklist(_id),
item_text TEXT, item_hint TEXT,
item_order INTEGER,
created_on INTEGER,
modified_on INTEGER
);
The first table is made fine. The error occurs in the second statement. I have tried both with wrapping the two queries in a transaction and without. Here is the error:
unknown column "checklist_id" in foreign key definition (code 1): , while compiling: CREATE TABLE item (_id INTEGER PRIMARY KEY AUTOINCREMENT, FOREIGN KEY(checklist_id) REFERENCES checklist(_id), item_text TEXT, item_hint TEXT, item_order INTEGER, created_on INTEGER, modified_on INTEGER)
You still have to create the column before you add it as a Foreign key.
So it would be:
CREATE TABLE
checklist (
_id INTEGER PRIMARY KEY AUTOINCREMENT,
checklist_title TEXT,
description TEXT,
created_on INTEGER,
modified_on INTEGER
);
CREATE TABLE
item (
_id INTEGER PRIMARY KEY AUTOINCREMENT,
checklist_id INTEGER,
item_text TEXT,
item_hint TEXT,
item_order INTEGER,
created_on INTEGER,
modified_on INTEGER,
FOREIGN KEY(checklist_id) REFERENCES checklist(_id)
);
这篇关于SQLite外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!