Take the 2-minute tour ×

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?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer


