Re: Foreign keys

Dean Gibson (DB Administrator) <postgresql(at)ultimeth(dot)com> wrote:

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

No it doesn't.  If you are observing activity at that time, it is
probably from validating that the constraint is initially valid.

>    3. However, the PostgreSQL documentation seems to indicate
>       that it's a good idea to also separately create an index on
>       Y.

It *often* is, but there are various reasons you might not want
such an index, which is why its creation is not automatic.

>    5. If I need the separate index on Y, should it be built
>       before or after the FOREIGN KEY constraint?

In some cases it may allow faster initial validation of the
constraint; if I wanted the index I would probably build it before
adding the constraint.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company