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.
Think about it. If you happen to delete a row in Base table, it trickle downs the cascade deletes in two ways. One thru Role table and the other UserGroup table. so its setting up the cascade deletes in two separate paths and they finally end up deleting data in UserGroup_Role table from two paths. You need to put some more thought to the model before setting up the relationships.
As a different matter, you need to work on the naming standards also. Use good names for the column so that it describes the entity attributes. Take Id column, its very to hard to know which Id you are referring to. Name the columns as RoleID, BaseID, UserGroupID, RoleCategoryID etc...
|||
Thanks for your reply Sankar,
Yes, I understand what your are saying but how do you suggest me to do this in order to preserve the database integrity when a Role is deleted? I was trying to enforce this through the use of a cascade effect, thus preventing the existence of orphan rows in the [Security].[Usergroup_Role] table. An alternative would be to create a trigger, or something like that, to do enforce this programmatically.
I’m still trying to learn the best way to solve this kind of issues in SQL Server.
As for your observation regarding the Id columns: thanks, that’s something I can certainly change.
Best regards.
|||
Whenever you have cyclic relationships between tables then as a common rule, you are either missing something or overdoing something. As we don't know the business rules wrt the model, we can only guess. Only you can make the judgement.
Ask yourself, does UserGroupID depend on applicationID, how and why?
What is the relationship between UserGroup and Role? and let us know how you got over this.
|||
Prolly, I'm just failing to see exactly what is overdone or missing. The rules are quite simple in fact.
Each application [Application].[Base] can have a variable number of Roles, for example Delete Employee. Each of the roles will have to be associated with the application to which they belong, which is represented by the column [ApplicationId] in the [Application].[Role] table. Furthermore, they also have to belong one (and only one) role Category from the [Application].[Role_Category] table. This relation is represented by the [CategoryId] column in the [Application].[Role] table.
In order to define who has access to what each application will define a variable number of usergroups through the [Security].[Usergroup] table. Since the usergroup is associated with an application (because they belong to it and are only to be used within it) this relation is enforced by the [ApplicationId] column in the [Security].[Usergroup].
Since the relation between usergroups and roles is many-to-many there was the need to create a link table [Security].[Usergroup_Roles] which makes the association of the roles that each usergroup has, or hasn't, access to.
Furthermore, there's the need to enforce data integrety in the following way:
If an application is deleted from the [Application].[Base] all of its usergroups, and consequently the roles that usergroup is associated with, must be deleted from the tables [Security].[Usergroup] and [Security].[Usergroup_Role] respectively.
The next integrety rule states that the roles associated with an application [Application.Role] must also be deleted should the application, to which they belong to, get deleted in the [Application].[Base]. That, by itself, isn't a problem. The cascading relation between the columns [Application].[Role].[ApplicationId] and [Application].[Base].[Id] takes care of that for us just fine.
So far so good!
The problem occurs when I try to enforce the 3rd integrety rule: Should an application role be deleted from the [Application].[Role] table, that action must be enforced in the [Security].[Usergroup_Role], as there can't be an allow / deny relation to an inexisting role. The creation of a cascading relation between [Security].[Usergroup_Role].[RoleId] and [Application].[Role].[Id] causes the multiple path error.
I know I could solve this with a trigger in the [Application].[Role] table that enforces the integrety after delete using the virtual DELETED table. That's infact what's in place at the moment. Thus, leaving me with only one question: is there any design change I can make to enforce all the integrety rules through cascading relations, instead of using programmatic solutions (triggers or otherwise)?
|||
I was reading this and thought i would chime in. I checked out the ERD and also agree that it looked a bit cyclical. When the ERD starts to look like a circle, I always recommend going back to the drawing board as it can lead to fictitious data being generated via queries.
I know this doesn't exactly meet your needs as detailed above, however, if you can come up with commonly conceived roles and groups, you may find that you can reuse them in multiple applications.
Not knowing your full details, I thought I would propose my solution anyways. I would have made a different design choice.
Your major objects appear to be the Base Application, User Groups and Roles. I believe you are trying to capture the the relationship in an inappropriate way. I would recommend having your 3 core tables each have a one-to-many relationship with a new link table which you may call ApplicationUserGroupRole. This would be uniquely indexed by a super key of the User Group, Role, and Application tables. (keeping the role_category table as is) Dropping the exising Usergroup_Role table.
This would allow you to capture the relationships of what User Group has which roles in which applications. I am not a believer of deleting historical security access information, so I would recommend having an Active Bit column or Active/Inactive DateTime fields to indicate when a specific user group has role rights in a specific application.
It wouldn't force the deletion of roles, etc... but a cascading delete should force the deletion of the link history table. (still a bad idea I think)
|||
Hello asjev1, I'm glad you dropped by 
I understand your design implementation, however I believe it doesn't meet our requirements. For instance, in your design the usergroups and the roles can exist even if the application they belong to is no longer there, only enforcing such integrety in the ApplicationUsergroupRole table. Ultimately this would mean that you would, eventualy, and if I understood correctly, end up with a number of usergroups and roles, in their respective core tables, that are no longer in use (as in for good).
Regarding the historical data I'm doing this a bit differently that what you might expect. The history, in this particular case, works this way: when an object (say a datarow) is changed it's shipped in XML to a log system. That XML contains all sort of informations, including the object in its original state prior to the change. All the history is monitored 24/7 by the staff of the company.
|||
Triggers are evil! But they may do the trick in this instance, depending on the load placed on your server. If the server isn't being over utilized nor does the plan call for a huge increase, then triggers are a great solution. But if you want cascading deletes, you have to get away from the cyclical pattern.
Good luck! Let us know how you solve this!
asjev1
|||Yes, indeed. Don't suppose you have a suggestion to fix the problem I pointed in your design.|||
If I understand your largest concern, that you would like all groups and roles associated with an application deleted once an application is removed, you could place an instead of delete trigger on the application table per the design i mentioned previously. The primary key of the application table can be caught in the temporary deleted table available in the trigger.
You could then do something like this:
CREATE TABLE TempTable
(ApplicationID,
RoleID,
GroupID)
1. Select associated records into temp table
SELECT ApplicationID,RoleId, GroupID
From ApplicationUserGroupRole
where ApplicationId=(SELECT ApplicationID from deleted);
2. delete link table records
Delete from ApplicationUserGroupRole
Where ApplicationId in (Select ApplicationID from deleted);
3. delete from role and group and application tables
Delete from ApplicationRole
Where RoleID in (select roleID from TempTable);
Delete from ApplicationGroup
Where GroupID in (select groupID from TempTable);
Delete from Application
where applicationID in (select applicationID from TempTable)
4. Clean up temp table
Drop Table TempTable
The net effect is:
1. Copy needed records into temporary table
2. Drop link table record (ApplicationUserGroupRole).
3. Drop Application/Role/Group records
4. Clean up
Hope it helps.
|||
Thanks both of you for your help.
TATWORTH:
