Oracle Unique Constraint based on column value

Question

I have the following unique constraint

dup_Checklist_QNum UNIQUE (QUESTION_NO, IS_ACTIVE)

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

All seemed fine until I had to rev a question for the 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 the constraint to only apply when IS_ACTIVE=1

I guess as a work around, I could just deactivate questions by using a unique number, 0.1, 0.2, 0.3, etc... Provided my application only cares when the value is 1, and ignores all other questions. — jeff
– jeff, Commented Jul 4, 2012 at 22:15
possible duplicate of Conditional unique constraint in oracle db — Justin Cave
– Justin Cave, Commented Jul 4, 2012 at 22:22

Justin Cave · Accepted Answer · 2012-07-04 22:17:56Z

18

You can create a unique function-based index

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

This takes advantage of the fact that Oracle b-tree indexes do not store data where the leaf block data would be entirely NULL.

answered Jul 4, 2012 at 22:17

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

I wonder when Oracle finally gives us less clumsy partial indexes by allowing a WHERE clause for the CREATE INDEX.

eriksmith200 Over a year ago

I have a similar case but the unique constraint needs to be on 2 columns, I'm trying to wrap my head around the syntax

Vitalizzare · Accepted Answer · 2024-06-18 08:13:46Z

0

For multiple columns, you can create unique index using the following syntax:

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

edited Jun 18, 2024 at 8:13

Vitalizzare

7,61611 gold badges23 silver badges49 bronze badges

answered Jun 11, 2024 at 15:21

Gopi

217 bronze badges

Collectives™ on Stack Overflow

Oracle Unique Constraint based on column value

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related