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

User · Answer

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

User · Answer

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    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          Select  Name  column in the next windown      Click OK in both windows

User · Answer

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    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    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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to unique-constraint