Re: Unique Constraint on Multiple columns

Dave (daveg.01@xxxxxxxxx) 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@xxxxxxxxxxxxx

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

.

Relevant Pages

  • Re: Linking SQL Server tables
    ... The linked table will be read only if there is no unique index or constraint ... >>> I'm trying to link tables from SQL Server to my Access 2003 database. ... >>> in the Microsoft SQL Server database. ...
    (microsoft.public.access.externaldata)
  • Re: Nonclustered UNIQUE INDEX
    ... Table window for a table in SQL Server 2000, ... UNIQUE setting: Constraint, and Index. ... >CREATE UNIQUE INDEX splunge ON blat ... >INSERT blat SELECT 1 ...
    (microsoft.public.sqlserver.server)
  • Re: Why does EM script clustered indexes different ways?
    ... When you create a unique index in EM, it is by default created as a UNIQUE ... create a UNIQUE or PRIMARY KEY constraint on the column ... Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique ... > script (so i can rule out the random chance of how the engine decided to ...
    (microsoft.public.sqlserver.programming)
  • Re: Unique constraint vs unique index
    ... to have an index too slap a unique index on. ... > What is the difference between using a unique constraint and a unique index? ... > primary key is emp_id), and you want to ensure that Social Security numbers ... > Social Security number for more than one employee, ...
    (microsoft.public.sqlserver)
  • Do I need a PK on a join table?
    ... I have a client table whose PK is surrogate key client_id. ... CONSTRAINT CLIENT_ID PRIMARY KEY ); ... FOREIGN KEY REFERENCES OLTP.PRODUCT; ... CREATE UNIQUE INDEX CLIENT_PRODUCT ON CLIENT_PRODUCT ...
    (comp.databases.theory)