RE: Creating a heirarchy of tables in a schema based on FK
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <cicciuxdba@xxxxxxxxx>, "Igor Neyman" <igor.neyman@xxxxxxxxx>
- Date: Wed, 18 Nov 2009 12:39:45 -0600
It's been around since Oracle 7 when constraints were really supported
in Oracle.
You could define constraints in Oracle 6, but they were not enforced.
Starting in 7 they were enforced and ON DELETE CASCADE was there from
the beginning.
-----------------------
Ric Van Dyke
Hotsos Enterprises
-----------------------
Hotsos Symposium
March 7 - 11, 2010
Be there.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Guillermo Alan Bort
Sent: Wednesday, November 18, 2009 9:16 AM
To: Igor Neyman
Cc: stbaldwin@xxxxxxxxxxxxxxxx; Steven.Rebello@xxxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
Subject: Re: Creating a heirarchy of tables in a schema based on FK
It would appear I missed this for three oracle versions. It's a good
thing I am not a developer then :-P
I have never used the on delete clause on a fk, not even on DBs I knew
supported it. I usually prefer to manage that from the application.
Well, that was embarrassing enough for one day...
In that case, I would guess it would still require going through each
table and modifying the constraint to add the ON DELETE CASCADE, which
would be the same effort as writing a script to delete the tables in
order :-P
Thanks for getting me out of an erred view on Oracle.
Regards.
Alan Bort
Oracle Certified Professional
On Wed, Nov 18, 2009 at 10:46 AM, Igor Neyman <igor.neyman@xxxxxxxxx>
wrote:
I think ON DELETE CASCADE for FKs was supported at least since 8i.
And, at least when there is no "special" circumstances, it's better to
use this feature, than creating some homegrown solution (i.e. proposed
in original message - going from bottom up).
Igor N.
On Wed, Nov 18, 2009 at 8:03 AM, Guillermo Alan Bort
<cicciuxdba@xxxxxxxxx> wrote:
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.
hth
Alan Bort
Oracle Certified Professional
On Wed, Nov 18, 2009 at 5:12 AM, Steve Baldwin
<stbaldwin@xxxxxxxxxxxxxxxx> 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
<Steven.Rebello@xxxxxxxxxx> 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 LTD.
> 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.
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
