What is the database schema for groups and parent groups?

Question

I need to manually delete a group that is corrupt. Already tried a few things. This corrupt group assigned itself as the parent to other groups and continues to add additional children groups every time 'managed groups' is open. I tried to delete the group via civicrm ui and directly in the db but then unable to edit any records or import records. Error message is below.

I also manually removed parent child relationships directly in the database and then disabled the corrupt group in civicrm. This disabled all the group that were just removed as children.

Also, cannot delete records from the group. And the number of records in this group changes every time 'managed group' is opened. Obviously corrupt and needs to be deleted. Any info on the data schema for groups and parent groups appreciated.

Here is the error when this corrupt group is deleted:

$Fatal Error Details = Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => handle )

[code] => -3
[message] => DB Error: constraint violation
[mode] => 16
[debug_info] => REPLACE INTO civicrm_group_contact ( group_id, contact_id, status ) VALUES ( 58, 39435, 'Added' ) [nativecode=1452 **

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)]

Answer 1

Groups are stored in civicrm_group. Parent/child relationships are stored in civicrm_group_nesting. However, based on the error you've posted, that's not the issue. The issue is that the group was deleted, but there are still contacts who are a member of the group (which is stored in civicrm_group_contact).

I'm not sure how you did that - as the error reports, there should be a "delete cascade" when you delete a group to remove the civicrm_group_contact records. To fix this, make sure you have a backup, then:

• Temporarily disable foreign key constraints.
• If your group id is 54, run DELETE from civicrm_group_contact WHERE group_id = 54.
• Re-enable your foreign key constraints.

Hopefully that should clear this error!