How does FOREIGN KEY references from one table to two table?

Question

when I design a database for production, I got a problem. I have 5 table's Product, SemiProduct, BOOM,BOOM_Details,Material.

Product(Code,Name,BOOM_Code,etc....)
SemiProduct(Code,Name,BOOM_Code,etc...)
BOOM(BOOM_Code,etc...)
BOOM_Details(ID,BOOM_Code,Component, Percent)
Material(Code,Name, etc..)

My data for each table is :

Product
{Pro1,Product1, BOOMPRO1}
{Pro2,Product2, BOOMPRO2}

-----------
SemiProduct
{SemiPro1,Semi Product1, BOOMSemi1}
{SemiPro2,Semi Product2, BOOMSemi2}

------------
BOOM
{BOOMPRO1}
{BOOMPRO2}
{BOOMSemi1}
{BOOMSemi2}

----------

Material
{M1,Material1}
{M2,Material12}

---------------

BOOM_Details
{1,BOOMPRO1,M1, 20}
{2,BOOMPRO1,M2, 25}
{3,BOOMPRO1,SemiPro1, 25}
{4,BOOMPRO1,SemiPro2, 30}
{5,BOOMPRO2,M1, 20} 
{6,BOOMPRO2,M2, 25} 
{7,BOOMPRO2,SemiPro1, 55}

The problem is col [Component] in [BOOM_Details]. It can be code of Material or Code of SemiProduct. Please help me create FOREIGN KEY from Component REFERENCES to [Material].[Code] and [SemiProduct].[Code] Or show me a different way to design the table with data like that.

Thank you very much !

score 1 · Answer 1 · answered Nov 19 '14 at 03:50

1

This is a common SQL Anti-Pattern. You should change your table structures so that your Material and Code of SemiProduct tables have a column that is a foreign key to the BOOM_Details table. That way you can always join between the tables as needed.

So instead of a field in BOOM_Details that could point to either of the two tables, have a field in both of the tables that contains the primary key of the BOOM_Details table and is constrained by a foreign key constraint.

answered Nov 19 '14 at 03:50

Greg the Incredulous

1,676
4
29
42

Thank Greg, but create a col in Material and SemiProduct can't solve this problem. Because if i have new BOOM_Details has old Material or SemiProduct. EX : BOOM_Details {5,BOOMPRO2,M1, 20} {6,BOOMPRO2,M2, 25} {7,BOOMPRO2,SemiPro1, 55} – Sóng Biếc Nov 19 '14 at 03:59
Could you explain why it won't solve the problem? What other constraints do you have that you haven't mentioned? – Greg the Incredulous Nov 19 '14 at 04:02
If have a col material to REFERENCES to BOOM_Details like {M1, Material1, BOOM_ID} . So how if I have a new BOOM_Details with Material M1 ? One BOOM Details can have much Material, and One Material can be used in more BOOM – Sóng Biếc Nov 19 '14 at 04:09

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

Foriegn key:- A FOREIGN KEY is a column which is used to join two tables. It points to a PRIMARY KEY in another table which we need to join. Here is the example for better understanding.

Read this for better understanding

Coming to the problem:- Here you need the foreign key between Boom_details and Material.So first you have to make sure that there is a primary key(ex:-id column with sequential no. id[1,2,3,4,5] or sequential alphabets[a,b,c,d] etc..) in the first table to which we have to point! Then add the foreign key column in your table from which we are pointing with same set of values as primary key in the first table.

EDIT:-

Here are the links for understanding how to use foreign key across multiple tables

LINK 1

LINK 2

Hope this helps!

edited May 23 '17 at 10:26

Community

1
1

answered Nov 19 '14 at 04:05

Sudhir kumar

549
2
8
31

Thank SUDHIR, But I need create FOREIGN KEY from [Component] REFERENCES to [Material].[Code] and [SemiProduct].[Code]. Not to only [Material].[Code] – Sóng Biếc Nov 19 '14 at 04:16
1

Just give a read of question and answers in the link.I think they may help you! @SóngBiếc – Sudhir kumar Nov 19 '14 at 05:04

How does FOREIGN KEY references from one table to two table?

2 Answers2