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@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
