sql/find_foreign_key_add_cascade.sql

Rama main · réplica de lectura del repositorio original.

-- https://stackoverflow.com/questions/868620/sql-script-to-alter-all-foreign-keys-to-add-on-delete-cascade;
WITH CTE AS (
    SELECT
        KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME,
        KCU1.TABLE_SCHEMA AS FK_SCHEMA_NAME,
        KCU1.TABLE_NAME AS FK_TABLE_NAME,
        KCU1.COLUMN_NAME AS FK_COLUMN_NAME,
        KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION,
        KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME,
        KCU2.TABLE_SCHEMA AS REFERENCED_SCHEMA_NAME,
        KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME,
        KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME,
        KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
        ON
            RC.CONSTRAINT_CATALOG = KCU1.CONSTRAINT_CATALOG
            AND RC.CONSTRAINT_SCHEMA = KCU1.CONSTRAINT_SCHEMA
            AND RC.CONSTRAINT_NAME = KCU1.CONSTRAINT_NAME

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2
        ON
            RC.UNIQUE_CONSTRAINT_CATALOG = KCU2.CONSTRAINT_CATALOG
            AND RC.UNIQUE_CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA
            AND RC.UNIQUE_CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
            AND KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
)

SELECT
    FK_SCHEMA_NAME,
    FK_TABLE_NAME,
    FK_CONSTRAINT_NAME
    --,FK_COLUMN_NAME
    --,REFERENCED_COLUMN_NAME

    ,
    'ALTER TABLE '
    || QUOTE_IDENT(FK_SCHEMA_NAME)
    || '.'
    || QUOTE_IDENT(FK_TABLE_NAME)
    || ' '
    || 'DROP CONSTRAINT ' || QUOTE_IDENT(FK_CONSTRAINT_NAME) || '; '
        AS DROPSTMT

    ,
    'ALTER TABLE '
    || QUOTE_IDENT(FK_SCHEMA_NAME)
    || '.'
    || QUOTE_IDENT(FK_TABLE_NAME)
    || ' 
    ADD CONSTRAINT '
    || QUOTE_IDENT(FK_CONSTRAINT_NAME)
    || ' 
    FOREIGN KEY('
    || STRING_AGG(FK_COLUMN_NAME, ', ')
    || ') 
'
    || '    REFERENCES '
    || QUOTE_IDENT(REFERENCED_SCHEMA_NAME)
    || '.'
    || QUOTE_IDENT(REFERENCED_TABLE_NAME)
    || '('
    || STRING_AGG(REFERENCED_COLUMN_NAME, ', ')
    || ') 
    ON DELETE CASCADE 
; ' AS CREATESTMT

FROM CTE

GROUP BY
    FK_SCHEMA_NAME,
    FK_TABLE_NAME,
    FK_CONSTRAINT_NAME,

    REFERENCED_SCHEMA_NAME,
    REFERENCED_TABLE_NAME