I am trying to set up a small database to organize permissions. I am using those four tables:
User, columns: UserID, NameUserGroup, columns: UserGroupID, NameUserGroupUsers, columns: UserID, UserGroupIDPermission, columns: PermissionID, UserID, UserType, Permission, PermissionValue
Rules:
- One User can be in multiple groups
- One Group can have multiple users
- A permission can be part of an user but also of an usergroup.
Now I am trying to set up a constraint, whenever I delete an user or a userGroup, I also want to delete the Permission rows with a database constraint.
The relation of user and userGroup are as following:
One row of User can have multiple rows in Permissions but with the extra condition: UserType = 1.
Sames for the groups:
One row of UserGroup can have multiple rows in Permissions but with the extra condition UserType = 2.
Examples:
Table: Permissions:
PermissionID | UserID | UserType | Permission | PermissionValue
---------------------------------------------------------------------
1 1 1 MainMenu 15
2 1 2 MainMenu 2
Row 1 representing an user-Permission of the user with userID = 1
Row 2 representing an userGroup-permission of the usergroup with usergroupID = 1.
What I tried so far:
ALTER TABLE dbo.permissions
ADD CONSTRAINT fk_permissionsAccounts
FOREIGN KEY(userID)
REFERENCES dbo.users(userID)
ON UPDATE CASCADE
ON DELETE CASCADE
but this one is actually wrong, since it should only happen to UserType=1
The next thing I tried, was adding a column UserType to both tables Users and Groups, including the type, but there I get the message:
the field is not part of a key.
Source on this try:
ALTER TABLE dbo.permissions
ADD CONSTRAINT fk_permissionsAccounts
FOREIGN KEY(userID, accountType)
REFERENCES dbo.users(userID, accountType)
ON UPDATE CASCADE
ON DELETE CASCADE
I am using SQL Server 2008
I'd be happy with any suggestions. thanks in advance.
Edit for Solution:
i've edited the table:
Permission, columns: PermissionID, UserID, UserGroupID, Permission, PermissionValue.
i have set null-allowed attribute on both: UserID and UserGroupID but added a check constraint which only allows one of both fields to be filled, other has to be null:
ALTER TABLE dbo.permissions
WITH CHECK ADD
CONSTRAINT chk_permissions
CHECK (
([AccountID] IS NULL AND [AccountGroupID] IS NOT NULL) OR
([AccountID] IS NOT NULL AND [AccountGroupID] IS NULL)
)
now i was able to add my default constraints:
ALTER TABLE dbo.permissions
ADD CONSTRAINT fk_permissionsUsers
FOREIGN KEY(userID)
REFERENCES dbo.users(userID)
ON UPDATE CASCADE
ON DELETE CASCADE
and:
ALTER TABLE dbo.permissions
ADD CONSTRAINT fk_permissionsUserGroups
FOREIGN KEY(userGroupID)
REFERENCES dbo.usergroups(userGroupID)
ON UPDATE CASCADE
ON DELETE CASCADE
UserTypeis 2, is in factUserIDmeant to contain aUserGroupIDfrom theUserGrouptable rather than an actualUserIDfrom theUsertable? If not, I'm not sure I understand this structure