Best way to alter a foreign constraint

From: Sylvain Marechal <marechal(dot)sylvain2(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Best way to alter a foreign constraint
Date: 2017-03-18 19:05:05
Message-ID: CAJu=pHQ=kxdWgTv_C+334f78mUGFQuS+N7TvdFq6vp8me6NpAg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.
>>>

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:
<<<
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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2017-03-18 19:40:35 Re: Best way to alter a foreign constraint
Previous Message Devrim Gündüz 2017-03-18 10:59:17 Re: CenOS 5/Postgresql 9.6