Skip to content

Instantly share code, notes, and snippets.

@luciancaetano

luciancaetano/pgsql-all_to_cascade.php

Created July 12, 2018 13:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save luciancaetano/c1f41a95b49ff4cb6ffe2bc82e8dbefe to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save luciancaetano/c1f41a95b49ff4cb6ffe2bc82e8dbefe to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. luciancaetano created this gist Jul 12, 2018.
    37 changes: 37 additions & 0 deletions pgsql-all_to_cascade.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,37 @@
    <?php

    $fks = \DB::select(
    /** @lang sql */
    "SELECT
    tc.CONSTRAINT_NAME,
    tc.TABLE_NAME,
    kcu.COLUMN_NAME,
    rc.update_rule AS on_update,
    rc.delete_rule AS on_delete,
    ccu.TABLE_NAME AS references_table,
    ccu.COLUMN_NAME AS references_field
    FROM
    information_schema.table_constraints tc
    LEFT JOIN information_schema.key_column_usage kcu ON tc.CONSTRAINT_CATALOG = kcu.CONSTRAINT_CATALOG
    AND tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
    AND tc.CONSTRAINT_NAME = kcu.
    CONSTRAINT_NAME LEFT JOIN information_schema.referential_constraints rc ON tc.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG
    AND tc.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
    AND tc.CONSTRAINT_NAME = rc.
    CONSTRAINT_NAME LEFT JOIN information_schema.constraint_column_usage ccu ON rc.unique_constraint_catalog = ccu.CONSTRAINT_CATALOG
    AND rc.unique_constraint_schema = ccu.CONSTRAINT_SCHEMA
    AND rc.unique_constraint_name = ccu.CONSTRAINT_NAME --- any conditions for table etc. filtering
    WHERE
    LOWER ( tc.constraint_type ) IN (
    'foreign key')");

    foreach ($fks as $fk){
    // constraint_name, table_name, column_name, on_update, on_delete, references_table, references_field

    $sql = /** @lang sql */
    "ALTER TABLE \"public\".\"{$fk->table_name}\" DROP CONSTRAINT \"{$fk->constraint_name}\",
    ADD CONSTRAINT \"{$fk->constraint_name}\" FOREIGN KEY ( \"{$fk->column_name}\" )
    REFERENCES \"public\".\"{$fk->references_table}\" ( \"{$fk->references_field}\" ) ON DELETE CASCADE ON UPDATE CASCADE;";

    \DB::select($sql);
    }