20

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?

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
  • are triggers a valid option for you? – dani herrera Dec 14 '12 at 15:30
  • @danihp, no they are not. I can't modify the schema –  Dec 14 '12 at 15:34
  • How many rows do you have to delete, and will this be a regular task? – wolφi Dec 14 '12 at 16:25
  • @WolfgangFleischmann, I don't even know how many. This is one off task, just need to test an unusual scenario. –  Dec 14 '12 at 17:08
  • 2
    Do you know the `DELETE FROM (SELECT pid, cid, gid FROM myparent JOIN mychild USING (pid) JOIN mygrandchild USING (cid)) WHERE pid=xxx` Syntax? That would arguably be the most painless way to delete the rows. I can elaborate if you want... – wolφi Dec 14 '12 at 17:44

2 Answers2

29

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.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
-1

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.

ceving
  • 21,900
  • 13
  • 104
  • 178
Helder Velez
  • 187
  • 1
  • 4