CODEWITHSUNDEEP
sqlsql-server-2008unique-index
ncoder
ncoder

Reputation: 165

Unique index in already existing database table

I am trying to add a new unique index on one of my database tables in SQL Server 2008. This is an existing table and the column where I want the unique index already has some duplicate values.

Can I set up a unique index for that column? If so, how?

Upvotes: 2

Views: 4600

Answers (5)

Jeremy A. West
Jeremy A. West

Reputation: 2300

May not have been true in SQL Server 2008, however you can use Management Studio to do this in later versions such as 2014.

  1. Right click your table
  2. Choose Design
  3. Expand "Identity Specification" and set (is Identity) to Yes
  4. Save

Upvotes: 0

Rory
Rory

Reputation: 103

If the column already has duplicate values then I would recommend you create a unique composite key instead.

e.g. So, to handle that issue with this table design, you need to create a unique constraint on the table CustomerID/ProductID columns:

create unique index cust_products_unique on CustomerProducts (CustomerID, ProductID)

So that in essence a combination of fields ensures that the index is unique.

Regards

Upvotes: 0

Lamak
Lamak

Reputation: 70678

First you are gonna need to delete the duplicate values on your column and then you can create a unique index on it. So lets assume your table has 2 columns, id and column1. To delete duplicate values you need to choose one, it can be random or with some order. So it would be like this:

WITH CTE AS
(
     SELECT *, ROW_NUMBER() OVER(PARTITION BY column1 ORDER BY Id) Corr
     FROM YourTable
)
DELETE FROM CTE
WHERE Corr > 1

CREATE UNIQUE INDEX I_Unique ON YourTable(Column1)

Upvotes: 5

Shantanu Gupta
Shantanu Gupta

Reputation: 21108

No as the name suggest, Unique Index which says key has to be unique. So you cant

See this

Upvotes: 1

Jennifer S
Jennifer S

Reputation: 1449

You can't set this column up with a UNIQUE index if the table already has duplicate values, unless you remove the records containing the duplicate values for that column. This goes to the definition of UNIQUE.

Upvotes: 5

Related Questions