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)

Hallerson

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

Jannjanna

Answer 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.

Coheman

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

Berretta

Answer 4

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

Beaird

Answer 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.

Homogeny

Why would anyone want a UNIQUE constraint on a foreign key? Usually in a 1:N relationship you place the foreign key column into the N entity's table to refer to the parent 1 entity. Therefore there will be many rows in the N table which will refer to the same 1 row. The UNIQUE constraint wouldn't prevent that? Am I misunderstanding something? – Sibie

The last example of sqlite.org/foreignkeys.html#fk_indexes shows the REFERENCES shorthand and it does not specify the parent column just the parent table (I guess the system deducts the column to be the primary key of the parent table). However it does need an explicit index creation statement. – Sibie

Sorry, I didn't mean you should or could combine UNIQUE and REFERENCES. You can only pick one or the other, not both. As for your second question, yes, the columns are deducted unless 'The child table references the primary key of the parent without specifying the primary key columns and the number of primary key columns in the parent do not match the number of child key columns.'. – Homogeny