How to Add a Foreign Key to an Existing SQLite Table

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)

Insert Foreign Key with two tables containing the same columns in SQLite with Python

What you need is an UPDATE statement and not INSERT.

You must update the table table_post after you transfer the CSV files to the SQLite database:

UPDATE table_post
SET profile_ID = (SELECT profile.profile_ID FROM profile WHERE profile.profileUrl = table_post.profileUrl)

If your version of SQLite is 3.33.0+ you can use the UPDATE...FROM syntax:

UPDATE table_post AS t
SET profile_ID = p.profile_ID 
FROM profile AS p
WHERE p.profileUrl = t.profileUrl

How to add a foreign key for a table that is already created?

Short answer: you can't.

This is documented as part of the SQL Features That SQLite Does Not Implement:

Only the RENAME TABLE, ADD COLUMN, and RENAME COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

Bold emphasis is mine.

So basically you would need to create the constraint at the time when the table is created. For your use case, one solution would be to:

• create a new table (with the foreign key constraint)
• copy the data from the old table
• drop the old table (or better yet, rename it, so you have a backup)
• rename the new table

In SQLite, how do I alter a table to add a column with default null and foreign key constraint?

DEFAULT NULL is the default; you do not need to specifiy it.

FOREIGN KEY is used to introduce a table constraint, but ALTER TABLE supports only column constraints. The syntax for a column constraint does not have the FOREIGN KEY:

ALTER TABLE Customers ADD AddressID INTEGER REFERENCES MasterAddress(AddressID);

---

Related Topics