How do I add a foreign key to an existing sqlite (3.6.21) table?

Question

I have the following table:

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

How do I add a foreign key constraint on parent_id? Assume foreign keys are enabled.

Most examples assume you're creating the table - I'd like to add the constraint to an existing one.

Answer 1

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 child(id);

However sqlite still does not 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 child(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)