Oracle Unique Constraint Based on Column Value

Question

I have the following unique limitation

dup_Checklist_QNum UNIQUE (QUESTION_NO, IS_ACTIVE) 

I am trying to prevent two questions having the same question number when it is active (value IS_ACTIVE = 1).

Everything seemed beautiful until I repeated the question a second time.

 QUESTION_NO=1, TEXT="Have you..", REV=1 IS_ACTIVE=0 QUESTION_NO=1, TEXT="Have you..", REV=2 IS_ACTIVE=0 <-- This should be ok but constraint was violated QUESTION_NO=1, TEXT="Have you..", REV=3 IS_ACTIVE=1 QUESTION_NO=1, TEXT="Have you..", REV=3 IS_ACTIVE=1 <-- This should be throw constraint exception 

I need constrint to be applied only when IS_ACTIVE = 1

Answer 1

You can create a unique index based on functions

 CREATE UNIQUE INDEX idx_dup_active ON <<table name>>( CASE WHEN is_active = 1 THEN question_no ELSE NULL END ); 

This exploits the fact that Oracle b-tree indexes do not store data where leaf block data is completely NULL.