Posted by Erland Sommarskog on 11/01/05 00:53

Dave (daveg.01@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, esquel@sommarskog.se

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

• Next in forum: Re: Stored Procs and db_owner
• Prev in forum: Re: Unique Constraint on Multiple columns
• Thread view: Re: Unique Constraint on Multiple columns

[Reply to this message]