CODEWITHSUNDEEP
oracle-databaseconstraintsddl
S.Nisar Ahmad
S.Nisar Ahmad

Reputation: 13

Unique Constraint Allow Null for One column in Oracle

I have the following Table Structure 

tbl_exam (Year,ExamCode,RollNo)

1). Data in Table is:

(2017,1,NULL)
(2017,1,NULL)  
(2017,1,1)
(2017,1,2)

Allow the above values

2). 

(2017,1,1)
(2017,1,1) **Not Allowed**

How to add Unique Constraint which allows null value for RollNo Column

Edit: Need Composite Key Constraint

Solved: Thanks To David Aldridge

create unique index exam_uidx on tbl_exam
(
Nvl2(RollNo, Year    , null),
Nvl2(RollNo, Exam_Code, null),
RollNo
)

Upvotes: 1

Views: 3133

Answers (1)

David Aldridge
David Aldridge

Reputation: 52336

I expect that you could do this by placing a unique constraint on:

(
  Nvl2(RollNo, Year    , null),
  Nvl2(RollNo, ExamCode, null),
  RollNo
)

Not tested

Upvotes: 2

Related Questions