32

How do you add a unique constraint in SQL Server 2005 to two columns? So lets say that I have:

PK, A, B ...
x1  1  1
x2  1  2
x3  2  1
x4  2  2

I should not be able to add another row 'x5' and have the values for A and B be 1,1 as they are already in the database in x1?

Ok we managed to get it to work and thanks to OMG. Go to the table view, select the two columns, right click and select 'indexes/keys' - general tab, select the columns you want to be unique and then set 'is unique' to true. This is using the table designer.

Thanks.

Improve this question
2

2 Answers 2

Reset to default
36 
ALTER TABLE YourTable
ADD CONSTRAINT UQ_YourTable_ConstraintName UNIQUE(A, B)
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

What is the syntax that goes in the constraint box. We highlight the two columns, right click 'constraints' and put in 'UNIQUE(DocType,PubID)' and it sais that there is an error - 'error validating constraint'
Easier/quicker than even going in to the table designer - just open a new query tab in SSMS and run the SQL.
+1 - unique constraint for the win, it tells somebody who comes to the system in the future more than a unique index does.
I am keeping all this in mind, I need to read some more of the msdn, time is an issue :)
@Abel - sure, but what does it imply to the next DBA who comes to the system? A constraint implies something in the data model is important, and index implies that performance was the primary concern.
|
35

In SQL Server, a unique constraint is really implemented as a unique index. Use:

CREATE UNIQUE INDEX <uix_name> ON <table_name>(<col_A>, <col_B>)

For more info, see this MSDN page.

Improve this answer

5 Comments

Yep, we managed this via the table designer and it worked, cool.
I would agree with @adaTheDev and add table constraint rather than adding a unique index "raw". Creating indexes implies possible performance improvement. But then one could drop this index before realizing that it's there not for performance but for functionality. You'd never drop a constraint without realizing that most be serving some functional purpose. (admittedly a unique constraint creates a unique index end but it can not be dropped directly, only through dropping the constraint)
@Ralph Shillington: A unique index can also have INCLUDE columns. A constraint can not. For more info: stackoverflow.com/questions/2152176/…
you're absolutely right. However I tend to think of that as an oddity. The INCLUDE is for performance, which may need "tweeking" as the system evolve, ie. columns included today may not be valuable to the workload tomorrow. However I think it's reasonable to say UNIQUEness is a constraint on the data that is not tied to a given workload or system usage, but to application functionality. It is an unfortunate, and perhaps rare, case that one would need to mix and match.
Just a heads up if you disable indexing, it disables the unique constraint that the index gives the table. This caused us a lot of headaches one day...

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.