From: Rod Taylor <pg@rbt.ca>
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Cc: "Pgsql-sql@postgresql.org" <Pgsql-sql@postgresql.org>
In-Reply-To: <1132673053.5701.9.camel@Panoramix>
References: <1132673053.5701.9.camel@Panoramix>
Date: Tue, 22 Nov 2005 10:39:56 -0500
Message-Id: <1132673996.970.223.camel@home>
Mime-Version: 1.0
Subject: Re: Foreign key to 2 tables problem
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Tue, 2005-11-22 at 16:24 +0100, Joost Kraaijeveld wrote:
> Hi,
> 
> Is there a way to create a foreign key to 2 tables: e.g. a bankaccount
> table that has a column "owner", that must point to a record in either
> the customer or the supplier table?

No. What you need is an owner table that customers and suppliers are
inherited from. Put your 'entity' data into the owner table. Put
customer and supplier specific information into the customer and
supplier structures.

create table owner (owner_name varchar(120) primary key, ...);
create table customer (customer_name varchar(120) references
owner, ...);
create table supplier (supplier_name varchar(120) references
owner, ...);

create table bankaccount (owner_name varchar(120) references owner);


You can use a periodic check to ensure that all owners are a customer or
supplier just incase your code breaks.

Incidentally, this also allows a single entity with a single bankaccount
to be both a customer and a supplier. They can supply you with product X
and purchase product Y without 2 different accounts.
--