Re: Best way to alter a foreign constraint

On Mon, Mar 20, 2017 at 11:07 AM, Sylvain MARECHAL <
marechal(dot)sylvain2(at)gmail(dot)com> wrote:

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

This query might help you determine which key is referenced.
The key information is confkey, which is reported as key position in the
referenced table.
Refer to system catalogs description in documentaion for more info.

SELECT cn.conname,
CASE WHEN cn.contype = 'c' THEN 'check'
WHEN cn.contype = 'f' THEN 'foreign key'
WHEN cn.contype = 'p' THEN 'primary key'
WHEN cn.contype = 'u' THEN 'unique'
WHEN cn.contype = 't' THEN 'trigger'
WHEN cn.contype = 'x' THEN 'exclusion'
END as type,
cn.condeferrable,
CASE WHEN cn.conrelid > 0
THEN (SELECT nspname || '.' || relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.oid = cn.conrelid)
ELSE ''
END as table,
confkey,
consrc
FROM pg_constraint cn
ORDER BY 1;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.