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