Conditional unique constraint in oracle

Question

I currently have this constraint added to table MY_TABLE

ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_UNQ_01 UNIQUE (COLUMN1, COLUMN2, COLUMN3) USING INDEX TABLESPACE "MY_TABLE_INDEX";

The problem is that I have added a new column:

ALTER TABLE MY_TABLE ADD (DRAFT NUMBER(1,0));

Now the constraint should apply only when the column DRAFT = 0

Is there some way how to do this?

Answer 1

In 11g, you could define a constraint on virtual columns:

ALTER TABLE my_table ADD (
   draft_column1 <DATATYPE> GENERATED ALWAYS AS (DECODE(draft, 0, column1)),
   draft_column2 <DATATYPE> GENERATED ALWAYS AS (DECODE(draft, 0, column2)),
   draft_column3 <DATATYPE> GENERATED ALWAYS AS (DECODE(draft, 0, column3)),
   CONSTRAINT UNQ_02 UNIQUE(draft_column1, draft_column2, draft_column3)
);

In 10g and before, you can use a function based unique index and the fact that all-NULL index entries are not recorded. So the following index will guarantee the unicity only when draft=0:

CREATE UNIQUE INDEX UNQ_03 ON my_table (
   DECODE(draft, 0, column1),
   DECODE(draft, 0, column2),
   DECODE(draft, 0, column3)
);