Thread: Foreign key to 2 tables problem
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
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 >
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
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 > > >
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. --
> 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.