如何在我的列 (SQL Server 2008 R2) 上创建唯一约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5181877/
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 create a unique constraint on my column (SQL Server 2008 R2)?
提问by White Island
I have SQL Server 2008 R2 and I want to set a unique column.
我有 SQL Server 2008 R2，我想设置一个唯一的列。
There seems to be two ways to do this: "unique index" and "unique constraint". They are not much different from what I understand, although unique constraint is recommended by most, because you also get an index automatically.
似乎有两种方法可以做到这一点：“唯一索引”和“唯一约束”。它们与我的理解没有太大区别，尽管大多数人推荐使用唯一约束，因为您还可以自动获得索引。
How do I create a unique constraint?
如何创建唯一约束？
ALTER TABLE Customer ADD CONSTRAINT U_Name UNIQUE(Name)
Is there a way to create a unique constraint through the SQL Server Management Studio?
有没有办法通过 SQL Server Management Studio 创建唯一约束？
采纳答案by Martin Smith
To create these constraints through the GUI you need the "indexes and keys" dialogue not the check constraints one.
要通过 GUI 创建这些约束，您需要“索引和键”对话框而不是检查约束。
But in your case you just need to run the piece of code you already have. It doesn't need to be entered into the expression dialogue at all.
但在你的情况下，你只需要运行你已经拥有的代码段。根本不需要进入表情对话。
回答by Eric Leschinski
Set column as unique in SQL Server from the GUI:
从 GUI 在 SQL Server 中将列设置为唯一：
They really make you run around the barn to do it with the GUI:
他们真的让你在谷仓里跑来跑去用 GUI 来做：
Make sure your column does not violate the unique constraint before you begin.
在开始之前，请确保您的列没有违反唯一约束。
- Open SQL Server Management Studio.
- Right click your Table, click "Design".
- Right click the column you want to edit, a popup menu appears, click Indexes/Keys.
- Click the "Add" Button.
- Expand the "General" tab.
- Make sure you have the column you want to make unique selected in the "columns" box.
- Change the "Type" box to "Unique Key".
- Click "Close".
- You see a little asterisk in the file window, this means changes are not yet saved.
- Press Save or hit Ctrl+s. It should save, and your column should be unique.
- 打开 SQL Server 管理工作室。
- 右键单击您的表，单击“设计”。
- 右键单击要编辑的列，会出现一个弹出菜单，单击“索引/键”。
- 单击“添加”按钮。
- 展开“常规”选项卡。
- 确保在“列”框中选择了要使其唯一的列。
- 将“类型”框更改为“唯一键”。
- 单击“关闭”。
- 您会在文件窗口中看到一个小星号，这意味着更改尚未保存。
- 按保存或按 Ctrl+s。它应该保存，并且您的列应该是唯一的。
Or set column as unique from the SQL Query window:
或者在 SQL 查询窗口中将列设置为唯一：
alter table location_key drop constraint pinky;
alter table your_table add constraint pinky unique(yourcolumn);
Changes take effect immediately:
更改立即生效：
Command(s) completed successfully.
回答by Tony L.
Here's another way through the GUI that does exactly what your script does even though it goes through Indexes (not Constraints) in the object explorer.
这是通过 GUI 的另一种方式，即使它通过对象资源管理器中的索引（而不是约束），它也可以完全执行您的脚本所做的工作。
- Right click on "Indexes" and click "New Index..." (note: this is disabled if you have the table open in design view)
- 右键单击“索引”并单击“新建索引...”（注意：如果您在设计视图中打开表，则禁用此功能）
- Give new index a name ("U_Name"), check "Unique", and click "Add..."
- 为新索引命名（“U_Name”），选中“唯一”，然后单击“添加...”
- Select "Name" column in the next windown
- 在下一个窗口中选择“名称”列
- Click OK in both windows
- 在两个窗口中单击确定
回答by detzu
One thing not clearly covered is that microsoft sql is creating in the background an unique index for the added constraint
没有明确说明的一件事是 microsoft sql 在后台为添加的约束创建唯一索引
create table Customer ( id int primary key identity (1,1) , name nvarchar(128) )
--Commands completed successfully.
sp_help Customer
---> index
--index_name index_description index_keys
--PK__Customer__3213E83FCC4A1DFA clustered, unique, primary key located on PRIMARY id
---> constraint
--constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
--PRIMARY KEY (clustered) PK__Customer__3213E83FCC4A1DFA (n/a) (n/a) (n/a) (n/a) id
---- now adding the unique constraint
ALTER TABLE Customer ADD CONSTRAINT U_Name UNIQUE(Name)
-- Commands completed successfully.
sp_help Customer
---> index
---index_name index_description index_keys
---PK__Customer__3213E83FCC4A1DFA clustered, unique, primary key located on PRIMARY id
---U_Name nonclustered, unique, unique key located on PRIMARY name
---> constraint
---constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
---PRIMARY KEY (clustered) PK__Customer__3213E83FCC4A1DFA (n/a) (n/a) (n/a) (n/a) id
---UNIQUE (non-clustered) U_Name (n/a) (n/a) (n/a) (n/a) name
as you can see , there is a new constraint and a new index U_Name
如您所见，有一个新约束和一个新索引U_Name
