Foreign key references a unique index instead of a primary key

From: Ivan Voras <ivoras(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Foreign key references a unique index instead of a primary key
Date: 2017-02-23 11:40:37
Message-ID: CAF-QHFUm+yMRr9m0unRD6CfHqK12Pfhao_hdXjuLG-t+icbBnQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

?

This is on PostgreSQL 9.3.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Leknín Řepánek 2017-02-23 12:44:01 Re: Move rows from one database to other
Previous Message Moreno Andreo 2017-02-23 10:57:22 Re: Latest PostgreSQL on Raspbian Jessie