CASCADE

One powerful feature in relational databases when working with foreign keys is the ability to automatically update or delete related rows in other tables. This is done using CASCADE.

Getting Started

Let’s start by creating a new database to test the cascading behavior:

MySQL

CREATE DATABASE cascadedb;

USE cascadedb;

Now, let’s create two tables:

• owners: list of cat owners
• cats: list of cats that belong to the owners

Table for Owners

MySQL

CREATE TABLE owners (
    ownerId INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(100) NOT NULL
);

This looks pretty normal – Nothing fancy here yet.

Table for Cats

MySQL

CREATE TABLE cats (
    catId INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(100) NOT NULL,
    ownerId INT NOT NULL,
    FOREIGN KEY (ownerId)
        REFERENCES owners(ownerId)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Here we define that a cat can have one owner. Here we also now define the cascading behaviour that if an owner is deleted or their ID is updated, the changes will cascade to the cats table from the owners table.

What does CASCADE do?

Let’s break down what each CASCADE option does:

• ON DELETE CASCADE: When a row in the parent table (owners) is deleted, all related rows in the child table (cats) are automatically deleted.
• ON UPDATE CASCADE: When the primary key of a parent row is updated, the change is automatically reflected in the child table’s foreign key.

CASCADE Options and Default Behavior

Here’s a list of different options you can use in a foreign key definition after ON DELETE or ON UPDATE:

• RESTRICT (default in MariaDB): Prevents the action (delete/update) if any related child rows exist. Checked immediately. If you do not specify the behavior, this will be the default.
• NO ACTION: Similar to RESTRICT, but the check is deferred until the end of the statement. Effectively the same as RESTRICT.
• CASCADE: Automatically deletes or updates the child rows when the parent row is deleted or its key is updated.
• SET NULL: Sets the foreign key in the child table to NULL when the parent is deleted or updated.

Testing the CASCADE

Add Data

Let's add some data:

MySQL

INSERT INTO owners (name) VALUES ("Aleksi"), ("Toni");

INSERT INTO cats (name, ownerId) VALUES 
("Cat 1", 1),
("Cat 2", 1),
("Cat 3", 2);

Check the Data

Check the data:

MySQL

SELECT * FROM cats;

You should see 3 cats, two belonging to Aleksi (id 1), one to Toni (id 2).

catId	name	ownerId
1	Cat 1	1
2	Cat 2	1
3	Cat 3	2

Delete Owner (Parent)

Now – Let's delete the owner Aleksi (ownerId = 1):

MySQL

DELETE FROM owners WHERE ownerId = 1;

Verify Cascade

And check the cats again:

MySQL

SELECT * FROM cats;

catId	name	ownerId
3	Cat 3	2

You should now see only 1 cat, one belonging to Toni (id 2). You will now see that the cats with ownerId = 1 (Aleksi) were automatically deleted — thanks to ON DELETE CASCADE.