It hit me today that a 1:n relationship can't be implemented just by a single foreign key constraint if n>0. I must have been sleeping very deeply not to notice this.
E.g. if there is a table "list" and another table "list_item" and the relationship can be described as "every list has at least one list_item" (and every list_item can only be part of one list, but this is trivial). A "correct" solution would require (at least?): 1. A foreign key pointing from each list_item to its list 2. Another foreign key pointing from each list to one of its list_item. But this must be a list_item that itself points to the same list, so just a simple foreign key constraint doesn't do it. 3. When a list has more than one list_item, and you want to delete the list_item that its list points to, you have to "re-point" the foreign key constraint on the list first. Do I need to use stored proceures then for all insert, update, delete actions? (4. Anything else that I've not seen?) Is there a "straight" (and tested) solution for this in PostgreSQL, that someone has already implemented and that can be re-used? No, I definitely don't want to get into programming PL/PgSQL myself. especially if the solution has to warrant data integrity under all circumstances. Such as concurrent update, insert, delete etc. TIA, Sincerely, Wolfgang -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql