'SQLite Foreign Key

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)

androidsqlsqliteforeign-key-relationshipcreate-table


Solution 1:[1]

You still have to create the column checklist_id INTEGER 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)
    );

Solution 2:[2]

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

So you need to add this:

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

now it should works.

Solution 3:[3]

You need to include the column name before you wrap it with 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
    );

Solution 4:[4]

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

Solution 5:[5]

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:

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 documentation has really nice railroad diagrams.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Anna Klein
Solution 2 Simon Dorociak
Solution 3 lorraine
Solution 4 rudakovsky
Solution 5

Related Questions