unique key based on 2 columns in SQl Server 2008?

Question

is it posible to have SQL Server to have a unique key based on 2 columns?

I can have duplicates in both columns but not at the same time:

MfgID  :   CustNum   
1      :     Cust01   
1      :     Cust02  
2      :     Cust02  
1      :     Cust03  
3      :     Cust03  
3      :     Cust04  
1      :     Cust02

In the example above all of these would be fine EXCEPT the last row. I'd like SQL Server to kck out an error on this for me.

score 22 · Accepted Answer · answered Mar 03 '11 at 17:32

22

CREATE UNIQUE NONCLUSTERED INDEX IX_TableName_MfgID_CustNum ON TableName
(
        MfgID  ,
        Column2
) WITH( IGNORE_DUP_KEY = OFF)

answered Mar 03 '11 at 17:32

amit_g

30,880
8
61
118

This is by far most simple and elegant solution. – Sam Grondahl Nov 28 '12 at 15:03
3

Shoud it not be a KEY in stead of an index? – Michel Jan 31 '13 at 09:27

score 9 · Answer 2 · edited Mar 03 '11 at 19:41

9

CREATE TABLE table1(
  MfgID INTEGER NOT NULL,
  CustNum VARCHAR(255) NOT NULL,
  CONSTRAINT unique_1 UNIQUE (MfgID, CustNum)
)

OR

ALTER TABLE table1 ADD CONSTRAINT unique_1 UNIQUE(fgID, CustNum)

edited Mar 03 '11 at 19:41

RichardTheKiwi

105,798
26
196
262

answered Mar 03 '11 at 18:09

Rahul R

181
1
1
5

Sachin Shanbhag · Answer 3 · 2011-03-03T17:36:08.057

3

Yes, It is called composite primary key.

CREATE TABLE table1(
  MfgID INTEGER NOT NULL,
  CustNum VARCHAR(255) NOT NULL,
  PRIMARY KEY (MfgID, CustNum)
)

edited Mar 03 '11 at 17:36

answered Mar 03 '11 at 17:29

Sachin Shanbhag

54,530
11
89
103

1

Doesn't have to be primary key it cat be unique composite index too. – Kuberchaun Mar 03 '11 at 19:46

unique key based on 2 columns in SQl Server 2008?

3 Answers3

Linked