The other day I had a task to clean up a database which was populated with a list of records. Unfortunately, my main table is linked to a host of other tables via foreign key dependencies and, to make things worse, no CASCADE constraints are specified. The process I want to is as follows:

Scan main table

First, we need to find the records in the main table we want removed. This is a simple SELECT statement:

    SELECT * FROM <my_table> WHERE <constraint>;

In my case, the statement is:

    SELECT * FROM PROJECTS pro WHERE pro.call_id in (80, 122);

(the 80, and 122 are call identifiers)

Get dependent relevant records

The next step is to find the records in one of the dependent tables we need to remove. This is done via an INNER JOIN:

    SELECT pri.*
    FROM PROJECT_MEMBERS pri
    INNER JOIN PROJECTS pro
      ON pro.proj_id = pri.proj_id
    WHERE pro.call_id IN (80, 122);

This will give us all the records in PROJECT_MEMBERS belonging to one of the projects in our selected calls.

Note: You can perform a COUNT(pri.*) for a quick feedback.

Delete

Now that we've identified the relevant records in PROJECT_MEMBERS, we can proceed to delete them:

    DELETE (
      SELECT pri.*
      FROM PROJECT_MEMBERS pri
      INNER JOIN PROJECTS pro
        ON pro.proj_id = pri.proj_id
      WHERE pro.call_id IN (80, 122);
    );

Find dependent tables

The above approach is OK if you have a very limited number of tables. However, if the number is large, it's better to batch-ify it. The first step is to find the dependent tables of your main table:

    SELECT table_name, constraint_name, status, owner
    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 = 'PROJECTS'
    )
    ORDER BY table_name, constraint_name

This will give you all the constraints and, more important, the linked tables.

Iterate through tables

Once we've identified the tables, we can try to iterate through them. Unfortunately, you can only do dynamic statements in PL/SQL, so we'll have to create a small program.

First, we loop through the dependent tables:

    FOR c IN (
      select table_name, constraint_name, status, owner
      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 = 'PROJECTS'
      )
      order by table_name, constraint_name)
    LOOP
      -- Add the loop code here
    END LOOP;

Then, for each iteration we:

  • count the rows and
  • If the count is not 0, then remove the relevant rows.

The inner script is something like:

    EXECUTE IMMEDIATE 'SELECT count(*)
      FROM ' || c.table_name || ' pri
      INNER JOIN PROJECTS pro
        ON pro.proj_id = pri.proj_id
      WHERE pro.call_id IN (80, 122);' INTO v_counter;
    IF v_counter > 0 THEN
      dbms_output.put_line ('Dependance ' || c.table_name || ' -
        ' || c.constraint_name || ' - ' || v_counter);
    END IF;

This will perform the dynamic SELECT with a variable table name.

IMPORTANT! This script assumes the primary key and all foreign keys are named the same (proj_id in this case).

Now, we're all ready to remove the rows inside the IF statement :)

The full script is something like this:

    DECLARE
      v_counter NUMBER;
    BEGIN
    FOR c IN (
      SELECT table_name, constraint_name, status, owner
      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 = 'PROJECTS'
      )
      ORDER BY table_name, constraint_name)
    LOOP
        BEGIN
           EXECUTE IMMEDIATE 'SELECT count(*)
            FROM ' || c.table_name || ' pri
            INNER JOIN PROJECTS pro
              ON pri.proj_id = pro.proj_id
            WHERE pro.call_id IN (80, 122)' INTO v_counter;
          IF v_counter > 0 THEN
            dbms_output.put_line ('Dep: ' || c.table_name || ' - ' || c.constraint_name || ' - ' || v_counter);
          END IF;
        EXCEPTION
          WHEN OTHERS THEN
          dbms_output.put_line ('Dep NOT FOUND for:' || c.table_name || ' - ' || c.constraint_name);
        END;
      END LOOP;
    END;

DISCLAIMER This script works for me. However, it's your responsibility to make sure the script is adapted to your situation. I can't be held accountable if you wipe out your DB while using it!

HTH,