Delete on Postgresql with cyclic constraints

Asked Oct 31 '17 at 11:38

Active Oct 31 '17 at 11:39

Viewed 37 times

I want to delete 2 rows of 2 tables that have mutual constraints. I can't modify the database structure.

The structure is something like this:

A (id, b_id, other_data) FOREIGN KEY (b_id) REFERENCES B(id)
B (id, a_id, other_data) FOREIGN KEY (a_id) REFERENCES A(id)

I thought that if I delete the 2 rows in the same transaction the constraints will be checked at the end, but I am having an error of constraint violation when tries to delete the first one.

Which is the right way to do this?

edited Oct 31 '17 at 11:39

Gordon Linoff

1,242,037
58
646
786

asked Oct 31 '17 at 11:38

Matías González

1,366
3
15
30

1

I think you may need to drop the constraints before changing the data. – Gordon Linoff Oct 31 '17 at 11:39
2

`DEFERRABLE` comes to mind. – joop Oct 31 '17 at 11:40
joop is right. **If** that design makes sense (which cyclic reference rarely make), _then_ a deferrable constraint is the best way to go. – Oct 31 '17 at 11:42
Excelent. That was what I was looking for! – Matías González Oct 31 '17 at 12:11

Delete on Postgresql with cyclic constraints

0 Answers0