CODEWITHSUNDEEP
sql-serveruniqueunique-constraint
Wahid Bitar
Wahid Bitar

Reputation: 14074

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

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?

Upvotes: 43

Views: 92636

Answers (3)

Tony L.
Tony L.

Reputation: 19396

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.

enter image description here

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

enter image description here

  1. Check your column in the next window

enter image description here

  1. Click OK in both windows

Upvotes: 8

Anton Gogolev
Anton Gogolev

Reputation: 115701

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.

Upvotes: 97

Thomas Owens
Thomas Owens

Reputation: 116159

You are looking for the UNIQUE constraint.

Upvotes: 6

Related Questions