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

sql oracle cascade
user1773602 picture user1773602 · Dec 14, 2012 · Viewed 53.3k times · Source

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?

Answer

GolezTrol picture GolezTrol · Dec 14, 2012

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.

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More