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
