SQL Server 2005 How Create a Unique Constraint?

How do I create a unique constraint on an existing table in SQL Server 2005?

I am looking for both the TSQL and how to do it in the Database Diagram.


The SQL command is:

ALTER TABLE <tablename> ADD CONSTRAINT
            <constraintname> UNIQUE NONCLUSTERED
    (
                <columnname>
    )

See the full syntax here.

If you want to do it from a Database Diagram:

  • right-click on the table and select 'Indexes/Keys'
  • click the Add button to add a new index
  • enter the necessary info in the Properties on the right hand side:
    • the columns you want (click the ellipsis button to select)
    • set Is Unique to Yes
    • give it an appropriate name

In SQL Server Management Studio Express:

  • Right-click table, choose Modify or Design(For Later Versions)
  • Right-click field, choose Indexes/Keys...
  • Click Add
  • For Columns, select the field name you want to be unique.
  • For Type, choose Unique Key.
  • Click Close, Save the table.

ALTER TABLE [TableName] ADD CONSTRAINT  [constraintName] UNIQUE ([columns])

Warning: Only one null row can be in the column you've set to be unique.

You can do this with a filtered index in SQL 2008:

CREATE UNIQUE NONCLUSTERED INDEX idx_col1
ON dbo.MyTable(col1)
WHERE col1 IS NOT NULL;

See Field value must be unique unless it is NULL for a range of answers.


ALTER TABLE dbo.<tablename> ADD CONSTRAINT
            <namingconventionconstraint> UNIQUE NONCLUSTERED
    (
                <columnname>
    ) ON [PRIMARY]