Add multiple CHECK constraints on one column depending on the values of another column

Question

Given a table with two columns col1 and col2, how can I use the Oracle CHECK constraint to ensure that what is allowed in col2 depends on the corresponding col1 value.

Specifically,

• if col1 has A, then corresponding col2 value must be less than 50;
• if col1 has B, then corresponding col2 value must be less than 100;
• and if col1 has C, then corresponding col2 value must be less than 150.

Thanks for helping!

Solution 1

You need to use a case statement, eg. something like:

create table test1 (col1 varchar2(2),
                    col2 number);

alter table test1 add constraint test1_chk check (col2 < case when col1 = 'A' then 50
                                                              when col1 = 'B' then 100
                                                              when col1 = 'C' then 150
                                                              else col2 + 1
                                                         end);

insert into test1 values ('A', 49);
insert into test1 values ('A', 50);
insert into test1 values ('B', 99);
insert into test1 values ('B', 100);
insert into test1 values ('C', 149);
insert into test1 values ('C', 150);
insert into test1 values ('D', 5000);

commit;

Output:

1 row created.

insert into test1 values ('A', 50)
Error at line 2
ORA-02290: check constraint (MY_USER.TEST1_CHK) violated

1 row created.

insert into test1 values ('B', 100)
Error at line 4
ORA-02290: check constraint (MY_USER.TEST1_CHK) violated

1 row created.

insert into test1 values ('C', 150)
Error at line 6
ORA-02290: check constraint (MY_USER.TEST1_CHK) violated

1 row created.

Commit complete.

Solution 2

add check constraint using case statement

CREATE TABLE tbl
(
  col1 varchar(10),
  col2 numeric(4),
  CONSTRAINT check_cols_ctsr
  CHECK (CASE WHEN col1='A' THEN col2 ELSE 1 END <50 AND 
         CASE WHEN col1='B' THEN col2 ELSE 1 END <100 AND 
         CASE WHEN col1='C' THEN col2 ELSE 1 END <150)
);