Re: constraint with reference to the same table

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.