SQL & PL/SQL

Delete the records from child tables and then delete from parent.

You write a before delete trigger on the parent table to achieve this.

In that case create your FK with ON DELETE CASCADE option.

Please alter foreign key constraint as on delete cascade option .

Santosh,

Problem is there are 50+ child tables for the parent and I have real difficulty in deleting the child records first and then the parent each time.

Define the relationsship (foreign key) with ON DELETE CASCADE Option.

see the last example in below site:

http://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php

It is very risky to use ON DELETE CASCADE in our business case. Because, I don't want this to happen all times.

Well, we are working on a large data migration project where in we migrate approx. 50,000 orders in one batch for 2 weeks. Each day we have scheduled 4 batches. Problem is that there are chances that in these 2 weeks, my customer may update some orders entries.

So after 2 weeks, I will once again load the same file.

If there's any change to the order, I will delete the order_id from the parent as well as from the child tables, else ignore the order in the migration target.

Though not advisable, one solution can be like below:

Prepare one procedure which takes primary key values of master table as input and delete the row for that input.. Inside this procedure you do below:
step 1: enable the on delete cascade option of master table.
step 2: delete the row -- this will also delete the rows from your 50+child tables.
step 3: disable the on delete cascade option of master table.

The advisable solution is:
Prepare the on delete trigger or Prepare one procedure which takes primary key values of master table as input and delete the row for that input..
Inside this procedure, write delete statements to delete rows from 50+child table. Though procedure preparation may be lengthy ( as you need to prepare delete statements for all child tables), later on, its reusable ...

You can think to keep back up of child tables data before deleting them.. In case of uncertainity, this can save your time and effort..

Regards,
Dipali..

use the cascade option with constraint,
or you can recreate the constraint with the option deferrable.
deferrable allow you to modify/delete the relation keys and oracle check the integrity of constraints after a commit.
It is usefull if you need to change the value in child table

What problem do you have with that? Extra logical dependencies or is it just a lot of work to find out all the tables?

You can create part of the child deletes with some clever select statement. Just run this once and put all the resulting code in your delete api (packaged procedure).

As a starter:
this select will show all the child tables and the columns.

select fkcol.owner, fkcol.table_name, fkcol.column_name 
        ,'delete from '||fkcol.owner||'.'||fkcol.table_name||' where '||fkcol.column_name||'=p_id;' deletetext
from user_constraints pk
join user_constraints fk on fk.r_constraint_name = pk.constraint_name and fk.r_owner = pk.owner
join user_cons_columns fkcol on fkcol.constraint_name = fk.constraint_name and fkcol.owner = fk.owner
where pk.constraint_type = 'P'
and pk.table_name = '<your table>';

The delete text column is an example depending on a parameter p_id. You could copy this output into your pl-sql module.