Oracle: Deleting child-record-referenced parent-rows
March 1, 2012 Leave a comment
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):