Conditional unique constraint in oracle
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));