Pablo Viquez Blog

I needed to switch all foreign keys from one table to another using PL/SQL in Oracle. This was the result:

Necesitaba un script para pasar los foreign keys de una tabla a otra en Oracle, al final este es el resultado:

/**
 * Modifies all references to entity_old and switch them to entity
 *
 * Process
 *  1. Disable Foreign Keys
 *  2. Drop foreign key
 *  3. Create the foreign key, pointing to the new location
 */

var v_old_table varchar2(100);
var v_new_table varchar2(100);
var v_sql varchar2(500);
var v_sql_delete varchar2(500);

exec :v_old_table := 'OLD_TABLE_NAME_HERE';
exec :v_new_table := 'NEW_TABLE_NAME_HERE';

SET SERVEROUTPUT ON;
BEGIN
    FOR row_entity IN (SELECT ac.CONSTRAINT_NAME,
                              ac.CONSTRAINT_TYPE,
                              ac.TABLE_NAME,
                              ac.R_CONSTRAINT_NAME,
                              ac.STATUS,
                              ac.DELETE_RULE,
                              cc.COLUMN_NAME,
                              sr.COLUMN_NAME AS COLUMN_SOURCE
                         FROM ALL_CONSTRAINTS ac,
                              USER_CONS_COLUMNS cc,
                              USER_CONS_COLUMNS sr
                        WHERE ac.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
                          AND ac.R_CONSTRAINT_NAME = sr.CONSTRAINT_NAME
                          AND ac.CONSTRAINT_TYPE = 'R'
                        AND ac.R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
                                                       FROM ALL_CONSTRAINTS
                                                      WHERE CONSTRAINT_TYPE IN ('P','U')
                                                       AND TABLE_NAME = :v_old_table))
    LOOP
        BEGIN
            -- Disable the foreign key IF is enable
            IF row_entity.STATUS = 'ENABLED' THEN
                :v_sql := 'ALTER TABLE ' || row_entity.TABLE_NAME ||
                  ' MODIFY CONSTRAINT ' || row_entity.CONSTRAINT_NAME ||
                  ' DISABLE';

                -- Print SQL Statement
                DBMS_OUTPUT.PUT_LINE(:v_sql || ';');

                EXECUTE IMMEDIATE :v_sql;
            END IF;

            IF row_entity.DELETE_RULE = 'CASCADE' THEN
                :v_sql_delete := ' ON DELETE CASCADE';
            ELSE
                :v_sql_delete := '';
            END IF;

            -- Drop the table reference
            :v_sql := 'ALTER TABLE '|| row_entity.TABLE_NAME ||
                    ' DROP CONSTRAINT ' || row_entity.CONSTRAINT_NAME;

            DBMS_OUTPUT.PUT_LINE(:v_sql || ';');
            EXECUTE IMMEDIATE :v_sql;

            -- Re-create the table reference
            :v_sql := 'ALTER TABLE '|| row_entity.TABLE_NAME ||
                    ' ADD CONSTRAINT ' || row_entity.CONSTRAINT_NAME ||
                    ' FOREIGN KEY (' || row_entity.COLUMN_NAME || ')' ||
                    ' REFERENCES ' || :v_new_table || ' (' || row_entity.COLUMN_SOURCE || ')' ||
                    :v_sql_delete;

            DBMS_OUTPUT.PUT_LINE(:v_sql || ';');
            EXECUTE IMMEDIATE :v_sql;
        END;
    END LOOP;
END;

/

show errors;

Tags: database, foreign keys, oracle