Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle Unique Constraint based on column value

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

like image 805
jeff Avatar asked Jul 04 '12 22:07

jeff


People also ask

Is a constraint that can be defined at column level?

Column-level constraints refer to a single column in the table and do not specify a column name (except check constraints). They refer to the column that they follow. Table-level constraints refer to one or more columns in the table. Table-level constraints specify the names of the columns to which they apply.

How do you find which column has unique constraints?

To check for a unique constraint use the already provided method: select count(*) cnt from user_constraints uc where uc. table_name='YOUR_TABLE_NAME' and uc.

Is unique a column constraint?

The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

How do you set a column as unique in Oracle?

The syntax for creating a unique constraint using an ALTER TABLE statement in Oracle is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.


1 Answers

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.

like image 60
Justin Cave Avatar answered Oct 24 '22 06:10

Justin Cave