MySQL 8.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.4.3


CREATE TABLE t (
  id int NOT NULL,
  parent_id int,
  name varchar(64) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (id),
  CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES t(id)
);
insert into t values
  (1, null, 'A'),
  (2, 1, 'B'),
  (3, 1, 'B'),
  (4, 4, 'C');

Records: 4  Duplicates: 0  Warnings: 0


/* simple delete */
delete from t where id = 2;
select * from t;


id
parent_id
name


1
null
A

3
1
B

4
4
C


/* id=3 can be deleted and then id=1 */
delete from t where id = 3 or id = 1;
select * from t;

Cannot delete or update a parent row: a foreign key constraint fails (`fiddle`.`t`, CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `t` (`id`))


/* id=4 can be deleted as it is cyclic to itself */
delete from t where id = 4;
select * from t;

Cannot delete or update a parent row: a foreign key constraint fails (`fiddle`.`t`, CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `t` (`id`))


/* when id=3 is deleted separately delete of id=1 passes */
delete from t where id = 3;
delete from t where id = 1;
select * from t;


id
parent_id
name


4
4
C