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.
