[sql] 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.

I also found you can do this via, the database diagrams.

By right clicking the table and selecting Indexes/Keys...

Click the 'Add' button, and change the columns to the column(s) you wish make unique.

Change Is Unique to Yes.

Click close and save the diagram, and it will add it to the table.

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

I also found you can do this via, the database diagrams.

By right clicking the table and selecting Indexes/Keys...

Click the 'Add' button, and change the columns to the column(s) you wish make unique.

Change Is Unique to Yes.

Click close and save the diagram, and it will add it to the table.

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

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

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.

In the management studio diagram choose the table, right click to add new column if desired, right-click on the column and choose "Check Constraints", there you can add one.

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

In some situations, it could be desirable to ensure the Unique key does not exists before create it. In such cases, the script below might help:

IF Exists(SELECT * FROM sys.indexes WHERE name Like '<index_name>')
    ALTER TABLE dbo.<target_table_name> DROP CONSTRAINT <index_name> 
GO

ALTER TABLE dbo.<target_table_name> ADD CONSTRAINT <index_name> UNIQUE NONCLUSTERED (<col_1>, <col_2>, ..., <col_n>) 
GO

You are looking for something like the following

ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
UNIQUE column_b

MSDN Docs

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.

I also found you can do this via, the database diagrams.

By right clicking the table and selecting Indexes/Keys...

Click the 'Add' button, and change the columns to the column(s) you wish make unique.

Change Is Unique to Yes.

Click close and save the diagram, and it will add it to the table.

To create a UNIQUE constraint on one or multiple columns when the table is already created, use the following SQL:

ALTER TABLE TableName ADd UNIQUE (ColumnName1,ColumnName2, ColumnName3, ...)

To allow naming of a UNIQUE constraint for above query

ALTER TABLE TableName ADD CONSTRAINT un_constaint_name UNIQUE (ColumnName1,ColumnName2, ColumnName3, ...)

The query supported by MySQL / SQL Server / Oracle / MS Access.

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

I also found you can do this via, the database diagrams.

By right clicking the table and selecting Indexes/Keys...

Click the 'Add' button, and change the columns to the column(s) you wish make unique.

Change Is Unique to Yes.

Click close and save the diagram, and it will add it to the table.

You are looking for something like the following

ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
UNIQUE column_b

MSDN Docs

To create a UNIQUE constraint on one or multiple columns when the table is already created, use the following SQL:

ALTER TABLE TableName ADd UNIQUE (ColumnName1,ColumnName2, ColumnName3, ...)

To allow naming of a UNIQUE constraint for above query

ALTER TABLE TableName ADD CONSTRAINT un_constaint_name UNIQUE (ColumnName1,ColumnName2, ColumnName3, ...)

The query supported by MySQL / SQL Server / Oracle / MS Access.

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.

In the management studio diagram choose the table, right click to add new column if desired, right-click on the column and choose "Check Constraints", there you can add one.

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

You are looking for something like the following

ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
UNIQUE column_b

MSDN Docs

In the management studio diagram choose the table, right click to add new column if desired, right-click on the column and choose "Check Constraints", there you can add one.

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 dbo.<tablename> ADD CONSTRAINT
            <namingconventionconstraint> UNIQUE NONCLUSTERED
    (
                <columnname>
    ) ON [PRIMARY]

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

You are looking for something like the following

ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
UNIQUE column_b

MSDN Docs

In some situations, it could be desirable to ensure the Unique key does not exists before create it. In such cases, the script below might help:

IF Exists(SELECT * FROM sys.indexes WHERE name Like '<index_name>')
    ALTER TABLE dbo.<target_table_name> DROP CONSTRAINT <index_name> 
GO

ALTER TABLE dbo.<target_table_name> ADD CONSTRAINT <index_name> UNIQUE NONCLUSTERED (<col_1>, <col_2>, ..., <col_n>) 
GO