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?
This question is related to
sql
sql-server
sql-server-2008
unique-constraint
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
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.
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.
alter table location_key drop constraint pinky;
alter table your_table add constraint pinky unique(yourcolumn);
Changes take effect immediately:
Command(s) completed successfully.
Source: Stackoverflow.com