Re: Problem with n to n relation

On Mon, 8 Oct 2001, Janning Vygen wrote:

> Am Montag, 8. Oktober 2001 18:09 schrieb Stephan Szabo:
> > On Mon, 8 Oct 2001, Janning Vygen wrote:
> >
> > > but how do i reach my goal. It should not be allowed to have a
> > > person without any address??
> >
> > Hmm, do you always have at least one known address at the time
> > you're inserting the person?
> >
> > I can think of a few somewhat complicated ways. Person getting a
> > column that references person2adress with initially deferred, the
> > problem here is that you don't know one of the tables' serial
> > values unless you're selecting it yourself which would mean you'd
> > have to change how you were getting your incrementing numbers
> > (getting currval of some sequence presumably and using that to
> > insert into person2adress).
>
> yeah, thats a way which works. dont know if its cool to do it like
> this, but you cant insert a person without any address. so you are
> forced to use a transaction.
>
> create table person (
> id serial,
> name text
> );
>
> create table address (
> id serial,
> street text NOT NULL
> );
>
> create table person2address (
> id int4,
> address_id int4 NOT NULL REFERENCES address (id),
> person_id int4 NOT NULL REFERENCES person (id)
> );
>
> ALTER TABLE person ADD CONSTRAINT person_has_adress FOREIGN KEY (id)
> REFERENCES person2address(id) DEFERRABLE INITIALLY DEFERRED;

This unfortunately will fail on 7.1 and higher due to the fact that
a target of a foreign key constraint must have a unique constraint
on it. The problem is that if you make id effectively the same
as person's id and unique you can't have two addresses for one person.
I think you might need to do something like (untested and I think I got
some syntax confused, but enough for the idea)

create table person (
id serial,
name text,
foo int4
);

create table address(
id serial,
street text NOT NULL
);

create table person2address (
id int4,
address_id int4 NOT NULL REFERENCES address (id),
person_id int4 NOT NULL REFERENCES person (id)
);

create sequence person2address_seq;

ALTER TABLE person ADD CONSTRAINT person_has_adress FOREIGN KEY (id)
REFERENCES person2address(id) DEFERRABLE INITIALLY DEFERRED;

begin;
select next_val('person2address_seq');
-- I'll refer to this as <val> below
insert into person (name, foo) values ('janning', <val>);
insert into address (street) values ('Sesamestreet');
insert into person2address values (<val>, currval('person_id_seq'),
currval('address_id_seq'));
commit;