Waiting for 9.4 – ALTER TABLE … ALTER CONSTRAINT for FKs

On 28th of June, Simon Riggs committed patch:

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.

But now, we can. Let's create some test schema:

$ CREATE TABLE table1 (id serial PRIMARY KEY);
CREATE TABLE
 
$ CREATE TABLE table2 (id serial PRIMARY KEY, t1_id int4 REFERENCES table1 (id));
CREATE TABLE
 
$ \d table2
                         TABLE "public.table2"
 COLUMN |  TYPE   |                      Modifiers                      
--------+---------+-----------------------------------------------------
 id     | INTEGER | NOT NULL DEFAULT NEXTVAL('table2_id_seq'::regclass)
 t1_id  | INTEGER | 
Indexes:
    "table2_pkey" PRIMARY KEY, btree (id)
Foreign-KEY constraints:
    "table2_t1_id_fkey" FOREIGN KEY (t1_id) REFERENCES table1(id)

and now, let's try changing it:

$ ALTER TABLE table2
    ALTER CONSTRAINT table2_t1_id_fkey
        DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE
 
$ \d table2
                         TABLE "public.table2"
 COLUMN |  TYPE   |                      Modifiers                      
--------+---------+-----------------------------------------------------
 id     | INTEGER | NOT NULL DEFAULT NEXTVAL('table2_id_seq'::regclass)
 t1_id  | INTEGER | 
Indexes:
    "table2_pkey" PRIMARY KEY, btree (id)
Foreign-KEY constraints:
    "table2_t1_id_fkey" FOREIGN KEY (t1_id) REFERENCES table1(id) DEFERRABLE

Nice. And what about changing it to initially deferred, or back to immediate?

$ ALTER TABLE table2
    ALTER CONSTRAINT table2_t1_id_fkey
        DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE
 
$ \d table2
                         TABLE "public.table2"
 COLUMN |  TYPE   |                      Modifiers                      
--------+---------+-----------------------------------------------------
 id     | INTEGER | NOT NULL DEFAULT NEXTVAL('table2_id_seq'::regclass)
 t1_id  | INTEGER | 
Indexes:
    "table2_pkey" PRIMARY KEY, btree (id)
Foreign-KEY constraints:
    "table2_t1_id_fkey" FOREIGN KEY (t1_id) REFERENCES table1(id) DEFERRABLE INITIALLY DEFERRED
 
$ ALTER TABLE table2
    ALTER CONSTRAINT table2_t1_id_fkey
        NOT DEFERRABLE;
ALTER TABLE
 
$ \d table2
                         TABLE "public.table2"
 COLUMN |  TYPE   |                      Modifiers                      
--------+---------+-----------------------------------------------------
 id     | INTEGER | NOT NULL DEFAULT NEXTVAL('table2_id_seq'::regclass)
 t1_id  | INTEGER | 
Indexes:
    "table2_pkey" PRIMARY KEY, btree (id)
Foreign-KEY constraints:
    "table2_t1_id_fkey" FOREIGN KEY (t1_id) REFERENCES table1(id)

Unfortunately, as of now, you can't change deferred-ness of primary keys or unique constraints, but it is still a step in good direction.