Re: Best way to alter a foreign constraint

Le 19/03/2017 à 09:54, Sylvain Marechal a écrit :
> 2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>:
>
> 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?:
>
In fact, CASCADE is not enough, because I don't know how the
test2_t1_fkey is built : does it use the test1_pkey primary key or the
test1_t1_key unique key?
I am sure this information can be found in system catalogs, but I find
it safer to explicitely delete then recreate the foreign constraint.

Sylvain