Re: FK Constraint on index not PK

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Stéphane Schildknecht <stephane(dot)schildknecht(at)postgresqlfr(dot)org>, Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Re: FK Constraint on index not PK
Date: 2007-01-13 16:31:07
Message-ID: 24951.1168705867@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> The problem is that the constraint was defined with a dependence on the
> second index. I guess what you could do is drop the constraint, drop
> the second index, and then recreate the constraint. Try it within a
> transaction block, just in case it doesn't work (but I don't see why it
> wouldn't)

That's certainly the safest way, but if this is a live production
database then the time spent holding exclusive locks while
re-establishing the FK constraint may be a problem. If that's not
tolerable then the solution is manually mucking with the system
catalogs as I suggested in an earlier reply.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-01-13 16:45:09 Re: index type for indexing long texts
Previous Message Tom Lane 2007-01-13 16:22:32 Re: Problems with unique restrictions