Skip to content

Instantly share code, notes, and snippets.

@izmailoff

izmailoff/gen_sql_fk_to_cascade_option.sql

Created February 23, 2021 09:55
Show Gist options
  • Save izmailoff/905332bf4ffb4fb7c27255cfea6ea1a4 to your computer and use it in GitHub Desktop.
Save izmailoff/905332bf4ffb4fb7c27255cfea6ea1a4 to your computer and use it in GitHub Desktop.
Download ZIP
Change all Postgres foreign keys to on update cascade - generates sql which you can execute.
Raw
gen_sql_fk_to_cascade_option.sql
select
cl2.relname as child_table,
att2.attname as "child_column",
cl.relname as "parent_table",
att.attname as "parent_column",
conname,
'alter table ' || cl2.relname || ' drop constraint ' || conname || ';' as drop_stmt,
'alter table ' || cl2.relname || ' add constraint ' || conname
|| ' foreign key (' || att2.attname || ') references ' || cl.relname
|| ' on update cascade deferrable initially deferred;' as create_stmt
from
(select
unnest(con1.conkey) as "parent",
unnest(con1.confkey) as "child",
con1.confrelid,
con1.conrelid,
con1.conname
from
pg_class cl
join pg_namespace ns on cl.relnamespace = ns.oid
join pg_constraint con1 on con1.conrelid = cl.oid
where
cl.relname not like 'django%' and cl.relname not like 'auth%'
and ns.nspname = 'public'
and con1.contype = 'f'
) con
join pg_attribute att on
att.attrelid = con.confrelid and att.attnum = con.child
join pg_class cl on
cl.oid = con.confrelid
join pg_class cl2 on cl2.oid = con.conrelid
join pg_attribute att2 on
att2.attrelid = con.conrelid and att2.attnum = con.parent;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment