From: | Wolfgang Keller <feliphil(at)gmx(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | *Proper* solution for 1..* relationship? |
Date: | 2013-04-26 11:59:11 |
Message-ID: | 20130426135911.fc869785e6651576b1e4ddd8@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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?
TIA,
Sincerely,
Wolfgang
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2013-04-26 12:55:05 | Re: *Proper* solution for 1..* relationship? |
Previous Message | Thom Brown | 2013-04-26 11:54:44 | Re: [NOVICE] How to Recover iPhone Contacts You Lost Somehow? |