Skip site navigation (1) Skip section navigation (2)

Re: Foreign key to 2 tables problem

From: Rod Taylor <pg(at)rbt(dot)ca>
To: Joost Kraaijeveld <J(dot)Kraaijeveld(at)Askesis(dot)nl>
Cc: "Pgsql-sql(at)postgresql(dot)org" <Pgsql-sql(at)postgresql(dot)org>
Subject: Re: Foreign key to 2 tables problem
Date: 2005-11-22 15:39:56
Message-ID: 1132673996.970.223.camel@home (view raw or flat)
Thread:
Lists: pgsql-sql
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.
-- 


In response to

pgsql-sql by date

Next:From: John McCawleyDate: 2005-11-22 15:42:40
Subject: Re: Foreign key to 2 tables problem
Previous:From: John McCawleyDate: 2005-11-22 15:37:33
Subject: Re: Foreign key to 2 tables problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group