ON DELETE CASCADE

In last post we created tables and added constraints. In this post we shall insert data and delete data.

ALTER TABLE EMP
ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY(DEPTID)
REFERENCES DEPT(DEPTNO) ON DELETE CASCADE;


CREATE TABLE DEPT
(
    DEPTNO NUMBER(4) CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY,
    DNAME VARCHAR2(30) CONSTRAINT DEPT_DNAME_NN NOT NULL
);

DROP TABLE EMP;

CREATE TABLE EMP
(
    EMPNO NUMBER(4),
    ENAME VARCHAR2(50),
    DOB DATE,
    GENDER VARCHAR2(10) DEFAULT 'MALE',
    DEPTID NUMBER(4)
);

ALTER TABLE EMP
ADD CONSTRAINT EMP_EMPNO_PK PRIMARY KEY(EMPNO);

ALTER TABLE EMP
ADD CONSTRAINT EMP_GENDER_CHK CHECK (GENDER IN ('MALE', 'FEMALE','TRANSVESTILE'));

ALTER TABLE EMP
ADD CONSTRAINT EMP_EMAIL_UK UNIQUE(DEPTID);

ALTER TABLE EMP
ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY(DEPTID)
REFERENCES DEPT(DEPTNO);