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:09:12
Message-ID: 3EC2E8B8.8070805@oasis.net.au (view raw or flat)
Thread:
Lists: pgsql-performance
Stephan,

Thanks also - I'm actually building a new database as I write this so 
this topic is perfect timing for me.

I'm using ref. integrity right now mostly for many-to-many type situations.

For example.
I create a table of People,
then a table of Business's,
then I need to relate many people to many business's.

So I create a business_people table *with* index's to the referred to tables
Eg:
CREATE TABLE business_people
(
b_p_id serial PRIMARY KEY,
b_id integer REFERENCES business ON UPDATE CASCADE ON DELETE CASCADE NOT 
NULL,
p_id integer REFERENCES people   ON UPDATE CASCADE ON DELETE CASCADE NOT 
NULL
);
CREATE  INDEX b_p_b_id_idx ON  business_people (b_id);
CREATE  INDEX b_p_p_id_idx ON  business_people (p_id);

The b_id and p_id are primary key's in other table's so they have an 
index too.

So far I think I've done every thing right.
Can I ask if you'd agree or not ?

As a side note when I build my PG database's I do it 100% by hand in text.
That is I write Create table statements, save them to file then 
cut'n'paste them into phpPgAdmin or use PSQL.
So the code I have below is the same code I use build the DB.
I wonder if this is OK or would make other PG user's gasp.
I'm sure most database people out there, not sure about PG people, would 
use some sort of GUI.

Thanks kindly
I appreciate your time guy's.
Regards
Rudi.








Stephan Szabo wrote:

>On Thu, 15 May 2003, Rudi Starcevic wrote:
>
>  
>
>>Can I confirm what this means then ..
>>
>>For large table's each column with ref. inegritry I should create an
>>index on those columns ?
>>    
>>
>
>In general, yes.  There's always an additional cost with having additional
>indexes to modifications to the table, so you need to balance the costs by
>what sorts of queries you're doing.  For example, if you're doing a
>references constraint to a table that is mostly there for say providing a
>nice name for something and those values aren't likely to change (and it's
>okay if a change were expensive) then you wouldn't necessarily want the
>additional index.
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>
>  
>

In response to

Responses

pgsql-performance by date

Next:From: Stephan SzaboDate: 2003-05-15 01:23:27
Subject: Re: constraint with reference to the same table
Previous:From: Victor YegorovDate: 2003-05-15 01:03:41
Subject: Re: constraint with reference to the same table

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