如何强制 SQL Server 2008 R2 中的整个表的列唯一？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4486258/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I force a column to be unique for an entire table in SQL Server 2008 R2?
提问by Giffyguy
I have a table with a Descriptionfield. I want to ensure that no two rows have the same "Description," but I can't make Description into my identity column (my ID column is an int).
我有一个带Description字段的表。我想确保没有两行具有相同的“描述”，但我无法将描述放入我的身份列（我的 ID 列是一个整数）。
Is it safe to set Descriptionas a second primary key (in addition to my ID which is already a primary key)?
设置Description为第二个主键是否安全（除了我已经是主键的 ID 之外）？
采纳答案by Lorenzo
Add a Unique index to the Description column.
向描述列添加唯一索引。
Using Sql Server Management Studio right click on the table and choose Design. Then right click on a column and choose "Indexes/keys". You will be prompted with the following window
使用 Sql Server Management Studio 右键单击表并选择“设计”。然后右键单击一列并选择“索引/键”。将提示您使用以下窗口
Click on Add on the bottom left and then specify properties for your index. If you want to use a DDL script then use something like this
单击左下角的添加，然后为您的索引指定属性。如果你想使用 DDL 脚本，那么使用这样的东西
CREATE UNIQUE NONCLUSTERED INDEX [IX_INDEXNAME] ON [dbo].[TABLENAME]
(
[Description] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
回答by Mitch Wheat
There is no such thing as a 'secondary primary key'. There is one primary key per table.
没有“辅助主键”这样的东西。每张表有一个主键。
Create a UNIQUE constrainton the Descriptioncolumn (highly unusual thing to do, BTW. For example, It is more usual to create a unique index on Product Namerather than a Product description) or if you have null values in the Descriptioncolumn create a Filtered index(SQL Server 2008 onwards)
在列上创建一个UNIQUE 约束Description（非常不寻常的事情，顺便说一句。例如，在Product Name而不是 a上创建唯一索引更常见Product description），或者如果Description列中有空值，则创建一个过滤索引（SQL Server 2008向前）
ALTER TABLE dbo.yourTable
ADD CONSTRAINT UQ_yourTable_Description UNIQUE ([Description]);
回答by Tony L.
There is another way to do this with the SSMS GUI if you prefer:
如果您愿意，还有另一种方法可以使用 SSMS GUI 执行此操作：
I've replaced the Descriptioncolumn with the name MyUniqueColumnfor the sake of the example.
为了示例，我已将Description列替换为名称MyUniqueColumn。
- Right click "Indexes" under your table in the SSMS Solution Explorer and click "New Index..." (I know you are looking to create a contstraint, not an index, but this is exactly what the
ADD CONSTRAINTSQL script does.
- 右键单击 SSMS 解决方案资源管理器中表下的“索引”，然后单击“新建索引...”（我知道您要创建约束，而不是索引，但这正是
ADD CONSTRAINTSQL 脚本所做的。
- Give new index a name (e.g. "UQ_MyUniqueColumn"), check "Unique", and click "Add..."
- 为新索引命名（例如“UQ_MyUniqueColumn”），选中“唯一”，然后单击“添加...”
- Check your column in the next window
- 在下一个窗口中检查您的列
- Click OK in both windows
- 在两个窗口中单击确定
