Delete db rows that contain reference to self

Question

I have some data:

id	parent_id	topic_id
1		1
2	1	1
3		2
4	3	2

parent_id has FK to id.

I'm trying to delete data by topic_id - as U see, one row is referencing to another row which both are to be deleted. But sometimes this fails due to a FK violation. It is makes it kinda hard to reproduce this situation.

How to delete them?

begin deletion from the leafs and work upwards?
encapsule this into transaction (right now it is in "auto-commit" mode)?
temporarily disable FKs? Don't want that, I might corrupt data
...?

Managed to reproduce this. SQL:

create table test (
    id bigint auto_increment primary key,
    parent_id bigint null ,
    topic_id bigint,
    foreign key (parent_id) references test(id)
) engine=innodb;

insert into test (id, parent_id, topic_id)
values
    (1, null, 1),
    (2,    1, 1),
    (3,    2, 1),
    (4, null, 2),
    (5,    4, 2);

-- select * from test;

delete from test where topic_id=1;

-- [23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`test`, CONSTRAINT `test_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `test` (`id`))

Provide your sample data not as a table but as CREATE TABLE + INSERT INTO scripts. Provide DELETE query which may sometimes fail. — Akina
– Akina, Commented May 27 at 9:59
id is autoincremented primary key. So your scheme guarantees that the relation id > parent_id is TRUE for each non-root row. In this case you may use a trick DELETE FROM test WHERE topic_id=1 ORDER BY id DESC;. See dbfiddle.uk/idWyrsD5 But I recommend to use the solution provided by Guillaume Outters because it provides deterministic behaviour. — Akina
– Akina, Commented May 27 at 12:11

Guillaume Outters · Accepted Answer · 2025-05-27 10:11:31Z

3

If you always want to delete child rows when removing an head, you could make your constraint cascade deletes:

alter table t add constraint fk_parent_id foreign key (parent_id) references t(id) on delete cascade;

A transaction will not help (not even a grouped delete, as in delete from t where id in (1, 2)), as shown in this fiddle.

answered May 27 at 10:11

Guillaume Outters

7,5371 gold badge22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete db rows that contain reference to self

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related