SQL SERVER – Create Unique Constraint on Table Column on Existing Table

Here is the question I received on twitter. It was about adding a unique constraint to a table that already exists. Here is how to create unique constraint on an existing table.

“Can we create a unique constraint on table column on Existing Table?”

Of course Yes!

Here is how you can create a unique constraint on the table which already exist in our system.

USE tempdb
GO
-- Create Table
CREATE TABLE Table1 (ID INT, Col1 VARCHAR(100))
GO
-- Alter Table Create Constraint
ALTER TABLE Table1
ADD CONSTRAINT UX_Constraint UNIQUE (Col1)
GO
-- Clean up
DROP TABLE Table1
GO

If your table already exists you can use above method to create the constraint. However, if you are about to create tables, you can just specify UNIQUE in the schema definition of Create Table itself. We will discuss about this in a future post.

What to Check Before You Create Unique Constraint on Existing Data

On a new, empty table this works every time. On a table that already has data, the command can fail, because SQL Server checks every existing row first. If two rows share the same value, you get error 1505 about a duplicate key, and nothing is created. The message also shows the duplicate value it found, which gives you a place to start.

So find the duplicates before you run the ALTER TABLE:

SELECT Col1, COUNT(*) AS Total FROM Table1 GROUP BY Col1 HAVING COUNT(*) > 1;

Clean those rows up, decide which one to keep, and then add the constraint. A few more things are worth knowing:

• NULL values: a unique constraint allows only one NULL in the column. If you need many NULLs but unique real values, use a filtered unique index with WHERE Col1 IS NOT NULL instead.
• It is an index: behind the scenes, SQL Server creates a unique nonclustered index by default, so the constraint also helps queries that search on that column.
• More than one column: you can list several columns, such as UNIQUE (Col1, Col2), when only the combination must be unique.
• Names matter: a clear name like UX_Constraint shows up in error messages, which makes a failed insert much easier to trace.

Finally, test the insert and update code of your application after you add the constraint. Code that used to create duplicates quietly will now get an error, and it is better to find that in testing than in production. Run the duplicate check once more right before the change, since new duplicates can arrive while you clean up.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.