Sort of on topic, how many foreign keys in a single table is good v.
bad? I realize it's relative to the tables the FK's reference so here's
an example:

Table A: 300 rows
Table B: 15,000,000 rows
Table C: 100,000 rows
Table E: 38 rows
Table F: 9 rows
Table G: is partitioned on the FK from Table A and has a FK column for
each of the above tables

I'm in the process of normalizing the database and have a schema like
this in mind. Works wonderfully for SELECT's but haven't gotten the
data import process down just yet so I haven't had a chance to put it
through it's paces. Depending on the performance of INSERT, UPDATE, and
COPY I may drop the FK constraints since my app could enforce the FK
checks.

TIA.

Greg


> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailtogsql-performance-owner@postgresql.org] On Behalf Of Chris
> Sent: Thursday, August 10, 2006 6:36 PM
> To: Merlin Moncure
> Cc: Sue Fitt; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] setting up foreign keys
>
> Merlin Moncure wrote:
> > On 8/10/06, Chris <dmagick@gmail.com> wrote:
> >> Sue Fitt wrote:
> >> > Thanks Chris and Chris, you've solved it.
> >> >
> >> > I had a gui open that connects to the database. It was doing
> >> > nothing (and not preventing me adding to or altering
> headwords_core
> >> > via psql), but having closed it the table is instantly
> created. Weird.
> >> >
> >> > BTW, referencing the same column twice is deliberate, it's a
> >> > cross-reference.
> >>
> >> The same column and the same table?
> >>
> >> Same column different table I could understand but not the same
> >> column & table
> >
> > create table color(color text);
> >
> > create table person(eye_color text references color(color),
> hair_color
> > text references color(color));
>
> lol. Good point
>
> *back to the hidey hole!*
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Spiegelberg, Greg wrote:
> Sort of on topic, how many foreign keys in a single table is good v.
> bad? I realize it's relative to the tables the FK's reference so here's
> an example:
>
> Table A: 300 rows
> Table B: 15,000,000 rows
> Table C: 100,000 rows
> Table E: 38 rows
> Table F: 9 rows
> Table G: is partitioned on the FK from Table A and has a FK column for
> each of the above tables
>
> I'm in the process of normalizing the database and have a schema like
> this in mind. Works wonderfully for SELECT's but haven't gotten the
> data import process down just yet so I haven't had a chance to put it
> through it's paces. Depending on the performance of INSERT, UPDATE, and
> COPY I may drop the FK constraints since my app could enforce the FK
> checks.

As long as both sides of the FK's are indexed I don't think you'll have
a problem with a particular number of FK's per table.

--
Postgresql & php tutorials
http://www.designmagick.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org