June 30, 2004 at 6:10 pm
In the table below:
===========
Roles
===========
UserID
GroupID
===========
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´ll appreciate receiving any tips.
»»» KenA
June 30, 2004 at 7:42 pm
Create a composite unique constraint or composite primary key (same basic thing in this case) on both columns. Using EM table design is easiest to use after the fact.
July 2, 2004 at 7:52 am
It's not that difficult in QA, and you'll learn more by typing it out:
ALTER TABLE roles
ADD CONSTRAINT MyConstraintName
UNIQUE (UserID, GroupID)
This will force the combination of the two columns to be unique across all rows. You can still have 1,1 and 1,2 ... but you can't have 1,1 and 1,1.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply