Normally if you need to drop a foreign key in SQL, you’d use the ALTER TABLE statement. But if you’re using SQLite, that’s not an option.

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 drop a foreign key like you can in other RDBMSs

The recommended way to “drop” a foreign key in SQLite is actually to transfer the data to a new table without a foreign key (or with a different one, if that’s what you need).

The Recommended Way

The SQLite documentation recommends a 12 step process for making schema changes to a table. We’ll use that process to “drop” a foreign key in the following example.

Create a Table with a Foreign Key

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

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

CREATE TABLE Pets( 
    PetId INTEGER PRIMARY KEY, 
    PetName,
    TypeId,
    FOREIGN KEY(TypeId) REFERENCES Types(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 the first one (Types) has the primary key and the other (Pets) has the foreign key. The foreign key was added on the last line of the second table.

We can verify that the foreign key was created by running the following command:

PRAGMA foreign_key_list(Pets);

Result:

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

We can see the details of the foreign key constraint.

Now let’s “drop” the foreign key.

“Drop” the Foreign Key

The following code “drops” the foreign key by creating a new table without 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
);

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.

PRAGMA foreign_key_list(Pets);

Result:

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

You can use the same method to add a foreign key to an existing table.

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
);

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.