> 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. ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
