SQL Delete Cascade

The SQL ON DELETE CASCADE is a constraint that allows SQL to delete rows associated with a specific parent table when the parent table is deleted.

Using this article, we will discuss how to implement the delete on cascade constraint in SQL.

SQL Cascade Practical Example

Suppose we have two tables containing developer info and another developer’s contributions. The developer has multiple contributions.

Here, we want to create a relationship where all the contributions from the other table are automatically removed if the developer is removed from the table.

Let us start by creating the developers’ table:

CREATE TABLE developers(
dev_id serial PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
dev_language VARCHAR(255) NOT NULL
);

Next, let us create a contributions table as shown:

CREATE TABLE contrib(
repo_id serial PRIMARY KEY,
repo_name VARCHAR(100) NOT NULL,
commit_id INT NOT NULL,
dev_id INTEGER NOT NULL,
FOREIGN KEY (dev_id)
REFERENCES developers (dev_id)
ON DELETE cascade
);

This table declares a foreign key constraint that holds a delete cascade constraint and references the dev_id in the developers’ table.

Let us add sample data to test as shown:

INSERT INTO developers(full_name, dev_language)
VALUES
('Romilly Roy Reeve', 'Python 3'),
('Anastasija Jimi Hirsch' , 'C++'),
('Marylène Nina Capella', 'Rust')

The developers’ table should have sample data as shown:

SELECT * FROM developers;