How to generate DELETE statements in PL/SQL, based on the tables FK relations?

Question

Is it possible via script/tool to generate authomatically many delete statements based on the tables fk relations, using Oracle PL/SQL?

In example: I have the table: CHICKEN (CHICKEN_CODE NUMBER) and there are 30 tables with fk references to its CHICKEN_CODE that I need to delete; there are also other 150 tables foreign-key-linked to that 30 tables that I need to delete first.

Is there some tool/script PL/SQL that I can run in order to generate all the necessary delete statements based on the FK relations for me?

(by the way, I know about cascade delete on the relations, but please pay attention: I CAN'T USE IT IN MY PRODUCTION DATABASE, because it's dangerous!)

I'm using Oracle DataBase 10G R2.

Please pay attention to this:

Generate Delete Statement From Foreign Key Relationships in SQL 2008?

Another user has just written it in SQL SERVER 2008, anyone is able to convert to Oracle 10G PL/SQL? I am not able to... :-(

Please assume that V_CHICKEN and V_NATION are the criteria to select the CHICKEN to delete from the root table: the condition is: "where COD_CHICKEN = V_CHICKEN AND COD_NATION = V_NATION" on the root table.

Answer 1

(My first answer became too long and difficult to edit, and it got Community Wikified, which is really annoying. Here is the latest version of the script.)

This script attempts to perform a cascading delete through recursion. It should avoid infinite loops when there are circular references. But it requires that all circular referential constraints have ON DELETE SET NULL or ON DELETE CASCADE.

CREATE OR REPLACE PROCEDURE delete_cascade(
    table_owner          VARCHAR2,
    parent_table         VARCHAR2,
    where_clause         VARCHAR2
) IS
    /*   Example call:  execute delete_cascade('MY_SCHEMA', 'MY_MASTER', 'where ID=1'); */

    child_cons     VARCHAR2(30);
    parent_cons    VARCHAR2(30);
    child_table    VARCHAR2(30);
    child_cols     VARCHAR(500);
    parent_cols    VARCHAR(500);
    delete_command VARCHAR(10000);
    new_where_clause VARCHAR2(10000);

    /* gets the foreign key constraints on other tables which depend on columns in parent_table */
    CURSOR cons_cursor IS
        SELECT owner, constraint_name, r_constraint_name, table_name, delete_rule
          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 IN ('P', 'U')
                                        AND table_name = parent_table
                                        AND owner = table_owner)
           AND NOT table_name = parent_table; -- ignore self-referencing constraints


    /* for the current constraint, gets the child columns and corresponding parent columns */
    CURSOR columns_cursor IS
        SELECT cc1.column_name AS child_col, cc2.column_name AS parent_col
          FROM all_cons_columns cc1, all_cons_columns cc2
         WHERE cc1.constraint_name = child_cons
           AND cc1.table_name = child_table
           AND cc2.constraint_name = parent_cons
           AND cc1.position = cc2.position
        ORDER BY cc1.position;
BEGIN
    /* loops through all the constraints which refer back to parent_table */
    FOR cons IN cons_cursor LOOP
        child_cons   := cons.constraint_name;
        parent_cons  := cons.r_constraint_name;
        child_table  := cons.table_name;
        child_cols   := '';
        parent_cols  := '';

        /* loops through the child/parent column pairs, building the column lists of the DELETE statement */
        FOR cols IN columns_cursor LOOP
            IF child_cols IS NULL THEN
                child_cols  := cols.child_col;
            ELSE
                child_cols  := child_cols || ', ' || cols.child_col;
            END IF;

            IF parent_cols IS NULL THEN
                parent_cols  := cols.parent_col;
            ELSE
                parent_cols  := parent_cols || ', ' || cols.parent_col;
            END IF;
        END LOOP;

        /* construct the WHERE clause of the delete statement, including a subquery to get the related parent rows */
        new_where_clause  :=
            'where (' || child_cols || ') in (select ' || parent_cols || ' from ' || table_owner || '.' || parent_table ||
            ' ' || where_clause || ')';

        delete_cascade(cons.owner, child_table, new_where_clause);
    END LOOP;

    /* construct the delete statement for the current table */
    delete_command  := 'delete from ' || table_owner || '.' || parent_table || ' ' || where_clause;

    -- this just prints the delete command
    DBMS_OUTPUT.put_line(delete_command || ';');

    -- uncomment if you want to actually execute it:
    --EXECUTE IMMEDIATE delete_command;

    -- remember to issue a COMMIT (not included here, for safety)
