Foreign keys

From: "Dean Gibson (DB Administrator)" <postgresql(at)ultimeth(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Foreign keys
Date: 2013-12-18 18:02:04
Message-ID: 52B1E31C.8010000@ultimeth.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have general question about FOREIGN KEYs:

1. Suppose I have table A with primary key X, and another table B with
field Y.
2. When I 'ALTER TABLE "B" ADD FOREIGN KEY( "Y" ) REFERENCES "A" ON
UPDATE CASCADE ON DELETE CASCADE', that clearly spends some time
building a separate index. Since there is already a unique index on
X, presumably (?) the index being built is on Y.
3. However, the PostgreSQL documentation seems to indicate that it's a
good idea to also separately create an index on Y.
4. Why, and why is the FOREIGN KEY index different from the ones on X
and Y in any way but trivial?
5. If I need the separate index on Y, should it be built before or
after the FOREIGN KEY constraint?

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2013-12-18 18:10:03 Re: Foreign keys
Previous Message Marc Mamin 2013-12-18 15:02:52 anyrecord/anyelement escaping question.