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
Solution 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.
Related videos on Youtube
![Geeks On Hugs](https://i.stack.imgur.com/HAp8K.jpg?s=256&g=1)
Geeks On Hugs
Android (Java) developer, formerly Widows (Delphi) developer. App Development: http://www.geeksonhugs.com GDG Research Triangle: http://plus.google.com/u/1/105162452149208177718/ Google+ Professional: http://plus.google.com/u/1/107682425502499671408/ Google+ Personal: http://plus.google.com/102461733163444490339/posts Twitter: http://twitter.com/GeeksOnHugs LinkedIn: http://www.linkedin.com/in/anthonytanas Facebook: http://www.facebook.com/anthony.tanas
Updated on July 13, 2020Comments
-
Geeks On Hugs almost 4 years
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)
-
Csaba Toth almost 4 yearsWhy 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?
-
Csaba Toth almost 4 yearsThe 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.
-
szmate1618 almost 4 yearsSorry, I didn't mean you should or could combine
UNIQUE
andREFERENCES
. 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.'.