CODEWITHSUNDEEP
sqloracle-databasecascade
user1773602
user1773602

Reputation:

How to force oracle to do cascade delete without dropping/recreating constraints

I've got a parent table, which has a dozen child tables referencing it. Those child tables in turn have other child tables referencing them. And so on.

I need to delete a row from the main parent table cascading it all the way down.

Dropping/recreating constraints all over the place to have them "ON CASCADE DELETE" is not an option

Going through all of them and deleting child rows manually is a nightmare.

Any other options?

Upvotes: 20

Views: 61294

Answers (2)

GolezTrol
GolezTrol

Reputation: 116170

The DELETE statement has no parameters to make it cascading.

So you can either make user of ON CASCADE DELETE or just execute a bunch of separate deletes in the right order.

You could write a 'smart delete' procedure which investigates table structure and generates a series of deletes bases on that, but that will likely be more work and more of a nightmare than writing the separate deletes. And you'd have to have those constraints for this to work, which in reality is not always desired.

Upvotes: 29

Helder Velez
Helder Velez

Reputation: 207

I solved this kind of problem in oracle with an application I've done on purpose to merge data from one branch to other one - target, and then delete the source one.

https://sites.google.com/site/keytreechanger/Home https://sites.google.com/site/keytreechanger/Home/screenshots

quoting

Given this error, for example: client Pedrus Root (internal key representation #R=111) is the same as Petrus Root (#R=222). Move data under 222 to 111, and then delete 222 branch.
KTC gathers all data relative to this case (and the next few cases to be solved), in a smarty way from all relevant tables of database.
The algorithm only searches where needed and using all available key components.
A backup of all data is locally maintained for audit purposes. The local repository evolves transparently to accommodate changes in table/field definitions.

The power user visually inspects the table/relation tree, and can view/print/edit data in any table and optionally modify the presumed action (cut, or cut and paste or done).
Finally KTC generates hundreds of lines of inserts, updates and deletes of corrective code to solve this particular case, listed in a suitable order ending with
delete from rootTable where #R=222.
Then, I apply the code with the users online.

In your situation a filter will have to remove all lines but the delete ones.

AFAIK there is no equivalent application in the market.

Upvotes: -1

Related Questions