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

Solution for a column that allows null

If your column allows null then a unique CONSTRAINT won't work - (unless you really only want one row with a null there).

Instead you can create a filtered index with a WHERE clause to achieve this:

CREATE UNIQUE NONCLUSTERED INDEX UX_Order_ColumnName ON [dbo].[Order]
(
    OptionalColumnName
)
WHERE (OptionalColumnName IS NOT NULL)

---

Error you'll get with ADD CONSTRAINT:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Order' and the index name 'UX_Order_ColumnName'. The duplicate key value is ().

Answer 2

1. ALTER TABLE [<Table_Name_Here>] DROP CONSTRAINT [<Constraint_Name_Here>];

   • This statement removes the constraint named <Constraint_Name_Here> from the table <Table_Name_Here>.
   • Constraints enforce data integrity in the database. In this case, the specified constraint is being dropped.

2. ALTER TABLE [<Table_Name_Here>] ADD CONSTRAINT [<Constraint_Name_Here>] UNIQUE ([<Column_Name_Here>]);

   • This statement adds a new constraint named <Constraint_Name_Here> to the table <Table_Name_Here>.
   • The constraint is defined as a UNIQUE constraint on the column <Column_Name_Here>.
   • This means that the values in the <Column_Name_Here> column must be unique across all rows in the table.

In summary:

• The first statement drops an existing constraint.
• The second statement adds a new UNIQUE constraint to a specific column.
• Please note adding a unique constraint to a specific column typically creates a unique non-clustered index to enforce uniqueness.

Answer 3

When you are in a Design view of your table, click on your desired column and expand the Table Designer tab at the top toolbar. Select the Indexes/Keys option.~

Then Add a new constraint and choose the right column and set its type to Unique Key, and name it somehow. And that's all! ^^

Answer 4

One thing not clearly covered is that microsoft sql is creating in the background an unique index for the added constraint

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

Answer 5

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.

1. Right click on "Indexes" and click "New Index..." (note: this is disabled if you have the table open in design view)

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

3. Select "Name" column in the next windown

4. Click OK in both windows

Answer 6

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.

Answer 7

To create these constraints through the GUI you need the "indexes and keys" dialogue not the check constraints one.

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.