SQLite Foreign Key
Solution 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:
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:
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:
Put the FOREIGN KEY definition at the end of the SQL statement