How to make "No Duplicates" column in SQL Server 2008?

Question

I have a simple table in my SQL Server database. This table contains two columns: ID int, Name nvarchar(50). The ID column is the primary key for my table.

I want the "Name" column to be "(No Duplicates)", like in Microsoft Access, But this column isn't the primary column. How could I do this?

Answer 1

Add a unique constraint for that column:

ALTER TABLE Foo ADD CONSTRAINT UQ_Name UNIQUE (Name) 

---

To add it through SQL Management Studio UI:

1. Open SQL Server Management Studio.
2. Expand the Tables folder of the database where you wish to create the constraint.
3. Right-click the table where you wish to add the constraint and click Design.
4. In Table Designer, click on Indexes/Keys.
5. Click Add.
6. Choose Unique Key in the Type drop-down list.

To handle a situation where a unique constraint violation occurs, see for error 2601.

Answer 2

This can also be done another way with the SSMS GUI if you prefer:

1. Right click "Indexes" under your table in the SSMS Solution Explorer and click "New Index..." (I know you are looking to create a contstraint, not an index, but this is exactly what the ADD CONSTRAINT SQL script does.

2. Give new index a name (e.g. "UQ_MyUniqueColumn"), check "Unique", and click "Add..."

3. Check your column in the next window

4. Click OK in both windows