From: Rudi Starcevic <rudi@oasis.net.au>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Postgres Performance <pgsql-performance@postgresql.org>
Subject: Re: constraint with reference to the same table
Date: Thu, 15 May 2003 11:48:20 +1000
Message-ID: <3EC2F1E4.8080706@oasis.net.au> (raw)
In-Reply-To: <20030514181136.S52444-100000@megazone23.bigpanda.com>
References: <20030514181136.S52444-100000@megazone23.bigpanda.com>
Stephen,
>> although I might not
>> have given a separate serial and instead made the primary key the two id
>> integers (since I'm not sure having the same reference twice makes sense
>> and I'm not sure that you'll need to reference the relationship itself
>> separately).
Yes I see.
That's a very good point.
If I make the primary key across both the business and person instead of using
a new primary key/serial then that will prevent the same business to person
relationship being entered twice.
If I did it that way would this be OK:
New:
CREATE TABLE business_person
(
b_id integer REFERENCES business ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
pn_id integer REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE NOT NULL
PRIMARY KEY(b_id,pn_id);
);
CREATE INDEX b_pn_b_id_idx ON business_person (b_id);
CREATE INDEX b_pn_pn_id_idx ON business_person (pn_id);
Old:
CREATE TABLE business_person
(
b_pn_id serial PRIMARY KEY,
b_id integer REFERENCES business ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
pn_id integer REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE NOT NULL
);
CREATE INDEX b_pn_b_id_idx ON business_person (b_id);
CREATE INDEX b_pn_pn_id_idx ON business_person (pn_id);
As I'd like to sometime's look up business's, sometime's look up people and sometimes
look up both I think I should keep the Index's.
Cheers
Rudi.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: rudi@oasis.net.au, sszabo@megazone23.bigpanda.com
Subject: Re: constraint with reference to the same table
In-Reply-To: <3EC2F1E4.8080706@oasis.net.au>
References: <20030514181136.S52444-100000@megazone23.bigpanda.com> <3EC2F1E4.8080706@oasis.net.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox