Re: 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(dot)klaver(at)aklaver(dot)com