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.
Related examples in the same category