SQL Standard

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:

    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)
('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;

Add sample data into the contributions table as shown:

INSERT INTO contrib(repo_name, commit_id, dev_id)
('repo1', 464576, 1),
('repo1', 445876, 2),
('repo1', 466466, 3);

The contrib table should contain data as:

SELECT * FROM contrib;

To illustrate how on delete cascade constraint works, let us remove the developer with the id of 3.

DELETE FROM developers WHERE dev_id = 3;

If you query the contrib table, you will notice that the record for dev_id 3 has been dropped as well.

SELECT * FROM contrib;

That is how to use them on delete cascade constraints in SQL.

Thank you for reading!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list