I’m designing tables for a simple poll system and I’ve come across a peculiar situation with foreign key references forming a closed square:
In short: poll contains a collection of polls. Each poll can have several poll_questions. poll_answer_set refers to a single anonymous person answering a set of questions to a single poll. poll_answer is an answer to a poll_question in a given poll_answer_set.
All foreign keys in these tables are set as ON DELETE CASCADE except for poll_question_id (marked by the green horizontal line), which is ON DELETE NO ACTION - because I don’t want any poll question to be deleted if anyone has already provided an answer for it.
Now imagine I have data in all these tables: there is a poll with a question, and a poll answer set with an answer to the question - and I’m trying to delete the poll. The ON DELETE actions for poll_answer will come from two directions simultaneously: via poll_answer_set and via poll_question. Which one will be fired first? In this case this will determine if the delete will succeed or not depending on whether the ON DELETE CASCADE or the ON DELETE NO_ACTION FK will be handled first. Well, in my trial the delete failed and when I switched the ON DELETE definitions in the two FKs of poll_answer then the delete succeeded. But can I expect this behaviour to be consistent? Is it documented anywhere in which order FK actions will cascade in case there are more than one coming to the same table?