We help IT Professionals succeed at work.
Get Started

Unique Key vs Index vs Unique Constraint

mrichmon
mrichmon asked
on
26,386 Views
Last Modified: 2012-08-14
I have a table in MS SQL 2005

It looks similar to:

PrimaryId
Name
GroupId

Where PrimaryId is the primary key for the table.

I want to enforce that within a group, names are unique

I have read a lot of places that say you should prefer a unique constraint over a a unique key.  However, looking at the interface for SQL 2005 I see the option for Indexes/Keys and Check Constraints.

From the Indexes/Keys, I see that I could add a new key and then choose that it is of type Unique Key, and specify information.  I do not see a way to create a unique constraint from the check constraints (i.e. what expression would I use?)

Is the Unique Key the optimal way to do this?

What is the difference between creating an index and specifying "Is Unique" to "Yes" vs creating a Unique Key and specifying "Is Unique" to "Yes"?

Also I know that in general the naming convention is:
PK_ for primary keys
FK_ for foreign keys
IX_ for indexes

Is there a naming convention for unique keys?  Maybe UK_

Thanks.
Comment
Watch Question
Database Architect - Dba - Data Scientist
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 32 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE