Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Posts
    3

    Unanswered: multiple fields as primary key??

    Hello,
    I have a UsageData table with the following fields: Facility, UsageDate, ProductID, BegInventory, PurchaseAmount, EndInventory. The user can only insert one entry per Facility per month (UsageDate gets set to mm/1/yyyy programatically) per ProductID - therefore the combination of these three fields is what makes that record unique, and together they serve as the primary key. I have seen and heard that using multiple fields as a primary key is not a good idea. However, I have thought about the AutoNumber option, but unless I am missing something very obvious, I do not see how this will help me since the user would still be able to insert an entry for the same Facility, UsageDate, and ProductID as long as the BegInventory, PurchaseAmount, and EndInventory are different than the "original" entry. Is there something obvious that I am missing, or is my only option in this case to use multiple fields as my primary key?
    Thanks!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CSturtevant
    I have seen and heard that using multiple fields as a primary key is not a good idea.
    it's not your fault, but you have seen and heard wrong

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

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I have asked this question before and what you are asking about is called a composite key or compound key. The thread I started turned into quite a debate over using a composite key or adding and autonumber field, a surrogate key. You will find people on both sides of the debate. With people on both sides, I think either way is acceptable.

    My personal opinion is if you can uniquely identify a record using the fields in the table then why add an additional field to your table. You may find that creating your application around a composite key is a tiny bit more challenging. You will have to make sure all of the fields are entered in the table otherwise you will get a very unfriendly error message from Access about your Primary Key.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DCKunkle
    You will have to make sure all of the fields are entered in the table otherwise you will get a very unfriendly error message from Access about your Primary Key.
    you will have to make sure all of the fields are entered in the table if you're using a surrogate autoincrement key, too

    otherwise you will get data inconsistencies such as duplicates

    that's one of the more frequently recurring threads on database forums -- "HHHALP! i need to remove all duplicates except one!!11!1"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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