Oracle: how to formulate a complex constraint with case when

Question

The following conditional constraint simply doesn't work. Is it possible to formulate in a working way?

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

score 6 · Answer 1 · answered May 23 '12 at 08:26

6

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

answered May 23 '12 at 08:26

int2000

565
3
14

2

You're right. But my intention was to describe one full "Case when"-Statement, because that one in the question was totally false. – int2000 May 23 '12 at 11:01

score 3 · Answer 2 · answered May 23 '12 at 08:28

3

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

answered May 23 '12 at 08:28

Judge Mental

5,209
17
22

score 2 · Answer 3 · answered May 23 '12 at 08:36

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

Oracle: how to formulate a complex constraint with case when

3 Answers3