Re: Creating a heirarchy of tables in a schema based on FK

From: <>
Date: Wed, 18 Nov 2009 08:38:34 -0500
Message-ID: <>

Alan, Oracle does do on delete cascade, you put it in when adding the FK, did you mean on update cascade? Or am I missing something here,

This is right out of the 9i docs.


   (employee_id     NUMBER(4) PRIMARY KEY, 
    last_name       VARCHAR2(10), 
    job_id          VARCHAR2(9), 
    manager_id      NUMBER(4) CONSTRAINT fk_mgr
                    REFERENCES employees ON DELETE SET NULL, 
    hire_date       DATE, 
    salary          NUMBER(7,2), 

    commission_pct NUMBER(7,2),
    department_id NUMBER(2) CONSTRAINT fk_deptno
                    REFERENCES departments(department_id) 
                    ON DELETE CASCADE ); 


Joe Testa, Oracle Certified Professional Senior Engineering & Administration Lead (Work) 614-677-1668
(Cell) 614-312-6715

Interested in helping out your marriage? Ask me about "Weekend to Remember"
Dec 11-13, 2009 here in Columbus.

Guillermo Alan Bort <> To:
Cc:, "" <>
11/18/2009 08:05 AM
Re: Creating a heirarchy of tables in a schema based on FK Sent by:

Hierarchycal (?) models are difficult to use, specially since RDBMS allow for multiple table relations (that's what the relational is all about ;-) )

AFAIK, Oracle does not support the ON DELETE CASCADE per se, at least it doesn't in 10g (and earlier). You can emulate that using PL/SQL and/or triggers.

You can use a brute force approach, running many the delete on all the tables sorted in an arbitrary order... eventually (after four or five runs) you'll have all the tables clear. NOT the best approach, just the fastest to write :-P

Alternativeley, you can use recursion, but might not work in all relational situations.

Alan Bort
Oracle Certified Professional

On Wed, Nov 18, 2009 at 5:12 AM, Steve Baldwin <
> wrote:

Could you just change the FK constraints to 'ON DELETE CASCADE' then just delete from the top level table(s)? This is by far the least amount of work.

Alternatively, you could write a recursive function that fetches from user_constraints and user_cons_columns and uses either dbms_sql or execute immediate (depending on your Oracle version) to execute the delete statement.

Hope this helps.

On Wed, Nov 18, 2009 at 6:30 PM, Steven Rebello <> wrote:
> Hi List members
> Would like your help/inputs in a problem I am facing here.
> I want to clear out records from a huge bunch of selected tables (1000+)
which have interdependencies established using FK constraints. Of course, just creating a "delete * from table" doesn't work because of the FK constraints.
> What I want to do is to identify the "child" tables which can be deleted
first and then create the hierarchy of tables so that the parent tables can then be cleared out, and so on.
> Can anyone point me to any reference or script which can generate this
hierarchy based on the foreign key constraints? Based on that I can then make the delete script pretty quickly.
> I thought of disabling the constraints and re-enabling them after the
"delete * from table" script, but would like to try the cleaner approach of starting from the bottom of the dependency tree for these tables.
> Thanks
> Steven Rebello
> Mastek is in NASSCOM's 'India Top 20' Software Service Exporters List.
> In the US, we're called MAJESCOMASTEK

> Opinions expressed in this e-mail are those of the individual and not
that of Mastek Limited, unless specifically indicated to that effect. Mastek Limited does not accept any responsibility or liability for it. This e-mail and attachments (if any) transmitted with it are confidential and/or privileged and solely for the use of the intended person or entity to which it is addressed. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. This e-mail and its attachments have been scanned for the presence of computer viruses. It is the responsibility of the recipient to run the virus check on e-mails and attachments before opening them. If you have received this e-mail in error, kindly delete this e-mail from desktop and server.

> --

Received on Wed Nov 18 2009 - 07:38:34 CST

Original text of this message