Results 1 to 4 of 4
  1. #1
    Pavise is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    2

    How to enforce referential integrity between join table and table with composite keys


    Forgive me if this is a silly question but:
    If my first table Customers has an auto CustomerNumber and composite keys [firstname],[lastname],[middleinitial] so as to protect from entering the same customer twice...
    and my sales table has invoice number set as the primary key...
    When I try to create the many to many relationship in a join table and enforce referential integrity, I get the warning "No unique index found for the referenced field of the primary table."
    If I was to delete a customer, wouldn't the relationship in the many to many join table be orphaned? How then would I go about enforcing the referential integrity in a many to many table with other tables with composite keys? Or is it even necessary?
    Hope that makes sense?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Set firstname, lastname, middleinitial as compound index but use autonumber PK. Save this value to Sales table as FK.

    Otherwise, would need 3 fields in Sales for firstname, lastname, middleinitial compound FK to link with compound PK. Advise you NOT do this.

    However, expect someday will encounter 2 people with exact same name. And what if John J Smith comes back and someone neglects to get his middle initial - he will have 2 records in Customers. Names are very poor unique identifiers.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Pavise,
    In addition to June' advice, there are links in the Similar Threads area at the bottom of the page that can be helpful.

    Also, as you probably already know, Access is 1 implementation of a relational database manager, but there are several. And each is based on relational concepts and facilities. So studying some theory and examples of these underlying database principles can help with any RDBMS. Here's an article on Entity relationship modelling that you may find useful.

  4. #4
    Pavise is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    2
    Thanks to both, I will do more study!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. relationships, referential integrity, can't enforce
    By markjkubicki in forum Programming
    Replies: 6
    Last Post: 10-25-2019, 04:40 PM
  2. Enforce Referential Integrity Limit
    By Western_Neil in forum Database Design
    Replies: 8
    Last Post: 05-12-2018, 09:14 AM
  3. Unable to Enforce Referential Integrity
    By DaveT99 in forum Database Design
    Replies: 3
    Last Post: 04-17-2018, 02:34 PM
  4. Is it always bad to NOT enforce referential integrity?
    By Access_Novice in forum Database Design
    Replies: 8
    Last Post: 08-18-2014, 09:59 PM
  5. Replies: 2
    Last Post: 04-18-2013, 05:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums