How to Use ON DELETE SET NULL in PostgreSQL Foreign Keys

In PostgreSQL, use ON DELETE SET NULL in a foreign key constraint to automatically set the referencing column to NULL when the referenced row is deleted. This helps keep data consistent without deleting the referencing row.

Syntax

The ON DELETE SET NULL clause is part of a foreign key constraint definition. It tells PostgreSQL to set the foreign key column to NULL if the referenced row is deleted.

Syntax parts:

• FOREIGN KEY (column_name): The column in the child table referencing the parent table.
• REFERENCES parent_table (parent_column): The parent table and column being referenced.
• ON DELETE SET NULL: Action to set the foreign key column to NULL on delete.

sql

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column)
ON DELETE SET NULL;

Example

This example shows two tables: departments and employees. The employees table has a foreign key to departments. When a department is deleted, the department_id in employees is set to NULL instead of deleting the employee.

sql

CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INT,
  CONSTRAINT fk_department
    FOREIGN KEY (department_id)
    REFERENCES departments (id)
    ON DELETE SET NULL
);

INSERT INTO departments (name) VALUES ('HR'), ('IT');
INSERT INTO employees (name, department_id) VALUES ('Alice', 1), ('Bob', 2);

-- Delete department with id 1
DELETE FROM departments WHERE id = 1;

-- Check employees after delete
SELECT * FROM employees ORDER BY id;

Output

 id | name  | department_id 
----+-------+---------------
  1 | Alice |          NULL
  2 | Bob   |             2

Common Pitfalls

Common mistakes when using ON DELETE SET NULL include:

• Not allowing the foreign key column to be NULL. The column must allow NULL values or the action will fail.
• Expecting the referencing row to be deleted automatically. ON DELETE SET NULL only sets the column to NULL, it does not delete the row.
• Forgetting to add the foreign key constraint or misspelling table/column names.

sql

/* Wrong: foreign key column NOT NULL */
CREATE TABLE employees_wrong (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INT NOT NULL,
  CONSTRAINT fk_department_wrong
    FOREIGN KEY (department_id)
    REFERENCES departments (id)
    ON DELETE SET NULL
);

/* Right: foreign key column allows NULL */
CREATE TABLE employees_right (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INT,
  CONSTRAINT fk_department_right
    FOREIGN KEY (department_id)
    REFERENCES departments (id)
    ON DELETE SET NULL
);

Quick Reference

Clause	Description
FOREIGN KEY (column)	Defines the foreign key column in the child table.
REFERENCES parent_table (column)	Specifies the parent table and column referenced.
ON DELETE SET NULL	Sets the foreign key column to NULL when the referenced row is deleted.
Column NULLABLE	Foreign key column must allow NULL values for this to work.

Key Takeaways

Use ON DELETE SET NULL in foreign key constraints to set referencing columns to NULL on delete.

Ensure the foreign key column allows NULL values to avoid errors.

This action keeps referencing rows but removes their link to deleted parent rows.

ON DELETE SET NULL does not delete referencing rows, only nullifies the foreign key.

Always test foreign key behavior with sample data to confirm expected results.