pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Janning Vygen <vygen@planwerk6.de>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem with n to n relation
Date: Tue, 9 Oct 2001 09:42:06 +0200
Message-ID: <0110090942060D.30615@janning> (raw)
In-Reply-To: <Pine.BSF.4.21.0110081026500.78161-100000@megazone23.bigpanda.com>
References: <Pine.BSF.4.21.0110081026500.78161-100000@megazone23.bigpanda.com>

Am Montag,  8. Oktober 2001 19:33 schrieb Stephan Szabo:
> 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)

Yes you are right! It doesnt work. i thought i have tested it...

But i dont understand your solution. Why did you insert foo in the 
person table? Dou want to use it as a foreign key? Maybe you just 
mistyped your alter table statement i guess. you create a unique 
person2address id and references foo to it. So 'foo' will always 
refernce the first address inserted. Right? Ok thats a fine solution, 
but there is more work to do like a trigger when deleting this 
address... 

Is this the preferable solution?? I am wondering about tutorials 
never explaining stuff like that. Is it too uncommon to have a person 
with at least one address?

Janning

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

-- 
Planwerk 6 /websolutions
Herzogstraße 86
40215 Düsseldorf

fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de



view thread (9+ messages)  latest in thread

Message-ID: <0110090942060D.30615@janning>
Permalink:  ../0110090942060D.30615@janning/
Also on:    postgresql.org/message-id/0110090942060D.30615@janning

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: vygen@planwerk6.de, sszabo@megazone23.bigpanda.com
  Subject: Re: Problem with n to n relation
  In-Reply-To: <0110090942060D.30615@janning>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox