How do I add a foreign key to an existing SQLite table?
You can't.
Although the SQL-92 syntax to add a foreign key to your table would be as follows:
ALTER TABLE child ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id)
REFERENCES parent(id);
SQLite doesn't support the ADD CONSTRAINT
variant of the ALTER TABLE
command (sqlite.org: SQL Features That SQLite Does Not Implement).
Therefore, the only way to add a foreign key in sqlite 3.6.1 is during CREATE TABLE
as follows:
CREATE TABLE child (
id INTEGER PRIMARY KEY,
parent_id INTEGER,
description TEXT,
FOREIGN KEY (parent_id) REFERENCES parent(id)
);
Unfortunately you will have to save the existing data to a temporary table, drop the old table, create the new table with the FK constraint, then copy the data back in from the temporary table. (sqlite.org - FAQ: Q11)
You can add the constraint if you alter table and add the column that uses the constraint.
First, create table without the parent_id:
CREATE TABLE child(
id INTEGER PRIMARY KEY,
description TEXT);
Then, alter table:
ALTER TABLE child ADD COLUMN parent_id INTEGER REFERENCES parent(id);