Android SQLite 外键

SQLite Foreign Key

提问by Geeks On Hugs

I'm following the instructions from the SQLite documentation at http://www.sqlite.org/foreignkeys.htmlhowever my attempt to add a foreign key is failing. Here are my create statements:

我正在按照http://www.sqlite.org/foreignkeys.html 上的 SQLite 文档中的说明进行操作，但是我尝试添加外键失败。这是我的创建语句：

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)

外键定义中的未知列“checklist_id”（代码 1）：，编译时：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)

回答by Scen

You still have to create the column checklist_id INTEGERbefore you add it as a Foreign key.

checklist_id INTEGER在将其添加为外键之前，您仍然必须创建该列。

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

回答by Simon Dorociak

Simply you are missing checklist_idcolumn in your itemtable. You need to declare it before you want to set it as FOREIGN KEY. You tried to create FKon non-existing column and this is reason why it doesn't work.

只是您checklist_id的项目表中缺少列。您需要先声明它，然后才能将其设置为FOREIGN KEY. 您试图FK在不存在的列上创建，这就是它不起作用的原因。

So you need to add this:

所以你需要添加这个：

checklist_id INTEGER,
FOREIGN KEY(checklist_id) REFERENCES checklist(_id)

now it should works.

现在它应该可以工作了。

回答by villager

You need to include the column name before you wrap it with FOREIGN KEY().

在使用 FOREIGN KEY() 将其包装之前，您需要包含列名。

CREATE TABLE 
    item (
        _id INTEGER PRIMARY KEY AUTOINCREMENT,  
        checklist_id INTEGER,
        FOREIGN KEY(checklist_id) REFERENCES checklist(_id), 
        item_text TEXT, item_hint TEXT, 
        item_order INTEGER, 
        created_on INTEGER, 
        modified_on INTEGER
    );

回答by rudakovsky

Put the FOREIGN KEY definition at the end of the SQL statement

将 FOREIGN KEY 定义放在 SQL 语句的末尾

回答by szmate1618

I think the above answers are not entirely correct, or at least slightly misleading. As they correctly pointed out, you can create the column, then on a separate line add a foreign key constraint. This is called specifying a table constraint.

我认为上述答案并不完全正确，或者至少有点误导。正如他们正确指出的那样，您可以创建列，然后在单独的行上添加外键约束。这称为指定表约束。

But there is also a shorter syntax, when applying only on 1 column, all 4 possible constraints (PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY) can also be specified inline (like NOT NULL, for example), as a column constraint. I.e. you can write:

但是还有一种更短的语法，当仅应用于 1 column 时，所有 4 个可能的约束（PRIMARY KEY、UNIQUE、CHECK、FOREIGN KEY）也可以内联（NOT NULL例如 ）指定为列约束。即你可以写：

CREATE TABLE 
item (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,  
    checklist_id REFERENCES checklist(_id), 
    item_text TEXT, item_hint TEXT, 
    item_order INTEGER, 
    created_on INTEGER, 
    modified_on INTEGER
);

By the way, if you are ever unsure about the correct syntax, the official documentationhas really nice railroad diagrams.

顺便说一句，如果您不确定正确的语法，官方文档中有非常好的铁路图。

回答by Chethana Dasanayaka

"FOREIGN KEY("+ArtistsMaster.PhotographDetails.COLUMN_ARTIST_ID+") REFERENCES "+ArtistsMaster.ArtistDetails.TABLE_NAME+"("+ArtistsMaster.ArtistDetails._ID+"))";