51

I have an existing table with data. I have just added a new column but I cannot find how to add a unique constraint on that column. Could someone please advise? Right-clicking and selecting "check constraints" wasn't helpful.

intrigued_66
  • 16,082
  • 51
  • 118
  • 189

4 Answers4

59

You need to right-click in the table designer and pick Indexes/Keys:

enter image description here

Then a dialog pops up and you can add a new index to the list of indices (on the left hand side) and define it to be a unique index:

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 17
    That's an unique index, not an unique constraint. Both create indexes, but there are differences between them (see http://databases.about.com/od/sqlserver/a/unique.htm). If you want a UNIQUE constraint, you should select Type = Unique Key. – Guillermo Gutiérrez Aug 23 '13 at 23:10
  • 1
    @guillegr123: yes, you're right - but you're also nitpicking here..... in the end, both approaches avoid duplicates, and in the end, both approaches will result in a unique index in SQL Server. I prefer to use the unique index, because it allows me to set certain properties (like fill factor) explicitly, if needed - which the unique constraint doesn't allow me to set. – marc_s Aug 24 '13 at 06:37
34

Do right-click in the table designer and choose Indexes/Keys.

enter image description here

Indexes/Keys window will open. Click the button Add, to create the new index/key, and choose the column to be unique in the Columns property:

enter image description here

In the properties of the new index/key, set Type to Unique Key:

enter image description here

And this is the generated code for the unique constraint:

ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [IX_Table_1] UNIQUE NONCLUSTERED 
(
    [myUniqueColumn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Guillermo Gutiérrez
  • 17,273
  • 17
  • 89
  • 116
  • 1
    If I wanted to make a unique constraint using several columns then I can add all of the columns I want to the `Columns` property of the new index/key? (e.g. FirstName and Surname - can have duplicates of each but not of both)? – Ben Nov 10 '13 at 10:50
  • 2
    Yes. If you push the "..." button on the Columns property, a window is displayed, and there you can choose all the columns you want, and also specify the order of the column in that index. And the combination of all the selected columns are taken into account for the uniqueness of the key, as you say in your example. – Guillermo Gutiérrez Nov 10 '13 at 13:48
4

Try right clicking and choosing Indexes/Keys, adding a new index and setting Is Unique to Yes.

podiluska
  • 50,950
  • 7
  • 98
  • 104
0

You just Right Click from which column you need to add unique key from your table and you can select the Indexes/Keys. Then you can add or delete the column which you want to set unique key

Blood-HaZaRd
  • 2,049
  • 2
  • 20
  • 43
dinesh.k
  • 197
  • 1
  • 8
  • 24