Re: [GENERAL] Foreign key references a unique index instead of aprimary key

On 02/23/2017 03:40 AM, Ivan Voras wrote:
> Hello,
>
> I've inherited a situation where:
>
>   * a table has both a primary key and a unique index on the same field.
>   * at some time, a foreign key was added which references this table
>     (actually, I'm not sure about the sequence of events), which has
>     ended up referencing the unique index instead of the primary key.
>
> Now, when I've tried dropping the unique index, I get an error that the
> foreign key references this index (with a hint I use DROP...CASCADE).
>
> This drop index is a part of an automated plpgsql script which deletes
> duplicate indexes, so I'm interested in two things:
>
>  1. How to detect if a foreign key depends on an index I'm about to
>     drop, so I can skip it

Trap the error and move on?:

https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

So something like:

drop index skill_code_u ;
ERROR:  cannot drop index skill_code_u because constraint skill_code_u on table skill_codes requires it
HINT:  You can drop constraint skill_code_u on table skill_codes instead.


CREATE OR REPLACE FUNCTION public.exception_test()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    state_text varchar;
BEGIN

DROP INDEX skill_code_u;
EXCEPTION
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS   state_text =  RETURNED_SQLSTATE;
    RAISE NOTICE '%', state_text;

END;
$function$

Where OTHERS is a special catchall condition.

select exception_test();
NOTICE:  2BP01
 exception_test
----------------

Looking up 2BP01 here:

https://www.postgresql.org/docs/9.6/static/errcodes-appendix.html

shows that it is:

dependent_objects_still_exist

You could narrow the exception to:

EXCEPTION
    WHEN dependent_objects_still_exist THEN


>  2. Is there a way to get around this situation, maybe modify the
>     pg_constraint table or other tables to reference the index / primary
>     key I want

I don't know if that would be wise, it would seem to skip the step where the FK
verifies that the column it is pointing at actually has unique values. In general
the idea of directly modifying system tables makes me nervous.

>
> ?
>
> This is on PostgreSQL 9.3.
>


--
Adrian Klaver
adrian.klaver@aklaver.com