SQL Server 2005如何创建一个唯一的约束?

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

如何在SQL Server 2005中创建现有表的唯一约束?

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

我正在寻找TSQL和如何在数据库关系图中实现它。

10 个解决方案

#1

271

The SQL command is:

SQL命令是:

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
单击Add按钮添加一个新的索引
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
- set是唯一的Yes
- give it an appropriate name
- 给它一个合适的名字
在右边的属性中输入必要的信息:您想要的列(单击省略号按钮来选择)设置是唯一的，请给它一个适当的名称。

#2

In SQL Server Management Studio Express:

在SQL Server Management Studio Express中:

Right-click table, choose Modify or Design(For Later Versions)
右键单击表，选择修改或设计(针对后续版本)
Right-click field, choose Indexes/Keys...
右键单击,选择索引/钥匙……
Click Add
单击Add
For Columns, select the field name you want to be unique.
对于列，选择要惟一的字段名。
For Type, choose Unique Key.
对于类型，选择唯一键。
Click Close, Save the table.
单击Close，保存该表。

#3

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

#4

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:

您可以使用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.

字段值必须是唯一的，除非它为一个范围的答案为空。

#5

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

#6

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.

单击close并保存图表，它将把它添加到表中。

#7

You are looking for something like the following

您正在寻找如下内容

ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
UNIQUE column_b

MSDN Docs

MSDN文档

#8

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

要在表已经创建时在一个或多个列上创建惟一的约束，请使用以下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.

由MySQL / SQL Server / Oracle / MS访问支持的查询。

#9

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.

在management studio关系图中，选择该表，右键单击以添加新列(如果需要的话)，右键单击该列并选择“Check Constraints”，在这里可以添加一个。

#10

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

#1

271