From: Rod Taylor <pg@rbt.ca>
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Cc: Pgsql-sql@postgresql.org <Pgsql-sql@postgresql.org>
Subject: Re: Foreign key to 2 tables problem
Date: Tue, 22 Nov 2005 10:39:56 -0500
Message-ID: <1132673996.970.223.camel@home> (raw)
In-Reply-To: <1132673053.5701.9.camel@Panoramix>
References: <1132673053.5701.9.camel@Panoramix>
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.
--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pg@rbt.ca, J.Kraaijeveld@Askesis.nl
Subject: Re: Foreign key to 2 tables problem
In-Reply-To: <1132673996.970.223.camel@home>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox