Delete rules on 'linking table' between 2 of the same entities
Question

Hi All,

i want to apply DELETE into the delete rules of a linking table which obviously runs into error:
"Could not create foreign key constraint. This may have happened because there are 'RoomId' values of entity 'Door' with no corresponding value in entity 'Room', or attribute 'RoomId' of entity 'Door' is creating a circular dependency between entities. Check the Error Log for more information. "
I understand why, but what is the best practice to solve this?

NOTE: Both tables are empty !

Best regards,

Jeroen

Hi Jeroen,


Can you share your OML file?

Hi Jochem,
sorry I cannot. This is just an example which you can create easily. Then you will discover you cannot adjust both Room1Id and Room2Id to 'DELETE'......

BR,

Jeroen

mvp_badge
MVP

Hi Jeroen,

Rather than setting a delete rule, I would handle this in a delete action.
When deleting a room first check for existing doors in that room and then delete those doors before deleting the room

Hi Eric,

indeed, i would do the same.
But maybe there's some very intelligent way :-)

mvp_badge
MVP

That's the most intelligent way that I know :P

mvp_badge
MVP

Hi Jeroen,


I do agree with Eric's point but still would like to suggest considering a different approach.

If your data model is that simple that the only reason for the Delete action to fail would be the foreign key, or in the case it fails, you will show a general error message (like "An error has occurred. Please try again later."), I wouldn't even do a query before the check and just handle the exception on the UI.

What would you gain with checking beforehand? I mean you will not allow the delete of the room with doors, just like the foreign key is preventing. In this way, when deleting a room successfully, which would be most of the times I presume, instead of having two queries: 1 for checking the existence of rooms and 2 for deleting the room, there would be only one query to delete the room, hence it would be more performant. The database already guarantees the database consistency, so I wouldn't recreate that "validation logic" (as simple as it may be) and would have a better performance.


Kind Regards,
João

mvp_badge
MVP

Hi Jeroen,

In addition to what others have said, in general it is not encouraged to use a Delete Rule of "Delete", as it can be slow, especially when there's cascading deletes, and also, since it's not the default setting, in a large factory, developers may tend to assume it's set to "Protect" and mistakenly code accordingly.

Yes, I agree with you all.
But my question is just "Why should it be possible to set both RoomId's on Delete?" 

mvp_badge
MVP

Because it's an edge case, and either nobody thought of it, or they thought it wasn't worth the bother (and I'm not sure MS SQL reacts the same as Oracle).

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.