Will ON DELETE CASCADE delete a parent even if another child has it as parent?

This is a question about the behavior of MySQL (I have version 8.0.18 for what it’s worth) when you delete a row in a table that was created with a DELETE ON CASCADE clause.

Suppose I have these 2 tables:

CREATE TABLE parents (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE children (
    id INT, 
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) 
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

Let’s also suppose that I have inserted some data in them, so that parents looks like this:

id
--
1
2

and children looks like this:

id | parent_id
--------------
1  |    1
2  |    1

Now suppose that I do this:

DELETE FROM children WHERE parent_id='1'

My question is whether, because of the ON DELETE CASCADE in the query I used to create children, this will also delete the row in parents whose id is 1, even though there is another row in children that also has it as parent? Based on what I have read about the behavior of ON DELETE CASCADE so far, it seems to be the case, but this isn’t very smart. So, in other words, I want to know if ON DELETE CASCADE is that dumb.

I tried to figure it out myself by testing the example I just described, but for some reason ON CASCADE DELETE doesn’t seem to work at all on my local server at the moment (nothing is deleted in parents even when I delete the second child in children, so there is no remaining child in children that refers to the parent whose id is 1 in parents), so while I’m figuring out why I thought that I should ask this question here because I’m probably not the only person who has wondered about that and the answer will be useful to other people.

Answer

There is a misconception here. The foreign key binds a child to a parent – not the other way around.

You seem to assume that on delete cascade deletes the parent when a chidren is removed. This is not the case: when a parent is deleted, the related children are deleted as well.

Here is an example based on your setup:

create table parents (
    id int not null,
    primary key (id)
);

create table children (
    id int, 
    parent_id int,
    foreign key (parent_id) references parents(id) on delete cascade
);

-- parent 1 has two children, parent 2 has one child
insert into parents values (1), (2);
insert into children values (1, 1), (2, 1), (3, 2);

-- delete parent 1
delete from parents where id = 1;

-- related children where deleted
select * from children;

id | parent_id
-: | --------:
 3 |         2