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@postgresql.org so that your
message can get through to the mailing list cleanly