SQLite: Foreign Keys with Cascade Delete

This SQLite post 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, 'Mark', '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:

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: