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
  • Save luciancaetano/c1f41a95b49ff4cb6ffe2bc82e8dbefe to your computer and use it in GitHub Desktop.
Save luciancaetano/c1f41a95b49ff4cb6ffe2bc82e8dbefe to your computer and use it in GitHub Desktop.
Download ZIP
Raw
pgsql-all_to_cascade.php
<?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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment