Problem when adding cascade actions to a relationship

Hello everyone,

I wanted to change a relationship in a database in order for it to have a cascading effect on update and on delete in order to preserve the data integrity. The problem is that when I click the save button I get the following error message:

'Role (Application)' table saved successfully
'Usergroup_Role (Security)' table
- Unable to create relationship 'FK_Usergroup_Role_RoleId_Application_Role_Id'.
Introducing FOREIGN KEY constraint 'FK_Usergroup_Role_RoleId_Application_Role_Id' on table 'Usergroup_Role' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

Any suggestion on how can I fix this? For a better understanding of the real problem I've uploaded a picture of the tables diagram.Here's the link

Best regards.

Instead of using a cascade, why not either write the delete into your stored procedure or usea logical delete flag?

|||

TATWORTH:

Instead of using a cascade, why not either write the delete into your stored procedure or usea logical delete flag?

Thanks for your reply Tatworth. It's only because that would make me have half the app with cascade and the other half hand coded. Before I consider that solution I'd much rather understand why this error is being returned and to know if there's any design change I can put in place to fix it.