Oracle SQL - Add constraint of cascade deleting


Add constraint of cascade deleting

create table  emp
( empno      NUMBER(4)    constraint E_PK        primary key
                          constraint E_EMPNO_CHK check (empno > 7000)
, ename      VARCHAR2(8)  constraint E_NAME_NN   not null
, init       VARCHAR2(5)  constraint E_INIT_NN   not null
, job        VARCHAR2(8)
, mgr        NUMBER(4)    constraint E_MGR_FK    references emp
, bdate      DATE         constraint E_BDAT_NN   not null
, msal       NUMBER(6,2)  constraint E_MSAL_NN   not null
, comm       NUMBER(6,2)
, deptno     NUMBER(2)    default 10
,                         constraint E_SALES_CHK check
                                                  + nvl2(comm,          1,0) = 1)
) ;
create table departments
( deptno NUMBER(2)     constraint D_PK          primary key
                       constraint D_DEPTNO_CHK  check (mod(deptno,10) = 0)
, dname  VARCHAR2(10)  constraint D_DNAME_NN    not null
                       constraint D_DNAME_UN    unique
                       constraint D_DNAME_CHK   check (dname = upper(dname))
, location VARCHAR2(8) constraint D_LOC_NN      not null
                       constraint D_LOC_CHK     check (location = upper(location))
, mgr    NUMBER(4)     constraint D_MGR_FK      references emp
) ;

create table history
( empno      NUMBER(4)    constraint H_EMPNO_NN  not null
                          constraint H_EMPNO_FK  references emp
                                                 on delete cascade
, beginyear  NUMBER(4)    constraint H_BYEAR_NN  not null
, begindate  DATE         constraint H_BDATE_NN  not null
, enddate    DATE
, deptno     NUMBER(2)    constraint H_DEPT_NN   not null
                          constraint H_DEPT_FK   references departments
, msal       NUMBER(6,2)  constraint H_MSAL_NN   not null
, comments   VARCHAR2(60)
,                         constraint H_PK        primary key (empno,begindate)
,                         constraint H_BEG_END   check (begindate < enddate)
) ;

Related Topic