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 ?
I can't see a good reason.
I can't either.
Isn't "ON DELETE CASCADE" simply an attribute ?
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.
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