How to create UNIQUE constraint in SSMS 2012 [duplicate]

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?

Answer 1

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

Answer 2

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.