X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 86169475F26 for ; Wed, 14 May 2003 21:43:18 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 87188-04 for ; Wed, 14 May 2003 21:43:16 -0400 (EDT) Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (Postfix) with ESMTP id C05CB475F16 for ; Wed, 14 May 2003 21:43:15 -0400 (EDT) Received: from oasis.net.au (guardian [210.8.139.5]) by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id LAA17521; Thu, 15 May 2003 11:52:42 +1000 X-Authentication-Warning: water.oasis.net.au: Host guardian [210.8.139.5] claimed to be oasis.net.au Message-ID: <3EC2F1E4.8080706@oasis.net.au> Date: Thu, 15 May 2003 11:48:20 +1000 From: Rudi Starcevic User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Stephan Szabo Cc: Postgres Performance Subject: Re: constraint with reference to the same table References: <20030514181136.S52444-100000@megazone23.bigpanda.com> In-Reply-To: <20030514181136.S52444-100000@megazone23.bigpanda.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/168 X-Sequence-Number: 1947 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.