CODEWITHSUNDEEP
sqlsql-servert-sql
KnusperPudding
KnusperPudding

Reputation: 412

T-SQL Constraint on Foreign Key and one Condition

I am trying to set up a small database to organize permissions. I am using those four tables:

Rules:

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

Upvotes: 0

Views: 178

Answers (1)

Rhys Jones
Rhys Jones

Reputation: 5518

Can you change the table design a little? If you add UserGroupId to the Permission table then this becomes much simpler. There is no need for the UserType column (unless you want it of course). Rows in the Permission table would have either a UserId or a UserGroupId, but not both (a check constraint can enforce this).

Also, as noted by @Damien_The_Unbeliever, using a column called UserId to hold either a UserId or a UserGroupId is not ideal. If you have to do this then change the name of the column to prevent confusion.

Upvotes: 2

Related Questions