Re: [GENERAL] Best way to alter a foreign constraint

On 03/18/2017 12:05 PM, Sylvain Marechal wrote:

Hello all,

Some of my tables were badly designed and have 2 indexes, like the
following example (lots of tables have same problem):

<<<
postgres=# \d test1
     Table "public.test1"
 Column |  Type   | Modifiers
--------+---------+-----------
 t1     | integer | not null
Indexes:
    "test1_pkey" PRIMARY KEY, btree (t1)
    "test1_t1_key" UNIQUE CONSTRAINT, btree (t1)
Referenced by:
    TABLE "test2" CONSTRAINT "test2_t1_fkey" FOREIGN KEY (t1) REFERENCES
test1(t1)

postgres=# \d test2
     Table "public.test2"
 Column |  Type   | Modifiers
--------+---------+-----------
 t2     | integer | not null
 t1     | integer |
Indexes:
    "test2_pkey" PRIMARY KEY, btree (t2)
Foreign-key constraints:
    "test2_t1_fkey" FOREIGN KEY (t1) REFERENCES test1(t1)

It is not possible to remove the "test1_t1_key" constraint because the
"test2_t1_fkey"  internally references it:
<<<
postgres=# ALTER TABLE test1 DROP CONSTRAINT test1_t1_key;
ERROR:  cannot drop constraint test1_t1_key on table test1 because other
objects depend on it
DETAIL:  constraint test2_t1_fkey on table test2 depends on index
test1_t1_key
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Why not CASCADE?:

test=# ALTER TABLE test1 DROP CONSTRAINT test1_t1_key CASCADE;
NOTICE:  drop cascades to constraint test2_t1_fkey on table test2
ALTER TABLE

It is the same end result as the first two steps of what you are doing below, just a different direction.

Is there a solution to" alter" the "test2_t1_fkey" constraint so that it
uses the "primary key constraint", then to remove the unnecessary unique
constraint on table test1

The following solution works but causes me deadlocks problems with BDR:

Is the below wrapped in a transaction?

<<<
ALTER TABLE test2 DROP CONSTRAINT test2_t1_fkey;
ALTER TABLE test1 DROP CONSTRAINT test1_t1_key;
ALTER TABLE test2 ADD CONSTRAINT test2_t1_fkey FOREIGN KEY (t1)
REFERENCES test1(t1);

Thanks and regards,
Sylvain

--
Adrian Klaver
adrian.klaver@aklaver.com