SQLite Foreign Key

Question

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)

user_8350033 · Answer

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.

SQLite Foreign Key

5 Answers