How to add foreign keys to an existing SQLite table?

CREATE TABLE child( 
 id INTEGER PRIMARY KEY, 
 parent_id INTEGER, 
 description TEXT);

ALTER TABLE child ADD CONSTRAINT fk_child_parent
 FOREIGN KEY (parent_id) 
 REFERENCES parent(id);

CREATE TABLE child (
 id INTEGER PRIMARY KEY, 
 parent_id INTEGER, 
 description TEXT,
 FOREIGN KEY (parent_id) REFERENCES parent(id)
 >);

I have the following table:

CREATE TABLE child( 
 id INTEGER PRIMARY KEY, 
 parent_id INTEGER, 
 description TEXT);

How to add foreign key constraints on parent_id? Assume that foreign keys are enabled.

Most examples assume that you are creating a table-I want to add constraints to an existing table.

You can’t.

Although the SQL-92 syntax for adding foreign keys to a table is as follows:

ALTER TABLE child ADD CONSTRAINT fk_child_parent
 FOREIGN KEY (parent_id) 
 REFERENCES parent(id);

SQLite does not support the ADD CONSTRAINT variant of the ALTER TABLE command (sqlite.org: SQL Features That SQLite Does Not Implement).< /p>

Therefore, the only way to add foreign keys 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)
);

Unfortunate Yes, you must save the existing data to a temporary table, delete the old table, create a new table with FK constraints, and then copy the data from the temporary table. (sqlite.org – FAQ: Q11)

< p>