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
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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With