Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Referential Integrity (2000)

    I think something in the application is corrupt and needs to be rebuilt, but I'd appreciate insight, if anyone has any!

    There are several parent-child relationships, with referential integrity. The PurchaseOrder table and PurchaseOrderDetails is one such linked set. There are other parent-child relationships unconnected to the Purchase Order set.

    The PurchaseOrder Form has a subform: PurchaseOrderDetails. The linkage between form and subform is intact.

    I can create PurchaseOrderDetail records without creating PurchaseOrder records. In other words, I can create orphan child records. I have removed the links from the relationships screen and re-created them. I can create a 1 to many link between tables where the child has records with no matching parent records.Access should not let me do this. Referential Integrity should prevent it.

    This happens in all the Parent/child relationships and forms in the system - and each set was created from scratch.

    Compact and Repair has no effect. Is there anything else to try before deciding this file is toast? Thanks for your thoughts.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Referential Integrity (2000)

    P.S. - I know more now and am even more confused. My question is no longer confined to a single file - it seems to be a generic Access question. I have a parent table with an Autonumber keyfield and a linked (with referential integrity) child table, and I have created a form/subform system. If I go directly to the child without putting anything in the parent table, the autonumber never fills into the parent - thus no parent record. But I can fill in all the child records I want. They add into the child table. I had not run into the problem before because it is illogical to use the system that way, but Access is letting me do it!

    Am I overlooking something obvious? I hope?

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

    Re: Referential Integrity (2000)

    What happens if you try to add records to the child table directly (not through a form) with a parent value that does not exist in the parent table?

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Referential Integrity (2000)

    The one overlooked weakness when you have a parent-child relations with enforced RI is that you can create records in the child if the linking field (in your case PurchaseOrderNo) is Null in the child. You can close this loophole by making sure this field is required in the child, or making it part of the PK.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Referential Integrity (2000)

    Thank you Mark. While I'm no Access expert, I've used, taught, developed in Access for several years. I have been teaching that Referential Integrity precludes having orphaned child records - PERIOD. I wonder how many other yawning exceptions I have missed!

    Happy Spring!

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Referential Integrity (2000)

    I was putting in a partial record (see Mark L's comment) and leaving the child's link field null. In that case, yes, I can create a child record in the table. (It was not a logical way to use the system - I was trying to break it!)
    Thanks for the response

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Referential Integrity (2000)

    The reason this escapes most people is a basic misunderstanding of Null. Most people try to think of Null as a value that they can compare, such as with other values. Therefore the think is that a record with a Null PurchaseOrderID in the child can only exist if there is a record with a Null value for this field in the Parent. Even thinking of Null as having no value can still cause problems, because people think they can match it up with another Null.

    It is probably better to think of Null as "possibly being anything"! In that context, that record with the Null value in the child could in fact have a parent, we just don't really know which one!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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