SQLite: Drop a Foreign Key
This SQLite tutorial 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.
Advertisements