Le 19/03/2017 à 09:54, Sylvain Marechal a écrit :
2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian.kla...@aklaver.com
<mailto:adrian.kla...@aklaver.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
