How to create UNIQUE constraint in SSMS 2012

Question

I'm totally new to SQL Server, I use version 2012.

I have a table with the following structure:

Attendance
--------------
TagID     (FK)
SessionID (FK)       
ScanningTime

I need to create a unique constraint based on two columns (TagID and SessionID)

I'm not sure how to do this. I have created a query and tried this code:

ALTER TABLE Attendance ADD CONSTRAINT UNIQUE NONCLUSTERED
(
    TagID,
    SessionID
)

But when I try to execute it, I receive this error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'UNIQUE'.

What is the proper way to alter the table using SSMS? Should I create a query each time I want to do so?

score 11 · Accepted Answer · answered Aug 14 '13 at 14:12

11

You were almost there. If you use the constraint keyword you need to provide a name.

ALTER TABLE Attendance ADD CONSTRAINT UQ_TagID_SessionID UNIQUE NONCLUSTERED
(
    TagID,
    SessionID
)

Or alternatively

ALTER TABLE Attendance ADD UNIQUE NONCLUSTERED
(
    TagID,
    SessionID
)

also works but then the constraint is auto named

answered Aug 14 '13 at 14:12

Martin Smith

438,706
87
741
845

Thanks, could you also answer my question in bold? – jaspernorth Aug 14 '13 at 14:16
1

I would strongly suggest naming constraints - first option. – Nenad Zivkovic Aug 14 '13 at 14:17
@jaspernorth - RE: Your bold question my preference is generally to avoid the designer and use TSQL but it is also possible to do this visually in SSMS through the [indexes and keys dialogue](http://technet.microsoft.com/en-us/library/ms187019.aspx). – Martin Smith Aug 14 '13 at 14:19

score 11 · Answer 2 · edited Jun 04 '20 at 16:32

11

If you prefer to do it via GUI instead of via ALTER statements, you can also right-click on the table in Object Explorer, select Design, then right-click somewhere on the empty background, and select Indexes/Keys. This will open a dialog where you select "Unique key" as the type.

Doing changes via GUI is a fast way for actions you tend to do rarely and hence are not sure about the syntax.

Many Management Studio dialogs - but not this one, probably as it is a sub dialog of the table designer dialog - have a "Script" button on the top left, which writes the action you are configuring via GUI to a query window so that you can save them for future similar tasks, or a copy and paste them, should you need similar actions.

edited Jun 04 '20 at 16:32

ΩmegaMan

29,542
12
100
122

answered Aug 14 '13 at 14:22

FrankPl

13,205
2
14
40

3

After you close that dialog, constraint is not created instantly, but only when you save/close the table designer. Therefore, you can right click again on empty background and select 'Generate Change Script..." option. – Nenad Zivkovic Aug 14 '13 at 14:41
@NenadZivkovic Your comment was really helpfull ;). I didn't think to click save after adding the constraint – MatPag Apr 24 '18 at 15:43

How to create UNIQUE constraint in SSMS 2012

2 Answers2