Waiting for 9.4 – ALTER TABLE … ALTER CONSTRAINT for FKs

ALTER TABLE ... ALTER CONSTRAINT for FKs
Allow constraint attributes to be altered,
so the default setting of NOT DEFERRABLE
can be altered to DEFERRABLE and back.
Review by Abhijit Menon-Sen

Basically whole purpose of this patch is described in the commit message, but let's see how that works.

As you perhaps know, foreign keys can be immediate or deferred (deferred means that the constraint is checked at the end of transaction and not immediately).

When you create foreign key, you define whether it will be immediate (default) or deferrable. And if deferrable – should it be “initially" immediate or deferred.

What is the “initially" thing? Well, if you have deferrable foreign key you can set it to immediate/deferred within a transaction using SET CONSTRAINTS… command.

Anyway. Up until now, when you created FKEY, you couldn't change it. So, if you made default one (immediate) and you wanted to change it to deferrable, you'd have to drop it and create new one. Not nice.