END;

Answer 2

The problem is if the top level key column isn't propagated all the way down to the bottom. If you can do DELETE FROM grandchild WHERE parent_id = :1, it is fine. If you have to do,

DELETE FROM grandchild
WHERE child_id in (SELECT id FROM child WHERE parent_id = :1)

then going down six or seven deep will give you ugly (and probably slow) queries.

While you said you can't make the constraints CASCADE, can you make them deferrable initally immediate ? That way existing code should not be impacted. Your 'delete' session would make all constraints deferred. Then delete from the parent, delete from the child where the record wasn't in the parent, delete from the grandchild where there's no match in the child etc...

Answer 3

This is a great exercise in developing your PL/SQL skills and general Oracle knowledge!

You need to identify all constrained columns in all tables with relations descending from your master table. You can get all the information you need from two views: ALL_CONSTRAINTS and ALL_CONS_COLUMNS. (If all the tables are in the same schema as the user executing the script, you can use USER_CONSTRAINTS and USER_CONS_COLUMNS if you prefer)

This query will find all the foreign key constraints which refer back to a given table (CUSTOMER in this example):

SELECT constraint_name, table_name, constraint_type
  FROM all_constraints
 WHERE constraint_type = 'R'
   AND r_constraint_name IN (SELECT constraint_name
                               FROM all_constraints
                              WHERE constraint_type IN ('P', 'U')
                                AND table_name = 'CUSTOMER');


CONSTRAINT_NAME                C
------------------------------ -
CUSTOMER_FK1                   R
CUSTOMER_FK4                   R
CUSTOMER_FK5                   R
CUSTOMER_FK3                   R
CUSTOMER_FK2                   R

Now, for each of the results from that query, you can use the CONSTRAINT_NAME column to get a table and column name which you can use to write DELETE statements to delete all child rows in all child tables.

This example gets the table and column name for a constraint called CUSTOMER_FK1

SELECT table_name, column_name
  FROM user_cons_columns
 WHERE constraint_name = 'CUSTOMER_FK1'

TABLE_NAME                    COLUMN_NAME                       
----------------------------- ------------------------------------
RESERVATION                   CUSTOMER_UID

So you could do, for example:

DELETE FROM reservation
 WHERE customer_uid = 00153464

or

DELETE FROM reservation
 WHERE customer_uid IN (SELECT customer_uid
                          FROM customer
                         WHERE customer_type = 'X')

But your child tables also have child tables, so of course you will have to delete those child rows (call them grandchild rows) first. Supposing there is a table called reservation_detail which has a foreign key relationship with reservation, your delete command for reservation_detail might look like:

DELETE FROM reservation_detail 
 WHERE reservation_uid in (SELECT reservation_uid     
                             FROM reservation 
                            WHERE customer_uid IN (SELECT customer_uid
                                                     FROM customer
                                                    WHERE customer_type = 'X')

And if reservation_detail also has children... you get the idea. Of course you could use joins instead of nested queries, but the principle is the same: the more levels deep your dependencies go, the more complex your delete commands become.

So now you know how to do it, the challenge is to write a generic PL/SQL script to delete all child rows, grandchild rows, great-grandchild rows ... (ad infinitum) for any given table, from the bottom up. You will have to employ recursion. Should be a fun program to write!

(Last edit: removed the script; see my other answer for the final solution.)