CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Razvan
Razvan

Reputation: 5

How to add an unique constraint on more columns in SQL Server 2008 (R2)?

CREATE TABLE tImprumuturi
  (
     ID_Imprumut  INT IDENTITY PRIMARY KEY,
     DataImprumut DATE DEFAULT getdate(),
     DataScadenta AS ( dateadd(day, 2, DataImprumut) ) persisted,
     CodCD        CHAR(10) FOREIGN KEY REFERENCES tCD(CodCd)NOT NULL,
     CodCV        CHAR(10) FOREIGN KEY REFERENCES tCaseteVideo(CodCaseta),
     CodAb        CHAR(10) FOREIGN KEY REFERENCES tAbonati(CodAbonat) NOT NULL,
     CONSTRAINT ucCodes UNIQUE (CodCD, CodCV, CodAb)
  )

I don't want to have many of the same CodCD OR CodCV OR CodAb but never two records with the same CodCD AND CodCV AND CodAb. My code is at the top as you can see, and even if I have the instruction CONSTRAINT ucCodes UNIQUE (CodCD,CodCV,CodAb), it still let me to insert two or more records with the same CodCD and CodCV and CodAb. Below you can see my records

insert into tImprumuturi(CodCV,CodCD,CodAb)
values('CV21','CD20','ab9'),
      ('CV21','CD19','ab9')

Upvotes: 0

Views: 552

Answers (2)

Mr Jones
Mr Jones

Reputation: 1198

CONSTRAINT ucCodes UNIQUE (CodCD, CodCV, CodAb)

The above sets a unique constraint on those three values, as if they were one value.

This would mean that the following would work:

set values('a', 'b', 'c'),
          ('a', 'b', 'd')

and the following would fail:

set values('a', 'b', 'c'),
          ('a', 'b', 'c')

EDIT

In response to your comment, you could do the following:

CONSTRAINT ucCodCD UNIQUE (CodCD, CodCV) 
CONSTRAINT ucCodCV UNIQUE (CodCD, CodAb) 
CONSTRAINT ucCodAb UNIQUE (CodCV, CodAb)

This would mean that the following would work:

set values('a', 'b', 'c'),
          ('a', 'd', 'e')

and the following would fail:

set values('a', 'b', 'c'),
          ('a', 'b', 'd')

Upvotes: 3

Sonam
Sonam

Reputation: 3466

Its working exactly as you have defined it in the table. As you have created constraint on the combination of CodCV,CodCD,CodAb, so here ('CV21','CD20','ab9') and ('CV21','CD19','ab9') would be considered as unique.(value is different for CodCD). May be you should try creating 2 separate unique constraint as following:

CONSTRAINT ucCod_CD UNIQUE (CodCD) 
CONSTRAINT ucCod_CV UNIQUE (CodCV) 
CONSTRAINT ucCod_Ab UNIQUE (CodAb)

Upvotes: 2

Related Questions