how can i make on delete cascade using sql developer in oracle 11g

Question

i am trying to delete a record from one table in my db , but the table has forign keys to another table in mu db , so i want to make on delete cascade using sql developr in oracle 11g , how can i make it?

Answer 1

If you are trying to delete rows in a (parent) table and other (child) tables have foreign key references to it, then it will cause the

ORA-02292: integrity constraint (XXXXXX) violated.

TO delete all the child rows whenever a parent record is removed, you specify the "ON DELETE CASCADE" option.

CREATE TABLE Dept_tab (
    Deptno  NUMBER(3) CONSTRAINT Dept_pkey PRIMARY KEY, 
    Dname   VARCHAR2(15), 
    Loc     VARCHAR2(15), 
                CONSTRAINT Dname_ukey UNIQUE (Dname, Loc), 
                CONSTRAINT Loc_check1 
                    CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')));

CREATE TABLE Emp_tab ( 
    Empno    NUMBER(5) CONSTRAINT Emp_pkey PRIMARY KEY, 
    Ename    VARCHAR2(15) NOT NULL, 
    Job      VARCHAR2(10), 
    Mgr      NUMBER(5) CONSTRAINT Mgr_fkey REFERENCES Emp_tab,
    Hiredate DATE, 
    Sal      NUMBER(7,2), 
    Comm     NUMBER(5,2), 
    Deptno   NUMBER(3) NOT NULL 
             CONSTRAINT dept_fkey REFERENCES Dept_tab ON DELETE CASCADE);

In this case , if a row from the department table is deleted, instead of raising an error which is the default case, it will delete all the employees form the employee table.

This has nothing to do with the tool (SQL Developer)...