How to create a unique constraint for my column (SQL Server 2008 R2)?

Question

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

There seem to be two ways to do this: "unique index" and "unique constraint". They are not much different from what I understand, although most restrictions are recommended for most, because you also get the index automatically.

How to create a unique constraint?

ALTER TABLE Customer ADD CONSTRAINT U_Name UNIQUE(Name) 

Is there a way to create a unique constraint through SQL Server Management Studio?

Answer 1

To create these constraints through a graphical interface, you need the indexes and keys dialog, not control constraints.

But in your case, you just need to run a piece of code that you already have. It does not need to be entered into the expression dialog at all.

Answer 2

Set the column as unique in SQL Server from the graphical user interface:

They really make you run around the barn to do this using the GUI:

Before you begin, make sure the column does not violate a unique constraint.

• Open SQL Server Management Studio.
• Right-click your spreadsheet, click Design.
• Right-click on the column that you want to change, a pop-up menu will appear, click "Indexes / Keys".
• Click the Add button.
• Expand the General tab.
• Make sure that you have a column that you want to make unique, selected in the "columns" field.
• Change the Type field to Unique Key.
• Click "Close."
• You see a small asterisk in the file window, which means that the changes have not yet been saved.
• Click "Save" or press Ctrl + s. It should save, and your column should be unique.

Or set the 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. 

Answer 3

Are you sure the column is currently unique? You can find out:

 select name,count(*) from customer group by name having count(*) > 1 

This will give you any duplicates.

Answer 4

Here's another way through the GUI, which does exactly what your script does, even if it goes through Indexes (not Constraints) in the object explorer.

• Right-click on "Indexes" and click on "New Index ..." (note: this is disabled if the table is open in view mode)

2. Give a new name index ("U_Name"), check "Unique", and click "Add ..."

3. Select the "Name" column in the next window

4. Click OK in both windows