Body: <p>Upgrading from 5.67.3 to 5.72.3 I get a warning that the relationship cache needs to be rebuilt. Very similar to <a href="https://civicrm.stackexchange.com/questions/47946/missing-relationship-cache-data-fails-to-rebuild-cache-with-db-error">this SE question</a>, but different.  The rebuild failed.  I then used api4 to run the rebuild and get the following error: (apologies for the text format). I've checked the FK constraints in the database and they appear to be correct. Suggestions as to the next step?</p>
  <p>&quot;INSERT INTO civicrm_relationship_cache (relationship_id, relationship_type_id, orientation, near_contact_id, near_relation, far_contact_id, far_relation, start_date, end_date, is_active, case_id)
  SELECT rel.id, rel.relationship_type_id, &quot;a_b&quot;, rel.contact_id_a, reltype.name_a_b, rel.contact_id_b, reltype.name_b_a, rel.start_date, rel.end_date, rel.is_active, rel.case_id
  FROM civicrm_relationship rel
  INNER JOIN civicrm_relationship_type reltype ON rel.relationship_type_id = reltype.id
  ON DUPLICATE KEY UPDATE relationship_type_id = rel.relationship_type_id, near_contact_id = rel.contact_id_a, near_relation = reltype.name_a_b, far_contact_id = rel.contact_id_b, far_relation = reltype.name_b_a, start_date = rel.start_date, end_date = rel.end_date, is_active = rel.is_active, case_id = rel.case_id
  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (<code>dcmadmin_civicrm</code>.<code>civicrm_relationship_cache</code>, CONSTRAINT <code>FK_civicrm_relationship_cache_near_contact_id</code> FOREIGN KEY (<code>near_contact_id</code>) REFERENCES <code>civicrm_contact</code> (<code>id</code>) ON DELETE CASCAD)]&quot;</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-05-17T16:56:39.793Z
Comments.Text: It sounds like you have a record referencing a nonexistent contact. Try `select r.id from civicrm_relationship r left join civicrm_contact c on r.contact_id_a = c.id where c.id is null`. If that turns up nothing replace id_a with id_b.
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-05-18T07:22:34.240Z
Comments.Text: Thanks for the quick response.  Yes, there are thousands of them, which of course asks the question of why?  I've looked at a few and it might be to do with case management
Comments.UserDisplayName: Andy Clark
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-05-20T07:56:09.467Z
Comments.Text: Does this help? https://civicrm.stackexchange.com/a/38243/245 OR https://civicrm.stackexchange.com/a/40869/245
Comments.UserDisplayName: jitendra - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-05-20T11:59:18.067Z
Comments.Text: Thanks @jitendra - I also had that problem in the 5.28 upgrade (I'd forgotten). The root cause was missing FK constraints for the relationship table (& many others). Presently thinking about how to fix that, which is a big problem.
Comments.UserDisplayName: Andy Clark
Tags: <relationships><sql>
Title: Relationship cache rebuild failure in upgrade to 5.72.3
ThreadText: # Relationship cache rebuild failure in upgrade to 5.72.3
  
  (Asked by: username_0 on 2024-05-17)
  
  Upgrading from 5.67.3 to 5.72.3 I get a warning that the relationship cache needs to be rebuilt. Very similar to [this SE question](https://civicrm.stackexchange.com/questions/47946/missing-relationship-cache-data-fails-to-rebuild-cache-with-db-error), but different. The rebuild failed. I then used api4 to run the rebuild and get the following error: (apologies for the text format). I've checked the FK constraints in the database and they appear to be correct. Suggestions as to the next step?
  
  "INSERT INTO civicrm\_relationship\_cache (relationship\_id, relationship\_type\_id, orientation, near\_contact\_id, near\_relation, far\_contact\_id, far\_relation, start\_date, end\_date, is\_active, case\_id)
  SELECT rel.id, rel.relationship\_type\_id, "a\_b", rel.contact\_id\_a, reltype.name\_a\_b, rel.contact\_id\_b, reltype.name\_b\_a, rel.start\_date, rel.end\_date, rel.is\_active, rel.case\_id
  FROM civicrm\_relationship rel
  INNER JOIN civicrm\_relationship\_type reltype ON rel.relationship\_type\_id = reltype.id
  ON DUPLICATE KEY UPDATE relationship\_type\_id = rel.relationship\_type\_id, near\_contact\_id = rel.contact\_id\_a, near\_relation = reltype.name\_a\_b, far\_contact\_id = rel.contact\_id\_b, far\_relation = reltype.name\_b\_a, start\_date = rel.start\_date, end\_date = rel.end\_date, is\_active = rel.is\_active, case\_id = rel.case\_id
  [nativecode=1452 \*\* Cannot add or update a child row: a foreign key constraint fails (`dcmadmin_civicrm`.`civicrm_relationship_cache`, CONSTRAINT `FK_civicrm_relationship_cache_near_contact_id` FOREIGN KEY (`near_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCAD)]"
  
  **Tags:** relationships, sql
  
  > **Comments:**
  > **username_1**: It sounds like you have a record referencing a nonexistent contact. Try `select r.id from civicrm_relationship r left join civicrm_contact c on r.contact_id_a = c.id where c.id is null`. If that turns up nothing replace id_a with id_b.  
  > **username_0**: Thanks for the quick response.  Yes, there are thousands of them, which of course asks the question of why?  I've looked at a few and it might be to do with case management  
  > **username_2**: Does this help? https://civicrm.stackexchange.com/a/38243/245 OR https://civicrm.stackexchange.com/a/40869/245  
  > **username_0**: Thanks @username_2 - I also had that problem in the 5.28 upgrade (I'd forgotten). The root cause was missing FK constraints for the relationship table (& many others). Presently thinking about how to fix that, which is a big problem.  
  
  ---
  
  ## 0 answers
  
  