Postgres dynamically update constraint foreign key

Question

I have lots of tables with lots of foreign keys and about all of them are UPDATE NO ACTION and DELETE NO ACTION.

Is it possible to dynamically update all this foreign keys to CASCADE instead of NO ACTION or RESTRICT?

For example:

ALTER TABLE * ALTER FOREIGN KEY * SET ON UPDATE CASCADE ON DELETE CASCADE;

Yours, Diogo

user330315 · Accepted Answer · 2012-05-30 12:37:32Z

6

No, this is not possible.

You will need to drop and re-create all constraints as a foreign key constraint cannot be altered like that.

The following statement will generate the necessary alter table statements to drop and re-create the foreign keys:

select 'alter table '||pgn.nspname||'.'||tbl.relname||' drop constraint '||cons.conname||';'
from pg_constraint cons
  join pg_class tbl on cons.confrelid = tbl.oid
  join pg_namespace pgn on pgn.oid = tbl.relnamespace
where contype = 'f'

union all 

select 'alter table '||pgn.nspname||'.'||tbl.relname||' add constraint '||cons.conname||' '||pg_get_constraintdef(cons.oid, true)||' ON UPDATE CASCADE ON DELETE CASCADE;'
from pg_constraint cons
  join pg_class tbl on cons.confrelid = tbl.oid
  join pg_namespace pgn on pgn.oid = tbl.relnamespace
where contype = 'f'

Save the output of this statement to a file and run it.

Make sure you validate the generated statements before running them!

answered May 30, 2012 at 12:37

user330315

Sign up to request clarification or add additional context in comments.

6 Comments

Diogo Garcia Over a year ago

Thanks! You gave me some light! Your sql seems to identify very well all foreign keys but it seems to misunderstand the fk owner...

Diogo Garcia Over a year ago

For example: I have a fk_user_perfil on the table acess.user that updates acess.perfil_user, your sql seems to think that the fk_user_perfil owner is acess.perfil_user instead of acess.user, I'm trying to solve that problem

user330315 Over a year ago

@DiogoGarcia: yes it assumes that the table owner and the FK owner are the same. You will need to add a second join from pg_constraints to pg_namespace to get the constraint schema (in addition to the table schema)

Diogo Garcia Over a year ago

Found a solution, instead of cons.confrelid = tbl.oid using cons.conrelid = tbl.oid so it will be related to that found fk not the 'pointed' fk, it is working fine here. Is it the best solution?

bart Over a year ago

Yes. confrelid is the oid of the table that the foreign key references to, and conrelid is the oid of the table that the constraint applies to.

|

Kelvin Chingarire · Accepted Answer · 2017-08-29 15:56:28Z

I would use the following code to generate the necessary alter table SQL statements to drop and re-create the foreign keys (in order):

    select 'ALTER TABLE '||pgn.nspname||'.'||tbl.relname||' DROP CONSTRAINT '||cons.conname||';' as sqlstr
    from pg_constraint cons
      join pg_class tbl on cons.conrelid = tbl.oid
      join pg_namespace pgn on pgn.oid = tbl.relnamespace
    where contype = 'f' 
    union
    select 'ALTER TABLE '||pgn.nspname||'.'||tbl.relname||' ADD CONSTRAINT '||cons.conname||' FOREIGN KEY ('||(select attname from pg_attribute where attrelid=cons.conrelid and attnum = ANY(cons.conkey))||') REFERENCES '||tblf.relname||' ('||(select attname from pg_attribute where attrelid=cons.confrelid and attnum = ANY(cons.confkey))||')  ON UPDATE CASCADE ON DELETE CASCADE;'  as sqlstr
    from pg_constraint cons
      join pg_class tbl on cons.conrelid = tbl.oid
      join pg_namespace pgn on pgn.oid = tbl.relnamespace
      join pg_class tblf on cons.confrelid = tblf.oid
    where contype = 'f' 

    ORDER BY sqlstr desc

Collectives™ on Stack Overflow

Postgres dynamically update constraint foreign key

2 Answers 2

Make sure you validate the generated statements before running them!

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Make sure you validate the generated statements before running them!

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related