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

sql-server unique unique-constraint
Wahid Bitar picture Wahid Bitar · Oct 19, 2009 · Viewed 76.4k times · Source

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

Anton Gogolev picture Anton Gogolev · Oct 19, 2009

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.

Related questions

How to select only the first rows for each unique value of a column

Let's say I have a table of customer addresses: CName | AddressLine ------------------------------- John Smith | 123 Nowheresville Jane Doe | 456 Evergreen Terrace John Smith | 999 Somewhereelse Joe Bloggs | 1 Second Ave In the table, one customer like John Smith can have multiple addresses. I need …

Read More
SQL - select distinct only on one column

I have searched far and wide for an answer to this problem. I'm using a Microsoft SQL Server, suppose I have a table that looks like this: +--------+---------+-------------+-------------+ | ID | NUMBER | COUNTRY | LANG | +--------+---------+-------------+-------------+ | 1 | 3968 | UK | …

Read More
How to create a unique index on a NULL column?

I am using SQL Server 2005. I want to constrain the values in a column to be unique, while allowing NULLS. My current solution involves a unique index on a view like so: CREATE VIEW vw_unq WITH SCHEMABINDING AS SELECT …

Read More