Thread: [GENERAL] Foreign key references a unique index instead of a primary key
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:
- How to detect if a foreign key depends on an index I'm about to drop, so I can skip it
- 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
?
This is on PostgreSQL 9.3.
Re: [GENERAL] Foreign key references a unique index instead of aprimary key
From
Adrian Klaver
Date:
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
Re: [GENERAL] Foreign key references a unique index instead of aprimary key
From
Arjen Nienhuis
Date:
On Feb 23, 2017 12:42 PM, "Ivan Voras" <ivoras@gmail.com> 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:
- How to detect if a foreign key depends on an index I'm about to drop, so I can skip it
- 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
You could recreate the primary key USING the unique index. This can be done in a transaction without scanning the table. That way there's only one index left.
ALTER TABLE my_table ADD CONSTRAINT PK_my_table PRIMARY KEY USING INDEX my_index;