Thread: Foreign key to 2 tables problem

Foreign key to 2 tables problem

From
Joost Kraaijeveld
Date:
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?


TIA

-- 
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl 




Re: Foreign key to 2 tables problem

From
Neil Saunders
Date:
As far as I'm aware, not without using an intermediatary table (id,
cust_id, supplier_id) . Otherwise, how would you know which table the
foreign key was referencing?

That said, an intermediatary table isn't a very clean solution; What
problem are you trying to solve, exactly?

Kind Regards,

Neil.

On 11/22/05, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl> 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?
>
>
> TIA
>
> --
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J.Kraaijeveld@Askesis.nl
> web: www.askesis.nl
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>


Re: Foreign key to 2 tables problem

From
Achilleus Mantzios
Date:
O Joost Kraaijeveld έγραψε στις Nov 22, 2005 :

> 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?


While there are techniques to accomplish this,
i see a problem:
How do you know if a certain value in owner is to be joined
with e.g. customer and not supplier??

The right way is to have 2 columns that can be null,
pointing to customer,supplier respectively,
and then write a trigger to ensure that exactly one
is not null.

> 
> 
> TIA
> 
> 

-- 
-Achilleus



Re: Foreign key to 2 tables problem

From
John McCawley
Date:
I've never seen anything like that.  I'm sure it's conceivable that you 
could write a weird trigger for it, but you have to consider 
maintainability, and what your queries are going to look like.  I 
haven't seen your datamodel, but it would seem that you could accomplish 
what you're looking for by having two separate foriegn key columns in 
the bankaccount table, one for the customer and one for the supplier.  
While your queries may end up somewhat funky, I can't imagine they'd be 
any worse than what would occur with what you're suggesting.

However, if customers or suppliers can have multiple accounts, you are 
going to need an intermediate table, as suggested by Neil.


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?
>
>
>TIA
>
>  
>


Re: Foreign key to 2 tables problem

From
Rod Taylor
Date:
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.
-- 



Re: Foreign key to 2 tables problem

From
John McCawley
Date:
> However, if customers or suppliers can have multiple accounts, you are 
> going to need an intermediate table, as suggested by Neil. 

Scratch that.  If accounts can have multiple owners you'll need an 
intermediate table.