Conditional Constraint - HowTo

In the table below:
===========
Roles
===========
UserID (int)
GroupID
===========
Considering that UserID is FK from Users Table and GroupID is FK from Groups Table:
===========
Groups
===========
GroupID (int)
GroupName
===========
===========
Users
===========
UserID
Username
Password
Email
===========
And considering a User can have 0 or many Roles, exs:
Roles table
===========
1,1
1,2
1,3
===========
Meaning that this user have roles: 1,2,3
The problem is that I can have also:
Roles table
===========
1,1
1,1
1,1
===========
which is bad, so I need to have a constraint in the Roles table to force GroupID Unique ONLY in case UserID is already there!
Is it possible?
I isn't this just a matter of letting both columns be the primary key?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> In the table below:
> ===========
> Roles
> ===========
> UserID (int)
> GroupID
> ===========
> Considering that UserID is FK from Users Table and GroupID is FK from Groups Table:
> ===========
> Groups
> ===========
> GroupID (int)
> GroupName
> ===========
>
> ===========
> Users
> ===========
> UserID
> Username
> Password
> Email
> ===========
> And considering a User can have 0 or many Roles, exs:
> Roles table
> ===========
> 1,1
> 1,2
> 1,3
> ===========
> Meaning that this user have roles: 1,2,3
>
> The problem is that I can have also:
> Roles table
> ===========
> 1,1
> 1,1
> 1,1
> ===========
> which is bad, so I need to have a constraint in the Roles table to force GroupID Unique ONLY in
case UserID is already there!
> Is it possible?
|||Hum, but then I believe there′ll be just 1 role per user?
"Tibor Karaszi" wrote:

> I isn't this just a matter of letting both columns be the primary key?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> case UserID is already there!
>
>
|||No, this enforces that you cannot have a duplicate of the *combination* of the columns. So, below is
fine:
1,2
1,3
2,2
2,3
Below is not fine:
1,2
1,2
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...[vbcol=seagreen]
> Hum, but then I believe therell be just 1 role per user?
> "Tibor Karaszi" wrote:
in[vbcol=seagreen]
|||Actually your Right! Sorry for misunderstanding and thanks very much for the quick response :-)
"Tibor Karaszi" wrote:

> No, this enforces that you cannot have a duplicate of the *combination* of the columns. So, below is
> fine:
> 1,2
> 1,3
> 2,2
> 2,3
>
> Below is not fine:
> 1,2
> 1,2
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...
> in
>
>
|||We all lose ourselves from time to time. Glad it helped. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:60CD2318-C000-4EC4-9C76-992096AD5F71@.microsoft.com...[vbcol=seagreen]
> Actually your Right! Sorry for misunderstanding and thanks very much for the quick response :-)
> "Tibor Karaszi" wrote:
below is[vbcol=seagreen]
ONLY[vbcol=seagreen]