SQLite: Foreign Keys with Set Null on Delete

This SQLite post explains how to use Foreign Keys with "set null on delete" in SQLite with syntax and examples.

What is a Foreign Key with "Set Null on Delete" in SQLite?

A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null. The records in the child table will not be deleted.

A foreign key with a "set null on delete" can only be defined in a CREATE TABLE statement.

How to Create a Foreign Key with "Set Null on Delete" using a CREATE TABLE statement

Syntax

The syntax for creating a foreign key with "set null on 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 SET NULL
);

Example

Let's look at an example of how to create a foreign key with "set null on 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 SET NULL
);

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 ON DELETE SET NULL, when a record in the departments table is deleted, all corresponding records in the employees table will have the department_id set to NULL.

How to Add a Foreign Key with "Set NULL on Delete" to an Existing Table

You can not use the ALTER TABLE statement to add a foreign key with "set null on 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 "set null on 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 SET NULL
);

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 ON DELETE SET NULL 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 SET NULL
);

INSERT INTO employees SELECT * FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

In this example, we've created a foreign key (with set null on delete) called fk_departments that references the departments table based on the department_id field.

Now, let's demonstrate how the ON DELETE SET NULL 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 set null on delete) will update the employees table and change all the department_id values to NULL where the department_id is 30.

After the delete, the employees table will look like this: