Skip to main content

Dynamic cascade delete

I

In this article, I will show you a script that deletes all rows from all tables for a specific tenant. You don't need to be familiar with your schema or manually piece together queries to perform the deletion. You can also easily modify this script to delete any entity, not just a tenant. There are several ways to achieve this, and I will show you two of them:


To find (even compound) references to a specific table, you can use this query:

SET DEFINE ON
SELECT
    r.table_name,
    c.column_name,
    r.constraint_name,
    k.column_name           AS primary_column
FROM user_constraints r
JOIN user_constraints p
    ON p.constraint_name    = r.r_constraint_name
    AND p.constraint_type   IN ('P', 'U')
JOIN user_cons_columns c
    ON c.constraint_name    = r.constraint_name
    AND c.table_name        = r.table_name
    AND c.column_name       NOT IN ('TENANT_ID')    -- we should match on position
JOIN user_constraints s
    ON s.table_name         = r.table_name
    AND s.constraint_type   = 'P'
JOIN user_cons_columns k
    ON k.constraint_name    = s.constraint_name
    AND k.table_name        = s.table_name
    AND k.column_name       NOT IN ('TENANT_ID')    -- we should match on position
WHERE r.constraint_type     = 'R'
    --AND r.table_name        = p.table_name          -- to see references in same table
    AND p.table_name        = '&TABLE_NAME'
ORDER BY
    1, 2;


When your column name is same in all tables, it is easy. You just need to disable related foreign constraints and maybe triggers (depending on your business case). To simplify this even more, I will disable all constraints and all triggers and enable them at the end.

DECLARE
    in_column_name      CONSTANT VARCHAR2(128)  := 'TENANT_ID';     -- tenant, user...
    in_column_value     CONSTANT PLS_INTEGER    := 100;             -- tenant_id, user_id...
    --
    v_affected          PLS_INTEGER := 0;
    v_affected_loop     PLS_INTEGER := 0;
