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: Mon, 8 Oct 2001 19:17:05 +0200
Message-ID: <0110081917050A.21592@janning> (raw)
In-Reply-To: <Pine.BSF.4.21.0110080902170.77847-100000@megazone23.bigpanda.com>
References: <Pine.BSF.4.21.0110080902170.77847-100000@megazone23.bigpanda.com>

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;

begin;
insert into person (name) values ('janning'); 
insert into address (street) values ('Sesamestreet');  
insert into person2address values(1,1,1); 
commit;


> You could probably also make your own deferred constraint trigger
> (although I'm not sure that it's documented since I don't think it
> was really meant as a user feature) which does the check at the end
> of any transaction in which rows were inserted into person.
>
> > > At 12:39 PM 10/5/2001 +0200, Janning Vygen wrote:
> > > >create table person (
> > > >  id    serial,
> > > >  name  text
> > > >);
> > > >
> > > >create table address (
> > > >  id     serial,
> > > >  street text
> > > >  ...
> > > >);
> > > >
> > > >create table person2adress (
> > > >  id         serial,
> > > >  person_id  integer not null references person(id),
> > > >  address_id integer not null references address(id),
> > > >);
> > > >
-- 
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: <0110081917050A.21592@janning>
Permalink:  ../0110081917050A.21592@janning/
Also on:    postgresql.org/message-id/0110081917050A.21592@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: <0110081917050A.21592@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