Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Red face SQL Foreign Key nightmare!

    Hey all,

    I am currently creating an asset tracking database for a degree course and have hit a brick wall in regards to setting up a single Foreign key field BUT with multiple references. Below are my tables:

    tbl_ohp_asset
    Asset_ID (PK) (AUTO-INCREMENT set to start at 1000)
    Asset_Type
    Asset_Desc

    tbl_pc_asset
    Asset_ID (PK) (AUTO-INCREMENT set to start at 2000)
    Asset_Type
    Asset_Desc

    tbl_rtr_asset
    Asset_ID (PK) (AUTO-INCREMENT set to start at 3000)
    Asset_Type
    Asset_Desc

    tbl_movements
    Move_ID (PK)
    Move_Date
    Asset_ID (FK) <------------------ Problem point!

    My aim is that when I come to creating an online form I can select, from a drop down box, an item that has been entered into the db previously. It will be an item with the Asset_ID of one of the 3 tables (ohp_asset, pc_asset & rtr_asset) which will enable me not only to select the item in its full entity BUT create a history of where the item has been, using the Asset_No as the FK reference.

    From research into this I know I can make a columns' contents a FK by using the FOREIGN KEY and REFERENCES syntax BUT how do I give the column MULTIPLE references, i.e. reference the ohp_asset, pc_asset AND the rtr_asset table inclusive? This would give me a UNION of some sort.

    I hope this makes sense! Any guidence or hints would be appreciated as I am self teaching SQL which is hard enough on its own!

    Thank you in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your problem lies in a missing supertype table

    do a search for supertype/subtype

    your three asset table tables should be subtype table tables, referencing a supertype table table which logs all assets

    thus, the tbl_movements table table would reference the main assets table table

    simple, yes?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Posts
    3

    Smile Looking into it now!

    Thank you for a VERY quick response.. Will follow the path suggested and will let you know result!!

  4. #4
    Join Date
    Sep 2011
    Posts
    3

    Lightbulb Sort of there!

    I understand the concepts now, and agree with your suggested path BUT after researching and looking around I cannot actually see the syntax to, for eg, make pc_asset a subtype table and then movements a supertype!

    AHHHHHHHHHHHHHHH! I will keep looking and hopefully it will "appear" on my screen LOL!

    Many thanks again..

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, no, movement isn't the supertype

    you need to declare another table, "all_assets", as the supertype table, and it is the only one which has AUTO_INCREMENT

    then ohc_assets, pc_assets, and rtr_assets are subtype tables, with their PKs also asset_id, but not AUT_INCREMENT, and the same asset_id is also a FK to the all_assets table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    of course, once you have declared the all_assets table, you're gonna ask which columns it should have, and the answer is, all the columns that are common to all subtypes

    in fact, it's possible you do not need the subtype tables, and you can simply add a column called "asset_type" to the all_assets table

    the reason for separate subtype tables is when there is a preponderance of columns that are unique to only one subtype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •