A constraint trigger is the closest feature to an ASSERTION statement defined by the SQL standard.
You'd begin a transaction, Insert to List and Listitem. Then commit. If you try this any other way, your constraint trigger will fire off an exception and will force a rollback of the initial insert.
IHTH.
On Fri, Apr 26, 2013 at 4:59 AM, Wolfgang Keller <feliphil@gmx.net> wrote:
It hit me today that a 1..* relationship can't be implemented just by a single foreign key constraint. I must have been sleeping very deeply not to notice this.
E.g. 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 "proper" solution would require:
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. Triggers, stored procedures...?
(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?