Re: Creating a heirarchy of tables in a schema based on FK
Date: Wed, 18 Nov 2009 11:16:10 -0300
Message-ID: <172762180911180616l5d99b559jf7059326b14221ac_at_mail.gmail.com>
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_at_gmail.com> 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_at_gmail.com
> > 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_at_multiservice.com> 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_at_mastek.com> 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.
>>> >
>>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>> >
>>> > --
>>> > http://www.freelists.org/webpage/oracle-l
>>> >
>>> >
>>> >
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 18 2009 - 08:16:10 CST