Oracle: how to formulate a complex constraint with case when
Try the following:
ALTER TABLE eni_trasc_voci_pwr_fattADD CONSTRAINT tvp_constraint_1 CHECK (CASE WHEN TVP_CODICE_ASSOGGETAMEN = '-' THEN tvp_regione else null end IS NULL);
It looks like you want logical implication here ("if X then Y"), which is logically equivalent to "(not X) or Y". CASE is used to create a finite map.
Your constraint should be something like
TVP_CODICE_ASSOGGETAMEN != '-' OR TVP_REGIONE IS NULL
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 and insert some values:
SQL> insert into t1 values ('-', 'reject');insert into t1 values ('-', 'reject') *ERROR at line 1:ORA-02290: check constraint (SODONNEL.T1_CHK1) violatedSQL>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) violatedSQL>SQL> insert into t1 values ('a', 'accept');1 row created.