Re: Problem with n to n relation - Mailing list pgsql-sql

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
NULLREFERENCES 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> belowinsert 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;