SQLite supports a very limited subset of the ALTER TABLE statement. The only things you can do with ALTER TABLE in SQLite is rename a table, rename a column within a table, or add a new column to an existing table.

In other words, you can’t use ALTER TABLE to add a foreign key to an existing table like you can in other database management systems.

Therefore, the only way you can “add” a foreign key to an existing table in SQLite is to create a new table with a foreign key, then transfer the data to the new table.

There’s more than one way to do this, but there is a recommended way.

The Recommended Way

The SQLite documentation recommends a 12 step process for making schema changes to a table.

For the purposes of this article, we’ll just deal with adding a foreign key.

To make it somewhat realistic, we’ll make sure the table already contains data.

Original Table Without Foreign Key

First, let’s create a table without a foreign key and populate it with data.

CREATE TABLE Types( 
    TypeId INTEGER PRIMARY KEY, 
    Type
);

CREATE TABLE Pets( 
    PetId INTEGER PRIMARY KEY, 
    PetName,
    TypeId
);

INSERT INTO Types VALUES 
    ( NULL, 'Dog' ),
    ( NULL, 'Cat' ),
    ( NULL, 'Parakeet' ),
    ( NULL, 'Hamster' );

INSERT INTO Pets VALUES 
    ( NULL, 'Brush', 3 ),
    ( NULL, 'Tweet', 3 ),
    ( NULL, 'Yelp', 1 ),
    ( NULL, 'Woofer', 1 ),
    ( NULL, 'Fluff', 2 );

Actually, here I created two tables and populated them with data. Two tables, because one (Types) will have the primary key and the other (Pets) will have the foreign key.

Notice that I didn’t create a foreign key.

We can verify that there are no foreign keys by running the following command:

PRAGMA foreign_key_list(Pets);

In my case, I get the following result:

(That’s blank because there are no foreign key constraints on this table.)

Now let’s “add” a foreign key.

Add Foreign Key

The following code adds a foreign key to our table by creating a new table with a foreign key constraint, transferring the data to that table, dropping the original table, then renaming the new table to the name of the original table.

PRAGMA foreign_keys = OFF;

BEGIN TRANSACTION;

CREATE TABLE Pets_new( 
    PetId INTEGER PRIMARY KEY, 
    PetName,
    TypeId,
    FOREIGN KEY(TypeId) REFERENCES Types(TypeId)
);

INSERT INTO Pets_new SELECT * FROM Pets;

DROP TABLE Pets;

ALTER TABLE Pets_new RENAME TO Pets;

COMMIT;

PRAGMA foreign_keys = ON;

Done.

If you need to reconstruct any indexes, triggers, or views, do that after the ALTER TABLE statement that renames the table (just prior to COMMIT).

Now let’s check the table for foreign key constraints again.

.mode line
PRAGMA foreign_key_list(Pets);

Result (using vertical output):

       id = 0
      seq = 0
    table = Types
     from = TypeId
       to = TypeId
on_update = NO ACTION
on_delete = NO ACTION
    match = NONE

This time we can see the details of the foreign key constraint.

Note that the first line of my command (.mode line) has got nothing to do with creating a foreign key. I’ve put it there solely to change the way my terminal outputs the result (so that you don’t have to scroll sideways to view the result).

An Alternative Method

When looking at the previous example, you might be thinking that there’s a more efficient way to do it. For example you could do it like this:

PRAGMA foreign_keys = OFF;

BEGIN TRANSACTION;

ALTER TABLE Pets RENAME TO Pets_old;

CREATE TABLE Pets( 
    PetId INTEGER PRIMARY KEY, 
    PetName,
    TypeId,
    FOREIGN KEY(TypeId) REFERENCES Types(TypeId)
);

INSERT INTO Pets SELECT * FROM Pets_old;

DROP TABLE Pets_old;

COMMIT;

PRAGMA foreign_keys = ON;

And it’s true. With my example, this method works just as well.

But this method also has the potential to corrupt references to the table in any existing triggers, views, and foreign key constraints.

So if your table already has existing triggers, views, or foreign key constraints, it’s probably safer to use the recommended method.