Oracle Unique Constraint with Expression

Question

Does Oracle support constraints with expressions like so?

Notice Z = 'N'

ALTER TABLE A ADD CONSTRAINT U_A_KEY UNIQUE(X,Y,Z = 'N');

Is this Unique constraint possible?

Example:

INSERT INTO A VALUES('X','Y','N');  --OK
INSERT INTO A VALUES('X','Y','Y');  --OK
INSERT INTO A VALUES('X','Y','Y');  --OK
INSERT INTO A VALUES('X','Y','N');  --VOLIATION

Answer 1

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

Answer 2

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.