Date: Tue, 30 Apr 2013 16:39:05 +0200
From: Wolfgang Keller <feliphil@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Correct implementation of 1:n relationship with n>0?
Message-Id: <20130430163905.77d43412c0176c3d9ebd8d90@gmx.net>
Mail-Copies-To: never
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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