May 13, 2005 at 4:58 am
Hi - I'm using Sql Server 2000.
I have a table which controls access to folders, based on the group a person belongs to:
group_id folder_id
1 1
1 2
1 3
2 3
I want to stop any duplicate entries:
group_id folder_id
1 1
1 2
1 3
2 3
2 3
...and for SQL to throw an exception if someone tries (which I'll catch in code).
How do I apply the index/constraint to the columns to let me do this - I presume I have to add an index to both columns, but I'm not sure what to set them as.
Thanks for any help,
Mark
May 13, 2005 at 5:30 am
If these are the only columns of the table, create a primary key as (group_id, folder_id) or the other way round, depending on which query is most used in your system.
May 13, 2005 at 5:35 am
And if there are other columns and already a primary key you instead add a unique constraint.
ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE CLUSTERED (group_id, folder_id)
You might want to create it as nonclustered instead, as well as in the opposite order as above.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply