Oracle unique constraint with expression

Question

Does Oracle support constraints on expressions like this?

Note Z = 'N'

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

Is it possible Unique constraint ?

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 ); 

Further:

 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 roll:

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

Answer 2

The simplest approach in this case is, as a rule, to create an index based on a function. 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 should not store the x and y values ​​in the index structure (making the index smaller). If z is "N", the values ​​of x and y are stored in the index, and the index behaves exactly like any other join index.

Answer 3

What I do in this sitaution is to create a column, for example. Z in your case, which has:

• A specific value (for example, your "N") in case I need it to be unique.
• Otherwise, zero means an unknown value: two unknown values ​​are considered not equal to each other.

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

Add two lines with equal X and Y and Z = "N" and you will get an error message; add two lines with equal X and Y with Z = null and you won’t.