Results 1 to 12 of 12
  1. #1
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

    One-to-one Referential Integrity Cannot Add Record

    I am having trouble with a one-to-one relationship and breaking referential integrity rules. I've attached a stripped down db for you all to take a look at.



    1) I want to maintain referential integrity in these relationships
    2) When I create a new row in the table [Addresses] I get NO error even though its' associated table is empty (I want this)
    3) When I create a new row in the table [JobItemValues] I get "You cannot add or change a record because a related record is required in the table 'ChangeOrderValues'" (I don't want this)

    Item number 3 is my problem. I need to be able to create items it JobItemValues table that possibly wont have a related item in ChangeOrderValues, sometimes I will, sometimes I wont.

    I am scratching my head and can not figure out the difference as to why the Address table will allow records but the JobItemValues table will not???

    Can anyone spot the critical difference?
    Attached Files Attached Files

  2. #2
    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
    ??? Your tables are not related.

    What is a CompanyAddress vs an Address? Why 2 tables?
    You need to identify a process/business fact(s) in order for anyone to offer advice
    re JobItemValues, ChangeOrderValues to each other and/or any other Table.

    Please describe the business this database is intended to support in clear, simple English. No jargon.
    Make sure the description is complete -- that is, makes reference to all of the tables.

    In a nut shell tell us the "what, why, who, when, where, how much and how often" of the business.

    What is the 1 to 1 you mention, and why do you say it is 1 to 1? How would a reader know that?

    Good luck.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Addresses is the 'master' (or lookup table) it has the PK that is saved as FK in CompanyAddresses (the 'child' or dependent table).

    You have a number field in ChangeOrderValues defined as part of a compound primary key and it is linked to autonumber field in JobItemValues. Yet the autonumber field is not defined as a PK, two other fields are defined as compound primary key. If you want to save the autonumber into ChangeOrderValues, then it should be PK and the other two fields in JobItemValues can be set as compound index to prevent duplicate pairs, but not as compound key.

    Why are these even split into two tables if they are truly 1-to-1?
    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.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    @orange,
    Maybe I've posted in the wrong subforum?

    This isn't really a question on high level table structure design. I'm trying to figure out why these specific tables in this specific situation are behaving as described in the attached database. I could have named them TableA, TableB, etc. Why is Access not letting me add rows to [JobItemValues] but is letting me add rows to [Addresses] when all other variables *appear* the same?
    Click image for larger version. 

Name:	4.png 
Views:	16 
Size:	22.9 KB 
ID:	30061

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you read post 3?
    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.

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by June7 View Post
    Addresses is the 'master' (or lookup table) it has the PK that is saved as FK in CompanyAddresses (the 'child' or dependent table).

    You have a number field in ChangeOrderValues defined as part of a compound primary key and it is linked to autonumber field in JobItemValues. Yet the autonumber field is not defined as a PK, two other fields are defined as compound primary key. If you want to save the autonumber into ChangeOrderValues, then it should be PK and the other two fields can be set as compound index to prevent duplicate pairs, but not as compound key.
    That'll do it! Thank you, June7. I had tried changing the AutoNumber to PK but it still didn't work, I imagine it had something to do with the generated Index settings on the table but I'm unfamiliar with the Index Properties interface. When I deleted the table and remade it with the AutoNumber as the PK all works as wanted. I need to familiarize myself with the Index Properties window.

    Also, I didn't know I could create compound non-pk indexes. Sweet!

    For anyone else's future reference:
    http://www.geeksengine.com/article/c...ex-access.html

    Why are these even split into two tables if they are truly 1-to-1?
    Basically a [JobItemValue] may or may not have half a dozen other foreign keys, mostly not. Rather than populate the table with those columns mostly being null I've chosen to use relationship tables and one-to-one, some of them also hold other relevant information that would otherwise be irrelevant/null. The one presented in this post is just one example of many.

    Did you read post 3?
    Sure did! Y'all just keep replying while I'm in the middle of replying

    Now to figure out how to mark solved...

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    On a side note I've now learned that if you create a table Create->Table Design, instead of Create->Table, and you define your own id field, access will generate an extra index on that field. Does this bloat the database or otherwise harm anything?

    Two indexes on one field:
    Click image for larger version. 

Name:	4.png 
Views:	14 
Size:	33.0 KB 
ID:	30064

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, I think it does cause bloat with an additional index, delete if you don't need it.
    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.

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by June7 View Post
    Yes, I think it does cause bloat with an additional index, delete if you don't need it.
    Got it, thank you. Are there times when I would need it?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you don't use the autonumber field as primary key but still might have need for it in some complex queries.
    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.

  11. #11
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by June7 View Post
    If you don't use the autonumber field as primary key but still might have need for it in some complex queries.
    Just to clarify: we're talking about having two indexes defined on the same field?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If the autonumber ID is not defined as primary key then can still index it. If it is defined as primary key then there is no need for the other index. Seems odd that Table design does that, never really noticed, don't often give attention to the index settings.
    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.

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

Similar Threads

  1. Referential integrity
    By Lou_Reed in forum Access
    Replies: 14
    Last Post: 12-17-2015, 03:08 PM
  2. Referential Integrity
    By lefty2cox in forum Forms
    Replies: 2
    Last Post: 11-30-2015, 07:26 AM
  3. Referential Integrity
    By YunqHero in forum Forms
    Replies: 4
    Last Post: 12-17-2012, 05:05 PM
  4. Referential Integrity
    By Desstro in forum Database Design
    Replies: 4
    Last Post: 06-13-2010, 01:29 PM
  5. Referential Integrity
    By jbarrum in forum Access
    Replies: 5
    Last Post: 01-14-2010, 09:04 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