locked
How to create foreign key automatically? RRS feed

  • Question

  • I am writing to seek help, in regards creating foreign key automatically, when I insert data into my ''price'' table.  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). Hence, i have junction table called  "Name_Prices", as shown below in the sample database schema:

    Names
    name_id [pk]
    name
    type
    UploadDate
    
    Prices
    Price_id [pk]
    name_id [fk]
    price
    uploadDate
    
    Name_Prices
    name_id REFERENCE names (name_id)
    price_id REFERENCE prices (price_id)
    PRIMARY KEY (name_id, price_id)

    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 desire goal/output is to be able to create a functionality, where I can insert the price's data into the database (''prices''), it will automatically insert foreign key in the price table (from the names table), and if there is a new price's name, then the database will create a new name id for it, in the name's table, transferring the name, its type, from the CSV input data. 

    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

    Friday, October 17, 2014 11:53 AM

Answers

All replies

  • Your design is not correct.

    if you already have the junction table why have name_id again as a fk in price?


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, October 17, 2014 11:57 AM
  • As noted above, I modified the design:

    Products
    Product_id [pk]
    Product
    type
    UploadDate
    
    Prices
    Price_id [pk]
    price
    uploadDate
    
    Product_Prices
    Product_id REFERENCE Products (Product_id)
    price_id REFERENCE prices (price_id)
    PRIMARY KEY (Product_id, price_id)
    Note 





    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    • Edited by Kalman Toth Friday, October 17, 2014 12:38 PM
    • Marked as answer by missy786 Thursday, October 23, 2014 9:59 AM
    Friday, October 17, 2014 12:37 PM