How to update table with ON DELETE CASCADE without dropping and recreating

Question

During development of a PostgreSQL database, I made foreign keys in every table. The problem is that I forgot to select ON DELETE CASCADE option.

I need to set that options on all tables. It seems I can only drop and recreate every constraint manually.

Does anybody know any shortcuts?

Answer 1

Update the constraints directly in pg_constraint. First get the oid's you need:

SELECT oid,* FROM pg_constraint WHERE contype = 'f';

And then update these constraints:

UPDATE pg_constraint SET confupdtype = 'c' WHERE oid = 'fk oid';

More information can be found in the manual. Please do this work within a transaction and do some tests within the transaction as well. Just to be sure.

Answer 2

You could use something like liquibase (http://www.liquibase.org/) which gives you essentially versioned DB schema's, and then it would be really simple to add constraints.

Of course this is serious overkill for this particular problem, but it does attempt to solve the issues on why you had this problem in the first place.