Re: constraint with reference to the same table

On Thu, 15 May 2003, Rudi Starcevic wrote:

> 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 ?

Generally, yes, I'd agree with something like that, 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).  If you weren't likely to be doing your own lookups on b_id
and p_id I'd have to consider the indexes more carefully, since I'd expect
that inserts/updates to business_people are much much more likely than
deletes or key updates to business or people.

> 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.

I generally do something like the above, or make the tables, get them to
what I want and schema dump them.