Oracle Unique Constraint with Expression

Maybe this gives an idea

drop table tq84_n;create table tq84_n (   x number,    y number,    z varchar2(10));create unique index tq84_n_x on tq84_n (  case when z = 'N' then x || '-' || y        else null  end);

Later:

insert into tq84_n values (4,5, 'N');insert into tq84_n values (9,6, 'Y');insert into tq84_n values (9,6, 'Y');insert into tq84_n values (4,5, 'Y');insert into tq84_n values (4,5, 'N');

Last one throws:

ORA-00001: unique constraint (SPEZMDBA.TQ84_N_X) violated

The simplest approach in this case is generally to create a function based index. Something like

CREATE UNIQUE INDEX u_a_key    ON a( (CASE WHEN z = 'N' THEN x ELSE null END),          (CASE WHEN z = 'N' THEN y ELSE null END) );

If z is not 'N', both CASE statements evaluate to NULL and Oracle doesn't have to store the x & y values in the index structure (making the index smaller). If z is 'N', the x & y values are both stored in the index and the index behaves just like any other compound index.

What I do in that sitaution is to create a column e.g. Z in your case, which has:

• A particular value (e.g. your "N") in the case I need it to be unique
• Null otherwise, meaning unknown: two unknown values are considered to be not equal to one another.

Then you can create your unique constraint UNIQUE(X,Y,Z).

Add two rows with equal X and Y and Z="N" and you'll get an error; add two rows with equal X and Y both with Z=null and you won't.