On Ôñé 30 Áðñ 2013 16:39:05 Wolfgang Keller wrote: > 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. >
I think your best bet is a trigger. use RAISE EXCEPTION to indicate an erroneous situation so as to make the transaction abort. (there is nothing wrong in getting your hands dirty with pl/pgsql btw) > TIA, > > Sincerely, > > Wolfgang > > > - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
