Oracle: how to formulate a complex constraint when

Question

The following conditional restrictions simply do not work. Is it possible to articulate in the workplace?

ALTER TABLE eni_trasc_voci_pwr_fatt ADD CONSTRAINT tvp_constraint_1 CHECK ( CASE WHEN TVP_CODICE_ASSOGGETAMEN = '-' THEN tvp_regione IS NULL END); 

Answer 1

Try the following:

 ALTER TABLE eni_trasc_voci_pwr_fatt ADD CONSTRAINT tvp_constraint_1 CHECK ( CASE WHEN TVP_CODICE_ASSOGGETAMEN = '-' THEN tvp_regione else null end IS NULL); 

Answer 2

It looks like you want a logical implication here ("if X, then Y"), which is logically equivalent to "(not X) or Y". CASE is used to create the final map.

Your restriction should be something like

TVP_CODICE_ASSOGGETAMEN != '-' OR TVP_REGIONE IS NULL

Answer 3

I think you can do what you want without the case statement:

 create table t1 (c1 varchar2(10), c2 varchar2(10)); alter table t1 add constraint t1_chk1 check ( (c1 = '-' and c2 is null) or (c1 != '-' and c2 is not null) ); 

Now try pasting some values:

 SQL> insert into t1 values ('-', 'reject'); insert into t1 values ('-', 'reject') * ERROR at line 1: ORA-02290: check constraint (SODONNEL.T1_CHK1) violated SQL> SQL> insert into t1 values ('-', null); 1 row created. SQL> SQL> insert into t1 values ('a', null); insert into t1 values ('a', null) * ERROR at line 1: ORA-02290: check constraint (SODONNEL.T1_CHK1) violated SQL> SQL> insert into t1 values ('a', 'accept'); 1 row created.