Thread: Problem with n to n relation
Hi, i create n to n relations like this, right? 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 notnull references address(id), ); than i can select all adresses from one person with id =1 with select street from address where id = ( select adress_id from person2adress where person_id = 1 ); ok so far so good. but you can still insert persons without any adress. so its a 0..n relation. But how van i achieve that you can´t insert any person without adress??? thanks in advance janning
just get rid of the serial_id in person2adress -- there is no reason for it. Make the pk of that table a composite --> person_id, address_id <-- that way you have added some additional integrity toyour structure. Only one record can exist for a given person at a given address. However any person can have any numberof address and any address can have any number of people living at it. Morgan At 12:39 PM 10/5/2001 +0200, Janning Vygen wrote: >Hi, > >i create n to n relations like this, right? > >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), >); > >than i can select all adresses from one person with id =1 with >select street >from address >where id = > ( > select adress_id > from person2adress > where person_id = 1 > ); > >ok so far so good. but you can still insert persons without any >adress. so its a 0..n relation. But how van i achieve that you can´t >insert any person without adress??? > >thanks in advance >janning > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster ------------- Morgan Curley Partner, e4media mcurley@e4media.com 917 751 8328 http://www.e4media.com -------------------------
Am Freitag, 5. Oktober 2001 14:30 schrieb Morgan Curley: > just get rid of the serial_id in person2adress -- there is no > reason for it. Make the pk of that table a composite --> person_id, > address_id <-- that way you have added some additional integrity to > your structure. Only one record can exist ffor a given person at a > given address. However any person can have any number of address > and any address can have any number of people living at it. ok fine, i understood it after i figured out what pk means :-) but how do i reach my goal. It should not be allowed to have a person without any address?? janning > 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), > >); > > > >than i can select all adresses from one person with id =1 with > >select street > >from address > >where id = > > ( > > select adress_id > > from person2adress > > where person_id = 1 > > ); > > > >ok so far so good. but you can still insert persons without any > >adress. so its a 0..n relation. But how van i achieve that you > > can´t insert any person without adress???
On Mon, 8 Oct 2001, Janning Vygen wrote: > Am Freitag, 5. Oktober 2001 14:30 schrieb Morgan Curley: > > just get rid of the serial_id in person2adress -- there is no > > reason for it. Make the pk of that table a composite --> person_id, > > address_id <-- that way you have added some additional integrity to > > your structure. Only one record can exist ffor a given person at a > > given address. However any person can have any number of address > > and any address can have any number of people living at it. > > ok fine, i understood it after i figured out what pk means :-) > > 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). 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), > > >); > > > > > >than i can select all adresses from one person with id =1 with > > >select street > > >from address > > >where id = > > > ( > > > select adress_id > > > from person2adress > > > where person_id = 1 > > > ); > > > > > >ok so far so good. but you can still insert persons without any > > >adress. so its a 0..n relation. But how van i achieve that you > > > can�t insert any person without adress???
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
NULLREFERENCES 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
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;
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
> 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... Yeah, miscopied the statement. And you're right, I'd forgotten about delete. I think you'd probably be better off faking the check constraint in a deferred constraint trigger. > 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? Well, the *best* way (that doesn't work in postgres) is probably to have a check constraint with a subselect, something like check exists(select * from person2address where ...) initially deferred. But we don't support subselect in check directly, and its not likely to happen soon (it's a potentially very complicated constraint). There are locking issues, but one could probably use a constraint trigger (a postgres specific thing I think, but...) and have the trigger do a select * from person2address where... and raise an exception if no matches are found. The locking issues are due to the fact that you could run into problems with multiple backends trying to do stuff to the same rows if you're not careful, although I think it might work out with for update.
Am Dienstag, 9. Oktober 2001 17:38 schrieb Stephan Szabo: > > 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... > > Yeah, miscopied the statement. And you're right, I'd forgotten > about delete. I think you'd probably be better off faking the > check constraint in a deferred constraint trigger. Thank you very much for your very comprehensive answers. I guess i know enough to solute my problem. Thanks Janning > > 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? > > Well, the *best* way (that doesn't work in postgres) is probably > to have a check constraint with a subselect, something like > check exists(select * from person2address where ...) initially > deferred. But we don't support subselect in check directly, and ^^^ ups, your are a developer of postgresql?? thanks for this great database. > its not likely to happen soon (it's a potentially very complicated > constraint). > There are locking issues, but one could probably use a constraint > trigger (a postgres specific thing I think, but...) and have the > trigger do a select * from person2address where... and raise an > exception if no matches are found. The locking issues are due to > the fact that you could run into problems with multiple backends > trying to do stuff to the same rows if you're not careful, although > I think it might work out with for update.