Re: Problem with n to n relation

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