SQLite: Foreign Keys with Cascade Delete
This SQLite tutorial explains how to use Foreign Keys with cascade delete in SQLite with syntax and examples.
What is a Foreign Dey with Cascade Delete in SQLite?
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQLite.
A foreign key with a cascade delete can only be defined in a CREATE TABLE statement.
How to Create a Foreign Key with Cascade Delete using a CREATE TABLE statement
Syntax
The syntax for creating a foreign key with cascade delete using a CREATE TABLE statement in SQLite is:
CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... CONSTRAINT fk_column FOREIGN KEY (column1, column2, ... column_n) REFERENCES parent_table (column1, column2, ... column_n) ON DELETE CASCADE );
Example
Let's look at an example of how to create a foreign key with cascade delete using the CREATE TABLE statement in SQLite.
For example:
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) ON DELETE CASCADE );
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.
Because of the cascade delete, when a record in the departments table is deleted, all records in the employees table will also be deleted that have the same department_id value.
How to Add a Foreign Key with Cascade Delete to an Existing Table
You can not use the ALTER TABLE statement to add a foreign key with cascade delete in SQLite. Instead you will need to rename the table, create a new table with the foreign key, and then copy the data into the new table.
Syntax
The syntax to add a foreign key with cascade delete to an existing table 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 ], ... CONSTRAINT fk_column FOREIGN KEY (column1, column2, ... column_n) REFERENCES parent_table (column1, column2, ... column_n) ON DELETE CASCADE ); INSERT INTO table1 SELECT * FROM _table1_old; COMMIT; PRAGMA foreign_keys=on;
Example
First, let's start by creating our 2 tables (departments and employees):
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 );
Next, let's add some data to these tables:
INSERT INTO departments VALUES (30, 'HR'); INSERT INTO departments VALUES (999, 'Sales'); INSERT INTO employees VALUES (10000, 'Smith', 'John', 30); INSERT INTO employees VALUES (10001, 'Anderson', 'Dave', 999);
Now, let's add a foreign key with cascade delete to the employees table:
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, CONSTRAINT fk_departments FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE ); INSERT INTO employees SELECT * FROM _employees_old; COMMIT; PRAGMA foreign_keys=on;
In this example, we've created a foreign key (with cascade delete) called fk_departments that references the departments table based on the department_id field.
Now, let's demonstrate how the cascade delete works. Currently, we have the following records in the employees table:
employee_id | last_name | first_name | department_id |
---|---|---|---|
10000 | Smith | John | 30 |
10001 | Anderson | Dave | 999 |
Now let's delete one of the records from the departments table and see what happens:
DELETE FROM departments WHERE department_id = 30;
Even though we are deleting the record from the departments table where the department_id is 30, the foreign key (with cascade delete) will remove all records from the employees where the department_id is 30 as well.
After the cascade delete, the employees table will look like this:
employee_id | last_name | first_name | department_id |
---|---|---|---|
10001 | Anderson | Dave | 999 |
Advertisements