Unable to Import, edit certain records and edit Group settings: DB Error: constraint violation

Question

I am unable to make certain edits to CiviCRM; unable to edit certain records and unable to edit the visibility of groups.

I think the problem is this: Cannot add or update a child row: a foreign key constraint fails (`ea...")

The error message is referring to a group '58' which I deleted and replaced with group "62".

How do I find the exact problem and fix it?

I'm on WP 4.5.3 with CiviCRM 4.7.9.

Here is the error message:

Database Error Code: Cannot add or update a child row: a foreign key constraint fails (xxx_civicrm.civicrm_group_contact, CONSTRAINT FK_civicrm_group_contact_group_id FOREIGN KEY (group_id) REFERENCES civicrm_group (id) ON DELETE CASCADE), 1452

Answer 1

It seems you have some entries remaining in civicrm_group_contact which point to the deleted civicrm_group entry?

You can confirm this with a query to look for group contact entries which point to a missing group.

-- Show contacts who belong to a group that doesn't exist.
SELECT * 
  FROM civicrm_group_contact 
 WHERE group_id NOT IN (SELECT id FROM civicrm_group);

If entries are found with that query, you can clear them out (or update them to point to the new group, or export those contact IDs, or ...).

-- Standard disclaimers apply; back up your DB before doing this!
DELETE  
  FROM civicrm_group_contact 
 WHERE group_id NOT IN (SELECT id FROM civicrm_group);

If updating, you'll want to avoid creating duplicate entries by reassigning group_id without checking for existing group additions.