How can I create a unique constraint on my column (SQL Server 2008 R2)?

Question

I have SQL Server 2008 R2 and I want to set a unique column.

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?

Answer 1

Set column as unique in SQL Server from the GUI:

They really make you run around the barn to do it with the GUI:

Make sure your column does not violate the unique constraint before you begin.

1. Open SQL Server Management Studio.
2. Right click your Table, click "Design".
3. Right click the column you want to edit, a popup menu appears, click Indexes/Keys.
4. Click the "Add" Button.
5. Expand the "General" tab.
6. Make sure you have the column you want to make unique selected in the "columns" box.
7. Change the "Type" box to "Unique Key".
8. Click "Close".
9. You see a little asterisk in the file window, this means changes are not yet saved.
10. Press Save or hit Ctrl+s. It should save, and your column should be unique.

Or set column as unique from the SQL Query window:

alter table location_key drop constraint pinky;
alter table your_table add constraint pinky unique(yourcolumn);

Changes take effect immediately:

Command(s) completed successfully.