CODEWITHSUNDEEP
sql-serversql-server-2008
Slee
Slee

Reputation: 28248

unique key based on 2 columns in SQl Server 2008?

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.

Upvotes: 15

Views: 20944

Answers (3)

Rahul R
Rahul R

Reputation: 181

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)

Upvotes: 9

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

Yes, It is called composite primary key.

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

Upvotes: 3

amit_g
amit_g

Reputation: 31250

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

Upvotes: 22

Related Questions