How to apply UNIQUE constraints to existing SQL columns

I wanted to add this to my blog because I had some difficulty finding out (via Google) how to add a UNIQUE constraint to a SQL column that already existed. To bring you up to speed, UNIQUE constraints prevent duplicate row entries in a column.

Note: This pertains to Microsoft SQL Server 2000.

To add a UNIQUE constraint to a table column use the following SQL query.

ALTER TABLE [dbo].[UserProfile]
ADD CONSTRAINT [UniqueEmail]UNIQUE ([Email])

If you try to add a UNIQUE constraint to a column with duplicate rows SQL will throw an error. So clean up those duplicates first. Also, keep in mind that having unique rows means you cannot have duplicate NULL values.

To remove a UNIQUE constraint use the following SQL query.

ALTER TABLE [dbo].[UserProfile]
DROP CONSTRAINT [UniqueEmail]

One Reply to “How to apply UNIQUE constraints to existing SQL columns”

Leave a Reply

Your email address will not be published. Required fields are marked *