Postgres dynamically update constraint foreign key

postgresql, postgresql-8.4

Solution

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!

Problem

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

Original source