Dean Gibson (DB Administrator) <postgresql@xxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
