Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Explain Referential Integrity After A Repair? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Using the Northwind database demo as an example, there is an Enforce Referential Integrity and Cascade Delete Related Record relationship between Orders and Order Details.

    You delete an Order, the Order Details are deleted automatically.

    If someday you get a corrupted Order record you will normally get #Deleted in all the fields of the bad record.

    When you run Compact and Repair Database or JetComp, the repair utilities will put #### in all fields of the offending record.

    What happens to the related Order Details records?

    Thanks, John

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

    Re: Explain Referential Integrity After A Repair? (a2k (9.0.6926) SP-3 Jet

    I don't know for certain, but I suspect that the related Order Details records will remain behind as orphans. It is probably a good idea to create a Find Unmatched query and test for orphaned records after a corrupted table that is on the "one" side of a relationship has had to be repaired.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Explain Referential Integrity After A Repair? (a2k (9.0.6926) SP-3 Jet

    They do remain and I do use unmatched queries to remove orphans.

    Reason I ask is I looked at one of my customers relationships the other and one was removed, was wondering, how can a relationship be removed if it wasn

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

    Re: Explain Referential Integrity After A Repair? (a2k (9.0.6926) SP-3 Jet

    Relationships always involve an index. This can be one defined explicitly by you, or created implicitly (and invisibly) by Access. Indexes can become corrupted as well as tables; perhaps the Compact and Repair process removes a relationship if an associated index is corrupt.

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

    Re: Explain Referential Integrity After A Repair? (a2k (9.0.6926) SP-3 Jet

    When you compact the database, the only way to preserve orphan child records is to remove the relationship. Otherwise, they would be in violation of the index since the parent record is gone from the corrupted table. Jet doesn't normally drop records arbitrarily, it doesn't let them be created in the first place under referential integrity. If that integrity is compromised by corruption, it removes the relationship and lets you decide what to do with those records.
    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
  •