By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,320 Members | 1,893 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,320 IT Pros & Developers. It's quick & easy.

Unique Constraint on Multiple columns

P: n/a
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,

Oct 31 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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)
Oct 31 '05 #2

P: n/a
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

Oct 31 '05 #3

P: n/a
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.

Nov 2 '05 #4

P: n/a
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)
Nov 2 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.