This discussion is archived
4 Replies Latest reply: Nov 26, 2012 10:28 AM by jgarry RSS

Does anybody know of a good reason for this ?

Marcus Rangel Journeyer
Currently Being Moderated
Hello everyone,

Every now and then I have to "clean" selected trees of records in test databases. In order to do this, I set every referential constraint in this one schema to "ON DELETE CASCADE" and then I delete the top level records. Oracle does the rest. I had to write some code for this, using some arrays and dbms_metadata, because you have to recreate an FK constraint to make it "ON DELETE CASCADE".

My question is: does anybody know the reason why Oracle won't let us issue the command below ?
alter table DEPT modify constraint FK_DEPT_EMP on delete cascade;
I can't see a good reason. Isn't "ON DELETE CASCADE" simply an attribute ? Is there any structural difference between a cascade constraint and a non-cascade ?

Regards,
Marcus Rangel
  • 1. Re: Does anybody know of a good reason for this ?
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ


    How EXACTLY does Oracle respond when you issue posted SQL?
  • 2. Re: Does anybody know of a good reason for this ?
    rp0428 Guru
    Currently Being Moderated
    >
    I can't see a good reason.
    >
    I can't either.
    >
    Isn't "ON DELETE CASCADE" simply an attribute ?
    >
    Yup!
    >
    Is there any structural difference between a cascade constraint and a non-cascade ?
    >
    Not that I'm aware of.

    There is a fundamental difference though in what would happen if users were performing DML on the table when you suddenly changed that attribute. Child records might get deleted when they should not.

    Whether to cascade the deletes on a table is a pretty major architectural factor that is taken into account when you create the processes that manipulate the table data. Without the CASCADE option in effect you can (you shouldn't, but you can) write code or issue a query that attempts to delete a parent record and Oracle will protect you.

    Before flashback and related technologies came along if you had accidentally deleted a parent and all of its children you would be up the proverbial creek without a paddle.

    Now that those other technologies can provide some additional protection there probably isn't much need for restricting that and an ALTER option to modify it might be appropriate.

    You can always suggest the option to Oracle for consideration.
  • 3. Re: Does anybody know of a good reason for this ?
    Marcus Rangel Journeyer
    Currently Being Moderated
    Thank you rp0428, I was afraid I was missing something obvious here. You said I could suggest this to Oracle. How do I do that ?
  • 4. Re: Does anybody know of a good reason for this ?
    jgarry Guru
    Currently Being Moderated
    Simply enter a service request on MOS. Search for How to request enhancement on MOS. You can see the enhancement request bug status values in http://docs.oracle.com/cd/E25290_01/doc.60/e25224/srhome.htm#BABBHEID

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points