Last post Oct 20, 2014 05:10 AM by Kevin Shen - MSFT
Oct 17, 2014 06:49 AM|missy786|LINK
I am writing to seek help, in regards creating foreign key. I have 2 tables, one called prices and names. the relationship between them, if that one name can have many prices and one price can have many names (many-to-many).
The price's data input comes in as CSV file everyday. (please see the example below)
name name_type price UploadDate
ALBA MBS 93.5 17/10/2014
ALESC Trup 58 17/10/2014
ALESC Trup 52 17/10/2014
my question is, i would like to be able to create a functionality, when insert the price's data into the database, it will automatically insert foreign key in the price table (from the names table), and if there is a new price's name, then create a new if
for the name, in the name's table, transferring the name, its type.
In order to achieve this task, where would I start implementing this logic?(in SQL server or application-side) what steps does this involve and is this task achievable, all in sql server side (i.e. store procedure, functions etc..).
Apology in advance, if the question is not clear to understand, i happy to follow up with further questions, if required.
Any help would be very appreciated. Many thanks
Oct 17, 2014 07:03 AM|raju dasa|LINK
For M-M relationship implementation, a Linking/Junction table should be used.
Check this site for sample and explanation:
Oct 17, 2014 07:41 AM|missy786|LINK
Thank you for your reply. I have created junction table called:
Name_Prices --> Junction Table
name_id REFERENCE names (name_id)
price_id REFERENCE prices (price_id)
PRIMARY KEY (name_id, price_id)
However, I am still little unclear of the logic for the following:
"my question is, i would like to be able to create a functionality, when insert the price's data into the database, it will automatically insert foreign key in the price table (from the names table), and if there is a new price's name, then create
a new if for the name, in the name's table, transferring the name, its type. "
If someone, could explain some sort of steps/guide, involved in achieving the above logic. Many thanks.
Oct 20, 2014 04:06 AM|Kevin Shen - MSFT|LINK
Based on my understanding ,it seems that you are using entity framework database first.
When you generate the edmx file,you will get your own dbcontext file.
Then it will create class Names and Prices responding to your table.
The Prices Class must have a reference to class Names. So when you want to add a new prices recode to database,you will write code like below:
Price p=new Price();
Names name=new Names();
It will automatically insert a foreign key for your price table.
Oct 20, 2014 04:52 AM|missy786|LINK
thank you so much for your reply and help. Would I need to reference the my junction table (name_price), in my insert logic or just my 'name' and 'price', in order to insert my prices into my 'price' table database.
Many thanks for your reply.
Oct 20, 2014 05:10 AM|Kevin Shen - MSFT|LINK
You don't need to reference the junction table,in fact the entity framework will maintain the junction table for you.
You can refer to the link about database first: