Set to null a parent record so that children are removed: howto?
- by EugeneP
How to delete a child row (on delete cascade ?) when setting a null value on a parent?
Here's the db design.
table A [id, b_id_1, b_id_2]
table B [id, other fields...]
b_id_1 and b_id_2 can be NULL
if any of them is null, it means NO B records for corresponding FK (there are 2 of them)
so (b_id_1,b_id_2) can be (null,null), (100, null), (null, 100_or_any_other_number) etc
How in one SQL query both set b_id_1 or b_id_2 to null and delete all rows from B that have this id?
What FK design should be applied to the 2 tables?
what foreign keys should be added? A - B (FK_1: A.b_id_1 references B.id, FK_2: A.b_id_2 references B.id)
and also B-A (FK_3: B.id references A.b_id_1, FK_4: B.id references A.b_id_2) ?
But again, setting an A's b_id_1 or A's b_id_2 to null - will it remove any of B's records?
I don't think so. So how to do that?
