SQL Server 2005 How Create a Unique Constraint

Question

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

User · Accepted Answer

The SQL command is   ALTER TABLE  lt tablename gt  ADD CONSTRAINT              lt constraintname gt  UNIQUE NONCLUSTERED                        lt columnname gt          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

User · Answer

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

User · Answer

ALTER TABLE  TableName  ADD CONSTRAINT   constraintName  UNIQUE   columns

User · Answer

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   lt index name gt        ALTER TABLE dbo  lt target table name gt  DROP CONSTRAINT  lt index name gt   GO  ALTER TABLE dbo  lt target table name gt  ADD CONSTRAINT  lt index name gt  UNIQUE NONCLUSTERED   lt col 1 gt    lt col 2 gt         lt col n gt    GO

User · Answer

ALTER TABLE dbo  lt tablename gt  ADD CONSTRAINT              lt namingconventionconstraint gt  UNIQUE NONCLUSTERED                        lt columnname gt        ON  PRIMARY

User · Answer

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   lt index name gt        ALTER TABLE dbo  lt target table name gt  DROP CONSTRAINT  lt index name gt   GO  ALTER TABLE dbo  lt target table name gt  ADD CONSTRAINT  lt index name gt  UNIQUE NONCLUSTERED   lt col 1 gt    lt col 2 gt         lt col n gt    GO

User · Answer

ALTER TABLE dbo  lt tablename gt  ADD CONSTRAINT              lt namingconventionconstraint gt  UNIQUE NONCLUSTERED                        lt columnname gt        ON  PRIMARY

User · Answer

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

User · Answer

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

User · Answer

You are looking for something like the following  ALTER TABLE dbo doc exz ADD CONSTRAINT col b def UNIQUE column b   MSDN Docs

User · Answer

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

User · Answer

ALTER TABLE  TableName  ADD CONSTRAINT   constraintName  UNIQUE   columns

User · Answer

ALTER TABLE  TableName  ADD CONSTRAINT   constraintName  UNIQUE   columns

User · Answer

ALTER TABLE dbo  lt tablename gt  ADD CONSTRAINT              lt namingconventionconstraint gt  UNIQUE NONCLUSTERED                        lt columnname gt        ON  PRIMARY

User · Answer

You are looking for something like the following  ALTER TABLE dbo doc exz ADD CONSTRAINT col b def UNIQUE column b   MSDN Docs

User · Answer

ALTER TABLE dbo  lt tablename gt  ADD CONSTRAINT              lt namingconventionconstraint gt  UNIQUE NONCLUSTERED                        lt columnname gt        ON  PRIMARY

User · Answer

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

User · Answer

You are looking for something like the following  ALTER TABLE dbo doc exz ADD CONSTRAINT col b def UNIQUE column b   MSDN Docs

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You are looking for something like the following  ALTER TABLE dbo doc exz ADD CONSTRAINT col b def UNIQUE column b   MSDN Docs

User · Answer

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

User · Answer

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

User · Answer

ALTER TABLE  TableName  ADD CONSTRAINT   constraintName  UNIQUE   columns

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Server 2005 How Create a Unique Constraint?

Examples related to sql

Examples related to sql-server

Examples related to constraints