Re: Deleting Child records

First you need to gather some information, do a : Select constraint_name, table_name, status from user_constraints (or dba_constraints) where table_name = 'YOUR_TABLE'; This will list all the constraints you have in your original table, where you want to delete records.
 Chances are this table has a primary key. You will obtain its name from the above query. Let's call it PKCN, for primary key contraint name.

Then do: Select table_name, constraint_name, status from user_constraints where r_constraint_name = 'PKCN'. This will list tables referencing the primary key from the original table.
You will use this information later on.

Now do:

alter table ORIGINAL_TABLENAME disable primary key cascade;

You should be able to delete the desired rows from your original table. However, you need to reinstate all the constraints that were disabled. Since there is no cascade for enabling constraints, you will have to enable them individually.

Reinstate dependent constraints on each child table by:

alter table DEPENDENT_TABLE enable constraint CONSTRAINT_NAME;

The tables and constraint names come from the previous queries.

Repeat the above queries to verify their status as enabled.

I hope this helps.

Dave Received on Thu Sep 23 1999 - 23:50:09 CDT