SQLite: Drop a Foreign Key

This SQLite post explains how to drop a foreign key in SQLite with syntax and examples.

Description

Once a foreign key has been created, you may find that you wish to drop the foreign key from the table. Let's explore how to do this.

How to Drop a Foreign Key on a Table

You can not use the ALTER TABLE statement to drop a foreign key in SQLite. Instead you will need to rename the table, create a new table without the foreign key, and then copy the data into the new table.

Syntax

The syntax to drop a foreign key in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table1 SELECT * FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

Example

If you had created a foreign key in SQLite as follows:

CREATE TABLE departments
( department_id INTEGER PRIMARY KEY AUTOINCREMENT,
  department_name VARCHAR
);

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  department_id INTEGER,
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

In this example, we've created a primary key on the departments table that consists of only one field - the department_id field. Then we've created a foreign key called fk_departments on the employees table that references the departments table based on the department_id field.

If we then wanted to drop the foreign key called fk_departments, we could execute the following command:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO _employees_old;

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  department_id INTEGER
);

INSERT INTO employees SELECT * FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

This example will rename our existing employees table to _employees_old. Then it will create the new employees table without a foreign key. Then it will insert all of the data from the _employees_old table into the employees table.

This workaround allows you to drop a foreign key from the employees table without losing the data in the table.