BEGIN
    -- disable all triggers
    FOR c IN (
        SELECT t.trigger_name
        FROM user_triggers t
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER TRIGGER ' || c.trigger_name || ' DISABLE';
    END LOOP;

    -- disable all foreign keys
    FOR c IN (
        SELECT
            r.table_name,
            r.constraint_name
        FROM user_constraints r
        WHERE r.constraint_type = 'R'
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER TABLE ' || c.table_name ||
            ' DISABLE CONSTRAINT ' || c.constraint_name;
    END LOOP;

    -- loop over tables with requested column
    FOR c IN (
        SELECT t.table_name
        FROM user_tab_cols c
        JOIN user_tables t
            ON t.table_name     = c.table_name
        LEFT JOIN user_mviews m
            ON m.mview_name     = c.table_name
        WHERE c.column_name     = in_column_name
            AND m.mview_name    IS NULL
    ) LOOP
        -- delete matching rows
        BEGIN
            EXECUTE IMMEDIATE
                'DELETE FROM ' || c.table_name ||
                ' WHERE ' || in_column_name || ' = ' || in_column_value;
            --
            v_affected := v_affected + SQL%ROWCOUNT;
        EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('ERROR: ' || RPAD(c.table_name, 40) || ' ' || SQLERRM);
        END;
    END LOOP;
    --
    COMMIT;

    -- enable all foreign keys
    FOR c IN (
        SELECT
            r.table_name,
            r.constraint_name
        FROM user_constraints r
        WHERE r.constraint_type = 'R'
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER TABLE ' || c.table_name ||
            ' ENABLE CONSTRAINT ' || c.constraint_name;
    END LOOP;

    -- enable all triggers
    FOR c IN (
        SELECT t.trigger_name
        FROM user_triggers t
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER TRIGGER ' || c.trigger_name || ' ENABLE';
    END LOOP;

    -- show number of deleted rows
    DBMS_OUTPUT.PUT_LINE('DELETED: ' || v_affected);
END;
/


In real life, your column names will be most likely different in each table (id, user_id, manager_id, approved_by...), hence you can't rely on the name, you have to use foreign keys. And you can have multicolumn foreign keys referencing not just the primary keys, but also unique constraints. So, if your foreign key strategy is not sound, good luck (check out this article about data integrity).

Another challenge is with the correct order. If you want to delete user, and he is referenced somewhere, you have to delete that row first. And depending on your app, you should just NULL the value and not delete the whole row (user B has a manager A, when you delete user A, you don't want to delete user B, you want just set his manager reference to NULL). So in the script below, references in the same table are set to NULL, the others are deleted.

You define the root table, column name and value/id you would like to remove:

DECLARE
    in_table_name       CONSTANT VARCHAR2(64)   := 'APP_USER';
    in_column_name      CONSTANT VARCHAR2(64)   := 'ID';
    in_ignore_column    CONSTANT VARCHAR2(64)   := 'TENANT_ID';	  -- for compound keys
    in_id               CONSTANT PLS_INTEGER    := 1000;
    --
    TYPE t_tables       IS TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
    v_processed         t_tables := t_tables();
    --
    PROCEDURE cascade_delete (
        io_processed        IN OUT t_tables,
        --
        in_table_name       VARCHAR2,
        in_column_name      VARCHAR2,
        in_id               PLS_INTEGER,
        in_level            PLS_INTEGER
    )
    AS
        v_affected          PLS_INTEGER := 0;
        v_affected_loop     PLS_INTEGER := 0;
        --
        TYPE t_numbers      IS TABLE OF PLS_INTEGER;
        v_id_array          t_numbers := t_numbers();
        --
        v_pad               VARCHAR2(30) := LTRIM(RPAD('.', in_level * 4, ' '), '.');
    BEGIN
        DBMS_OUTPUT.PUT_LINE(v_pad || 'START: ' || in_table_name);
        --
        io_processed.EXTEND;
        io_processed(io_processed.LAST) := in_table_name;

        -- remove references to the same table
        FOR c IN (
            SELECT
                r.table_name,
                c.column_name,
                r.constraint_name
            FROM user_constraints r
            JOIN user_constraints p
                ON p.constraint_name    = r.r_constraint_name
                AND p.constraint_type   = 'P'
            JOIN user_cons_columns c
                ON c.constraint_name    = r.constraint_name
                AND c.table_name        = r.table_name
            JOIN user_cons_columns k
                ON k.constraint_name    = p.constraint_name
                AND k.table_name        = p.table_name
            WHERE r.constraint_type     = 'R'
                AND r.table_name        = in_table_name
                AND p.table_name        = in_table_name
            ORDER BY
                1, 2
        ) LOOP
            EXECUTE IMMEDIATE
                'UPDATE ' || c.table_name ||
                ' SET ' || c.column_name || ' = NULL' ||
                ' WHERE ' || c.column_name || ' = ' || in_id;
        END LOOP;

        -- find all references and delete them in a loop (too lazy for dependency sort)
        FOR i IN 1 .. 100 LOOP
            v_affected_loop := 0;
            --
            FOR c IN (
                SELECT
                    r.table_name,
                    c.column_name,
                    r.constraint_name,
                    k.column_name           AS primary_column
                FROM user_constraints r
                JOIN user_constraints s
                    ON s.table_name         = in_table_name
                    AND r.constraint_type   = 'R'
                    AND r.r_constraint_name = s.constraint_name
                JOIN user_cons_columns c
                    ON c.constraint_name    = r.constraint_name
                    AND c.table_name        = r.table_name
                    AND c.column_name       NOT IN (in_ignore_column)   -- ignore compound key column
                JOIN user_constraints p
                    ON p.table_name         = r.table_name
                    AND p.constraint_type   IN ('P', 'U')
                JOIN user_cons_columns k
                    ON k.constraint_name    = p.constraint_name
                    AND k.column_name       NOT IN (in_ignore_column)   -- ignore compound key column
                ORDER BY
                    1, 2
            ) LOOP
                IF c.table_name MEMBER OF io_processed THEN
                    DBMS_OUTPUT.PUT_LINE(v_pad || '- ' || c.table_name || ' > SKIPPING');
                    CONTINUE;
                END IF;
                --
                DBMS_OUTPUT.PUT_LINE(v_pad || '- ' || c.table_name || '.' || c.column_name);

                -- fetch key
                EXECUTE IMMEDIATE
                    'SELECT DISTINCT ' || c.column_name ||
                    ' FROM ' || c.table_name ||
                    ' WHERE ' || c.primary_column || ' = ' || in_id
                    BULK COLLECT INTO v_id_array;
                --
                FOR j IN 1 .. v_id_array.COUNT LOOP
                    DBMS_OUTPUT.PUT_LINE(v_pad || 'SUBCONTRACT: ' || c.table_name || '.' || c.primary_column || ' = ' || v_id_array(j));
                    --
                    cascade_delete (
                        io_processed    => io_processed,
                        in_table_name   => c.table_name,
                        in_column_name  => c.primary_column,
                        in_id           => v_id_array(j),
                        in_level        => in_level + 1
                    );
                END LOOP;
                --
                BEGIN
                    EXECUTE IMMEDIATE
                        'DELETE FROM ' || c.table_name ||
                        ' WHERE ' || c.column_name || ' = ' || in_id;
                    --
                    v_affected      := v_affected       + SQL%ROWCOUNT;
                    v_affected_loop := v_affected_loop  + SQL%ROWCOUNT;
                EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE(v_pad || 'ERROR: ' || c.table_name || ' ' || SQLERRM);
                    DBMS_OUTPUT.PUT_LINE(v_pad || 'QUERY: ' ||
                        'DELETE FROM ' || c.table_name ||
                        ' WHERE ' || c.column_name || ' = ' || in_id
                    );
                END;
            END LOOP;
            --
            IF v_affected_loop = 0 THEN
                EXIT;
            END IF;
        END LOOP;
        --
        COMMIT;

        -- delete primary table
        BEGIN
            EXECUTE IMMEDIATE
                'DELETE FROM ' || in_table_name ||
                ' WHERE ' || in_column_name || ' = ' || in_id;
            v_affected := v_affected + SQL%ROWCOUNT;
        EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(v_pad || 'ERROR: ' || in_table_name || ' ' || SQLERRM);
            DBMS_OUTPUT.PUT_LINE(v_pad || 'QUERY: ' ||
                'DELETE FROM ' || in_table_name ||
                ' WHERE ' || in_column_name || ' = ' || in_id
            );
        END;
        --
        COMMIT;
        --
        DBMS_OUTPUT.PUT_LINE(v_pad || 'DELETED: ' || v_affected);
    END;
BEGIN
    -- disable all triggers
    FOR c IN (
        SELECT t.trigger_name
        FROM user_triggers t
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER TRIGGER ' || c.trigger_name || ' DISABLE';
    END LOOP;

    -- delete rows, not able to rollback
    cascade_delete (
        io_processed    => v_processed,
        in_table_name   => in_table_name,
        in_column_name  => in_column_name,
        in_id           => in_id,
        in_level        => 0
    );

    -- enable all triggers
    FOR c IN (
        SELECT t.trigger_name
        FROM user_triggers t
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER TRIGGER ' || c.trigger_name || ' ENABLE';
    END LOOP;
END;
/


If you remove the commit and DDL statement at the end of the script, you should be able to see what gets delete and still be able to ROLLBACK it.


Comments