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?
Add a comment
|
5 Answers
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.
Comments
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)
Comments
No as the name suggest, Unique Index which says key has to be unique. So you cant
Comments
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
Comments
May not have been true in SQL Server 2008, however you can use Management Studio to do this in later versions such as 2014.
- Right click your table
- Choose Design
- Expand "Identity Specification" and set (is Identity) to Yes
- Save
