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

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

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  --- gt  index --index name    index description   index keys --PK  Customer  3213E83FCC4A1DFA    clustered  unique  primary key located on PRIMARY   id  --- gt  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  --- gt  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  --- gt  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

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

Set column as unique in SQL Server from the GUI:

Or set column as unique from the SQL Query window:

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to unique-constraint