Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Table

 

Check constraint with decode and nvl2

SQL> SQL> create table employees   2  ( empno      NUMBER(4)    constraint E_PK   3                            primary key   4                            constraint E_EMPNO_CHK   5                            check (empno > 7000)   6  , job        VARCHAR2(8)   7  , comm       NUMBER(6,2)   8  , constraint E_SALES_CHK  check   9                            (decode(job,'SALESREP',0,1)  10                             + nvl2(comm,          1,0) = 1)  11  ) ; Table created. SQL> SQL> drop table employees; Table dropped.