Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to determine what happens for a particular circular foreign key that will cascade delete. Please feel free to address any other possibilities that my inexperience has not listed.

On my root table, it links to one of its children's data via an initially deferred foreign key that cascade deletes.

If the child's row is deleted, will the parent be automatically deleted, or will some part fail? I want to delete by the child row because it's the most organized way to delete per my specific case. Or am I being unnecessarily fussy because a well designed view could do this for me?

If some part fails, how can my intent be implemented?

Note, I've read ypercube's answer, and it seems like it will behave as I expect, but I can't confirm in the docs, and that is for MySQL.

I've also read ypercube's recommendation, but if I only insert through a transaction, won't the check be deferred and self-delete if the columns aren't nullable? Or will an error be thrown instead, or some combination?

share|improve this question
1  
Things are the other way around (or you mixed up parent and child :) : deleting the child should not influence its parent at all. –  dezso Feb 14 at 16:48
    
@dezso Thank you for looking dezso! OK, noob-alert: the data naturally branches from a parent, but it's more expedient to delete by one of its children, so I was hoping deleting the relevant child row would in turn delete the parent, which would delete the rest of the family. Is this simply not possible, or should I kink my database a little to make what seems to be the top of the branch a strange child of the delete-convenient table? Thank you so very much in advance! –  Gracchus Feb 14 at 16:52
1  
This related answer on SO may be of help. You have found that yourself already. Other than that, the question is a bit fuzzy. How are "child" and "parent" implemented? You would best provide a test setup to demonstrate what you are talking about, exactly. I suggest SQLfiddle for the purpose. –  Erwin Brandstetter Feb 15 at 0:11
    
@ErwinBrandstetter Thank you Erwin Brandstetter! If I can figure it out or how to get my code onto here, I will do it! –  Gracchus Feb 15 at 0:27
    
You create the fiddle and then edit your question, copying the SQLFiddle link and/or all the code in your question. –  ypercube Feb 15 at 0:37
add comment

1 Answer

This SO answer seems to confirm that it does behave in the way I expect, I assume since it has a checkmark, and no one's reasonably challenged it.

share|improve this answer
    
The linked answer is for Oracle. This one is about Postgres. –  Erwin Brandstetter Feb 15 at 0:12
    
@ErwinBrandstetter It was all I can find on the subject. Once a real answer comes, I'll delete this one. –  Gracchus Feb 15 at 0:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.