How to delete all dependent rows

Question

I have a table A which has primary key column a and it is forigen key to B table(Table B has primary key b and column a).Table B has also a primary key b which forigen key to C table and so on.

Now if I want to delete a row from Table A then it wont allow me because its dependent table B and table B is dependent on Table C.So I have to delete a row from C first then B and at last A manually

So can anybody please tell me if there is any way to delete directly without going to each table and checking if it has any dependencies or not and then deleting

Answer 1

If you're not allowed to modify the tables, you can take a look at the EXISTS operator.
It will allow you to delete rows from a table only if the query inside the EXISTS return at least 1 result. You can use it to check dependencies.

You can write 3 queries:

DELETE C c
WHERE EXISTS (SELECT 1
              FROM B b
              WHERE c.b = b.b
              AND EXISTS (SELECT 1
                          FROM A a
                          WHERE a.a = b.a
                          AND ... ));
DELETE B b
WHERE EXISTS (SELECT 1
              FROM A a
              WHERE a.a = b.a
              AND ...);

DELETE A a
WHERE ...

The first one will take care of record in C that references records in B that references records in A that you want to delete.
Then you can remove records from B since there is no more dependencies in C.
Finally, you're able to delete records from A using the same logic.