Unique Constraint on Multiple columns

[Dave]

Can you create a unique constraint on multiple columns, or does it have
to be implemented as a unique index?

If possible can someone please post some sample code?

Thanks,

[Hugo Kornelis]

On 31 Oct 2005 14:42:28 -0800, Dave wrote:

Can you create a unique constraint on multiple columns, or does it have
to be implemented as a unique index?

If possible can someone please post some sample code?

Thanks,

Hi Dave,

CREATE TABLE Example
(Col1 int NOT NULL,
Col2 int NOT NULL,
UNIQUE (Col1, Col2)
)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Dave (da******@gmail.com) writes:

Can you create a unique constraint on multiple columns, or does it have
to be implemented as a unique index?
A UNIQUE constraint is always implemented as a unique index. But an
index can span more than one column.
If possible can someone please post some sample code?

Here is a real-world table:

CREATE TABLE officerefunds (
orfid int NOT NULL,
chtcode aba_chtcode NOT NULL
CONSTRAINT ckc_orf_chtcode CHECK
(chtcode NOT IN ('MIN', 'MAX', 'CTX', 'STX')),
ofcid smallint NULL,
ityid smallint NULL,
insid aba_insid NULL,
chgid smallint NULL,
officerefund aba_fraction NOT NULL,
deductfee bit NOT NULL,
CONSTRAINT pk_orf PRIMARY KEY CLUSTERED (orfid),
CONSTRAINT ak_orf UNIQUE NONCLUSTERED
(chtcode, ofcid, ityid, insid, chgid),
CONSTRAINT ckt_orf_instrument CHECK
(NOT (ityid IS NOT NULL AND insid IS NOT NULL))
)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dave]

That is kind of what i though. So it is always an index? I could not
find that documentation anywhere, but I know I have read that some
where too.

[Hugo Kornelis]

On 2 Nov 2005 08:36:48 -0800, Dave wrote:

That is kind of what i though. So it is always an index? I could not
find that documentation anywhere, but I know I have read that some
where too.

Hi Dave,

In SQL Server, a unique index will always be created forn each PRIMARY
KEY or UNIQUE constraint. This index is used to enforce the constraint.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)