CODEWITHSUNDEEP
sqlsql-serverdatabaseconstraints
KnusperPudding
KnusperPudding

Reputation: 412

SQLServer foreign key wont let me add constraint with cascade (on delete or update)

I have a simple datamodel in my database with three tables:

One ticket can have zero to multiple subtickets. Every ticket and every tubticket can have one state.

So my database looks like this:

my example Database

between: xTicket and xState i have the constraint:

on update cascade / on delete no action (updating the StateID in xTicket when changed, probihit deleting the entry in xTicket)

between: xSubTicket and xTicket I have the constraint:

on update cascade / on delete cascade (updating the TicketID in xSubTicket and deleting an entry when an entry in xTicket is deleted)

but when I want to the same constraint as for xTicket and xState for:

on update cascade / on delete no action I get the following message:

Foreign key constraint may cause cycles or multiple cascade paths

the foreign key will only let me set the constraint ON UPDATE CASCADE to either between xTicket and xState or between xSubTicket and xState.

so far I have found other questions about the same issue, where I got to know: either change the database design properly or the use of INSTEAD OF Triggers. - I actually want to know why this design is not acceptable, what am I doing wrong? How do I do it correctly?

thanks for any suggestion in advance

Upvotes: 1

Views: 81

Answers (1)

TomTom
TomTom

Reputation: 62093

I actually want to know why this design is not acceptable,

Why do you think ti is not acceptable? This is merely a limitation of SQL Server. An inconvenient one.

You are free to code around it and use triggers to do the cascading operations.

I would ague your design is broken because hard deleting states should never happen and you try to do the database do cleanup that may well be more complex. I would go for a soft delete (marking a state as not available for new objects) so the cascade operations make no sense there. But that is another discussion and outside of the topic of your question.

Upvotes: 1

Related Questions