From: | Rudi Starcevic <rudi(at)oasis(dot)net(dot)au> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
Cc: | Postgres Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: constraint with reference to the same table |
Date: | 2003-05-15 01:48:20 |
Message-ID: | 3EC2F1E4.8080706@oasis.net.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Victor Yegorov | 2003-05-15 01:58:11 | Re: constraint with reference to the same table |
Previous Message | T. Alex Beamish | 2003-05-15 01:45:04 | INNER JOIN vs WHERE |