PostgreSQL: ON DELETE Actions for Foreign Keys

Understand ON DELETE actions (CASCADE, RESTRICT, SET NULL, SET DEFAULT) for foreign keys in PostgreSQL and how they maintain referential integrity.

Understanding and Implementing ON DELETE Actions in PostgreSQL Foreign Keys

Introduction to ON DELETE Actions

In relational databases like PostgreSQL, foreign keys maintain referential integrity between tables. When you define a foreign key constraint, you also specify an `ON DELETE` action defining what happens to the related rows in a child table when a corresponding row in the parent table is deleted. This ensures data consistency and prevents orphaned records. This is a crucial aspect of database design.

`ON DELETE` Actions in PostgreSQL

PostgreSQL provides several `ON DELETE` actions:

Example: `ON DELETE SET NULL`

This example demonstrates creating and populating tables with a foreign key constraint using the `SET NULL` action. The `SET NULL` action sets the foreign key column in the `Sector` table (the child table) to `NULL` when the corresponding row in the `Staff` table (the parent table) is deleted. This shows how `SET NULL` handles the deletion of parent rows, setting corresponding foreign keys to NULL.

DROP TABLE IF EXISTS Staff;
DROP TABLE IF EXISTS Sector;

CREATE TABLE Staff (
    Staff_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Staff_name VARCHAR(50) NOT NULL
);

CREATE TABLE Sector (
    Sector_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Staff_id INT REFERENCES Staff(Staff_id) ON DELETE SET NULL,
    Sector_name VARCHAR(200) NOT NULL
);

INSERT INTO Staff (Staff_name) VALUES ('Sophia Smith'), ('Jessica Williams'), ('Victoria Lopez');
INSERT INTO Sector (Staff_id, Sector_name) VALUES (1, 'Nursing'), (1, 'Nursing'), (2, 'Medication'), (2, 'Medication'), (3, 'Accounting');

DELETE FROM Staff WHERE Staff_id = 2;

After this delete, the `Staff_id` in the `Sector` table will be `NULL` for the rows where `Staff_id` was 2.

Example: `ON DELETE CASCADE`

This example demonstrates the `CASCADE` action. With `ON DELETE CASCADE`, deleting a parent row automatically deletes all related child rows. This example shows how to create tables with a foreign key constraint that uses the `CASCADE` action. It shows how the `CASCADE` action automatically removes dependent rows when a parent row is deleted.

DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Department;

CREATE TABLE Employee (
    Employee_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Employee_name VARCHAR(50) NOT NULL
);

CREATE TABLE Department (
    Department_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Employee_id INT REFERENCES Employee(Employee_id) ON DELETE CASCADE,
    Department_name VARCHAR(200) NOT NULL
);

INSERT INTO Employee (Employee_name) VALUES ('John Smith'), ('Michael Brown');
INSERT INTO Department (Employee_id, Department_name) VALUES (1, 'Accounting'), (1, 'Accounting'), (2, 'Human Resource'), (2, 'Human Resource');

DELETE FROM Employee WHERE Employee_id = 2;

The corresponding rows in the `Department` table will also be deleted automatically because of the `ON DELETE CASCADE` constraint.

Conclusion

Understanding `ON DELETE` actions is crucial for maintaining database integrity. The choice of action (`NO ACTION`, `RESTRICT`, `SET NULL`, `SET DEFAULT`, `CASCADE`) depends on the specific requirements of your database design and how you want your application to handle deletion of data.