Sometimes we want to delete a record from a table and we know, or suspect, that there are records in child tables that refer to the current record via a foreign key.
The script below will look at all foreign keys that refer to the primary key (called PT_PK in the script) of the desired parent table (called PARENT_TABLE in the script). It then gets the table name and key column name of the child table and builds a SQL statement to delete the records from the child table that match the key value (Called 'VALUE' in the example below). The SQL is then executed using dynamic SQL.
Finally it deletes the parent record and commits.
This could also be adjusted to use a unique key instead of the primary key if that was used for foreign keys instead.
For testing, replace the execute statement with
dbms_output.put_line(v_sql);
to view the SQL that will be executed and comment out the final delete and commit until you are happy with the SQL it will run.
Note that this example is for a single column key only and only goes one level down. You could build a recursive function that repeatedly tracks down all child tables using the same method or extend it to allow for multi-column keys.
Note also that the '''VALUE''' example used below assumes the column is a literal string and hence needs the triple quotes so it can be inserted as such in the SQL statement. If you need a number then then it would be specified as 'VALUE'. E.g. '9'. Or use other data types as required.
DECLARE
CURSOR c_fk IS
SELECT constraint_name,
table_name
FROM all_constraints
WHERE constraint_type = 'R' AND
delete_rule = 'NO ACTION' AND
r_constraint_name IN (
SELECT constraint_name
FROM all_constraints
WHERE constraint_type = 'P' AND -- primary
table_name = 'PARENT_TABLE' AND
owner = 'SCHEMA_OWNER') AND
table_name != 'PARENT_TABLE';
CURSOR c_child_col (cp_child_cons_name IN
all_cons_columns.constraint_name%TYPE,
cp_child_tab_name IN
all_cons_columns.table_name%TYPE) IS
SELECT cc1.column_name AS child_col,
cc1.table_name AS child_table
FROM all_cons_columns cc1,
all_cons_columns cc2
WHERE cc1.constraint_name = cp_child_cons_name AND
cc1.table_name = cp_child_tab_name AND
cc2.constraint_name = 'PT_PK' AND --parent primary key
cc1.position = cc2.position
ORDER BY cc1.position;
v_sql VARCHAR2(1000);
BEGIN
FOR v_fk_rec IN c_fk LOOP
FOR v_child_col_rec IN c_child_col(
v_fk_rec.constraint_name,
v_fk_rec.table_name) LOOP
v_sql := 'DELETE FROM '||v_child_col_rec.child_table||
' WHERE '||
v_child_col_rec.child_col ||
' = ' ||
'''VALUE'''; -- the primary key value
EXECUTE IMMEDIATE v_sql;
END LOOP;
END LOOP;
DELETE FROM PARENT_TABLE WHERE PARENT_KEY = 'VALUE';
COMMIT;
END;
