Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple-field Primary Key (ver1.0)

    My tables use multiple-field primary keys to create unique records. A referential integrity problem arises when these tables are joined to a third that shares a field common to both joined tables. The problem can be avoided by creating a duplicate field and joining one table to it. But this method is not efficient.

    Any thoughts?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple-field Primary Key (ver1.0)

    Welcome to Woody's Lounge!

    Can you provide more information? Where and how does the problem with referential integrity arise?

    Oh, and what is ver1.0? Not Access version 1, I hope?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple-field Primary Key (ver1.0)

    Using fields named Name and Date is asking for problems, so I hope you aren't really using those.

    A simple way to avoid this whole issue is to use a multiple-field unique key in your intermediate tables to restrict data but use an autonumber as the primary key to uniquely identify each record. The autonumber value can then be inserted as a long in the Transactions table to create the many-to-many join between the Date table and the Sector table. It looks to me like the "Date" table is actually a payments table, so I see no reason not to have a PaymentID (autonumber) that could then be inserted into the Transaction table to create a link between payment and Transaction. You don't need the name field in Transaction at all, so you could easily create a SectorID field (autonumber) in Sector and insert that value into the Transaction table to create a link between Sector and Transaction. Since Name is available through either link, you don't need it at all in Transaction if you follow this design.
    Charlotte

Posting Permissions

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