Dean Gibson (DB Administrator) <postgresql@ultimeth.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
