Use decode() function in check constraints
SQL> SQL> SQL> create table emp 2 ( empno NUMBER(4) constraint E_PK 3 primary key 4 , ename VARCHAR2(8) 5 , init VARCHAR2(5) 6 , job VARCHAR2(8) 7 , mgr NUMBER(4) 8 , bdate DATE 9 , sal NUMBER(6,2) constraint E_sal_NN not null 10 , comm NUMBER(6,2) 11 , deptno NUMBER(2) default 10 12 , constraint E_SALES_CHK check(decode(job,'Tester',0,1)+ nvl2(comm,1,0) = 1) 13 ) ; Table created. SQL> SQL> create index year_sal_idx 2 on emp (12*sal + coalesce(comm,0)); Index created. SQL> SQL> SQL> drop index year_sal_idx; Index dropped. SQL> drop table emp; Table dropped.