Skip site navigation (1) Skip section navigation (2)

Re: constraint with reference to the same table

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 (view raw or flat)
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.



In response to

Responses

pgsql-performance by date

Next:From: Victor YegorovDate: 2003-05-15 01:58:11
Subject: Re: constraint with reference to the same table
Previous:From: T. Alex BeamishDate: 2003-05-15 01:45:04
Subject: INNER JOIN vs WHERE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group