Oracle: Deleting child-record-referenced parent-rows

Foreign key references/constraints are a neat thing to keep your data’s integrity. But it can be a pain if you need to delete records in a parent-table.

Unless you have identified and deleted all referencing child-records, the DB won’t let you delete the parent-records. To identify the children you need to know the foreign-key-structure put on that parent-table.

Figure out which child-tables are referencing your parent-table:

SELECT a.owner CHILD_OWNER,
  a.constraint_name CHILD_CONSTRAINT,
  c.COLUMN_NAME CHILD_COLUMN,
  a.table_name CHILD_TABLE,
  a.r_owner PARENT_OWNER,
  a.r_constraint_name PARENT_REF_NAME,
  d.column_name PARENT_COLUMN,
  b.table_name AS PARENT_TABLE,
  a.delete_rule
FROM dba_constraints a,
  dba_constraints b,
  dba_cons_columns c,
  dba_cons_columns d
WHERE 
a.constraint_type='R' 
AND a.r_constraint_name=b.constraint_name
AND a.constraint_name  =c.constraint_name
AND b.constraint_name  =d.constraint_name
and a.owner='TEST'
ORDER BY PARENT_TABLE;

Then you can drill down by the references to the child-records and delete them first.

If your circumstances (applications, user) would allow, it would perhaps by easier to “alter” all your foreign-keys with “ON DELETE CASCADE”.
Unfortunately you can not simply modify them to get that enabled. You’ll have to drop and re-create them with “ON DELETE CASCADE”.

alter table t2 drop constraint T2_FK1;
alter tablet2 add constraint T2_FK1 foreign key (dept) references t1 (dept) on delete cascade;

But mind to lock the db for your users prior to drop the constraints or else you could mess up your data-integrity!

At a first glance I wondered, if it wouldn’t be wise to always use the CASCADE-option – until I deleted my first parent-row.

SQL> select * from t1;

      DEPT
----------
       102
       103
       104
       105
       107

5 rows selected.

SQL> select * from t2;

    USERID       DEPT
---------- ----------
        11        105
         2        102
         3        103
         4        104

4 rows selected.

SQL> delete from t1 where dept=102;

1 row deleted.

SQL>

The DB gave no information about the implicitly deleted child-record! This could lead to some problems as Laurent Schneider explained here very nice (read John Flack’s comment there too):

http://laurentschneider.com/wordpress/2007/08/on-delete-cascade.html

About these ads