nHibernate Cascade="Delete-Orphan" for multiple keys

Question

Lets say I have the following 3 tables:

Table A, Table B & Table C.

Table C has a foreign key to Table A. Table C has a foreign key to Table B.

When I delete a row from Table B I want it to delete the orphan in Table C but only if it doesn't hold any references to Table A. If it does hold a reference to table A I want it to delete the row in Table B and set the foreign key to null in table C.

Is that even possible? what is it's default behavior?

Answer 1

Your scenario is typical "Business requirement" not a "cascade" requirement.

As in detail discussed here: When/Why to use Cascading in SQL Server? (mechanism behind is the same for SQL cascade delete / NHibernate cascade delete)

• ...
• Cascade Delete may make sense when the semantics of the relationship can involve an exclusive "is part of" description. For example, and OrderLine record is part of it's parent order, and OrderLines will never be shared between multiple orders. If the Order were to vanish, the OrderLine should as well, and a line without an Order would be a problem.
• The canonical example for Cascade Delete is SomeObject and SomeObjectItems, where it doesn't make any sense for an items record to ever exist without a corresponding main record.
• ...

Again, while these are about feature on a SQL server, they apply to NHibernate cascade feature as well.

So, in your case, where there is a pretty complex logic about deletion, the answer should be:

• move the deletion defintion outside of the Data layer (NHibernate mapping)
• place it inside of the Business layer. Create rules arround that, assure in tests that your logic is working (regardless on pesistence engine, i.e. NHibernate)
• profit in the future from your/custom deletion logic, once users will adjust their demands. You won't be limited by NHibernate...