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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
flavour404
  • 6,184
  • 30
  • 105
  • 136

2 Answers2

36
ALTER TABLE YourTable
ADD CONSTRAINT UQ_YourTable_ConstraintName UNIQUE(A, B)
AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • 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' – flavour404 Feb 02 '10 at 00:01
  • 1
    Easier/quicker than even going in to the table designer - just open a new query tab in SSMS and run the SQL. – AdaTheDev Feb 02 '10 at 00:18
  • +1 - unique constraint for the win, it tells somebody who comes to the system in the future more than a unique index does. – Matt Whitfield Feb 02 '10 at 01:43
  • I am keeping all this in mind, I need to read some more of the msdn, time is an issue :) – flavour404 Feb 03 '10 at 02:54
  • @MattWhitfield: it is still a unique index, it will create a new index exactly the same way `CREATE UNIQUE INDEX` does it. – Abel Apr 27 '14 at 19:19
  • 1
    @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. – Matt Whitfield Apr 29 '14 at 09:56
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.

Rashmi Pandit
  • 23,230
  • 17
  • 71
  • 111
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Yep, we managed this via the table designer and it worked, cool. – flavour404 Feb 02 '10 at 00:12
  • 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 Feb 02 '10 at 01:50
  • @Ralph Shillington: A unique index can also have INCLUDE columns. A constraint can not. For more info: http://stackoverflow.com/questions/2152176/unique-way-to-have-unique-rows-in-table – OMG Ponies Feb 02 '10 at 02:24
  • 1
    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. – Ralph Shillington Feb 02 '10 at 11:01
  • 1
    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... – James Becwar May 02 '13 at 21:08