Results 1 to 4 of 4
  1. #1
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    76

    Unable to Enforce Referential Integrity

    I am unable to enforce referential integrity for the relationship MPN_Id to MPN_id, all fields types are Short Text


    The main table SPD1 was received with all records, say 1000 records
    The Id fields in this main table SPD1 are currently blank or has a value that is held in the look-up table (I had already started populating before creating the enorforced integrity)
    The two look up tables as shown hold the unique values
    The two relationship are both to show all data in SPD1 and only the data that matches the look up table and are 1 to many


    I am able to create the enforce referential integrity for the Comment_Id to Comment_Id, BUT not for the MPN_Id/MPN_Id

    The idea is that when I populate the Id in the SPD1 table, its corresponding look-up data will be brought in
    Any ideas WHY I am unable to enforce the integrity, as I cant see any difference in data or relationship??

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1 have you set MPN_ID as primary key in the parent table
    2. if you already have records in your tables, they must already meet referential integrity rules in order to enforce it. i.e. all child records have an existing parent record and there are no orphans

    this implies you do not meet the rules
    The Id fields in this main table SPD1 are currently blank

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The image isn't showing so can only offer a suggestion.
    RI can only be enforced if both tables contain records for the linked fields.
    So if table A has 1000 records but table B has equivalent records for only 999 of those, RI can't be implemented as it would be immediately violated.

    Do an unmatched query on the 2 tables to find out which has missing (or additional) records.
    Deal with that and then apply RI.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    76
    Thanks Ajax, pity the diagram didnt show. The blanks wasnt the problem it was a data error in the 2nd table
    Thanks for helping

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

Similar Threads

  1. 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
  2. Replies: 2
    Last Post: 04-18-2013, 05:56 AM
  3. Referential Integrity
    By Paul H in forum Database Design
    Replies: 3
    Last Post: 11-14-2011, 03:07 PM
  4. referential integrity
    By askjacq in forum Database Design
    Replies: 1
    Last Post: 10-14-2011, 03:23 PM
  5. Referential Integrity
    By U810190 in forum Access
    Replies: 1
    Last Post: 03-29-2010, 05:21 PM

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