ansaurus

Question

SQL Server 2005 Unique constraint on two columns

Answer 1

+5 A:

ALTER TABLE YourTable
ADD CONSTRAINT UQ_YourTable_ConstraintName UNIQUE(A, B)

AdaTheDev 2010-02-01 23:26:30

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 2010-02-02 00:01:30

Easier/quicker than even going in to the table designer - just open a new query tab in SSMS and run the SQL.

AdaTheDev 2010-02-02 00:18:05

+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 2010-02-02 01:43:21

I am keeping all this in mind, I need to read some more of the msdn, time is an issue :)

flavour404 2010-02-03 02:54:59

Answer 2

+3 A:

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

CREATE UNIQUE INDEX uix_name ON table(A, B)

For more info, see this MSDN page.

OMG Ponies 2010-02-01 23:31:37

Yep, we managed this via the table designer and it worked, cool.

flavour404 2010-02-02 00:12:23

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 2010-02-02 01:50:43

@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 2010-02-02 02:24:35

@OMG Ponies: 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 2010-02-02 11:01:21

ansaurus

tags:

views:

answers:

SQL Server 2005 Unique constraint on two columns

related questions