Answers.Body: <p>I think there might me some orphan data in your civicrm_activity table. Can you run the below query to check if you get any result?</p>
  
  <pre><code>SELECT * FROM `civicrm_activity` ca 
  LEFT JOIN civicrm_campaign cc ON cc.id = ca.campaign_id
  WHERE ca.campaign_id IS NOT NULL AND cc.id IS NULL;
  </code></pre>
  
  <p>If you get result then probably you will need to delete this activity or make campaign_id to NULL for all this records.</p>
  
  <blockquote>
    <p>Note: Foreign key is important in CiviCRM, Many foreign keys are set to ON CASCADE DELETE or SET NULL. So its possible you might end up more orphan data in your database if you disable them which may have problem while upgrading CiviCRM in future.</p>
  </blockquote>
  
  <p>You can use below query to either update the civicrm_activity.campaign_id or delete campaign activities</p>
  
  <pre><code>UPDATE `civicrm_activity` ca 
  LEFT JOIN civicrm_campaign cc ON cc.id = ca.campaign_id
  SET ca.campaign_id = NULL
  WHERE ca.campaign_id IS NOT NULL AND cc.id IS NULL;
  
  OR
  
  DELETE ca.* FROM `civicrm_activity` ca 
  LEFT JOIN civicrm_campaign cc ON cc.id = ca.campaign_id
  WHERE ca.campaign_id IS NOT NULL AND cc.id IS NULL;
  </code></pre>
  
  <blockquote>
    <p>Note: Backup your database before you execute above sql.</p>
  </blockquote>
  
  <p>HTH</p>
  
  <p>Pradeep</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-19T23:15:32.497Z
Answers.Text: That brought up 270 results. Do you know how to easily clear them out? This isn't our main db so the data isn't overly crucial if we lose some here. 
  
  Any idea what might have caused it? Contacts deleted but still in the trash can maybe? We have about 25,000 contacts in the db.
Answers.UserDisplayName: JustinL
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-19T23:17:27.337Z
Answers.Text: Can you run an query to set campaign_id to null? This might be because when you deleted campaign their data remained in db...
Answers.UserDisplayName: Pradeep Nayak
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-20T00:41:40.157Z
Answers.Text: Do you have a suggested query to do that? :-) There is nothing in civicrm_campaign. Might I have imported contacts from another db who were linked to a campaign in that db that didn't have the corresponding campaign in this db? Is that possible? I'm thinking that might be the problem.
Answers.UserDisplayName: JustinL
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-20T12:45:08.503Z
Answers.Text: Might be possible. I have updated my answer to include query to update or delete activity. Please let me know how it goes.
Answers.UserDisplayName: Pradeep Nayak
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-17T03:01:11.613Z
Answers.Text: After running that UPDATE command and then exporting the db, the import worked. Thanks Pradeep.
Answers.UserDisplayName: JustinL
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-17T07:17:19.943Z
Answers.Text: So can this marked as answered?
Answers.UserDisplayName: Pradeep Nayak
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-18T10:46:25.440Z
Answers.Text: Yeah I suppose so. Although I do have a few questions below. Seems I can't mark it answered due to lack of reputation :-\
Answers.UserDisplayName: JustinL
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-19T08:53:37.903Z
Answers.LastActivityDate: 2018-06-20T12:44:08.250Z
Answers.LastEditDate: 2018-06-20T12:44:08.250Z
Answers.UserDisplayName: Pradeep Nayak
Answers.Body: <p>I agree. It's safe to disable them temporarily but if you get errors when trying to enable them there is data missing issue.  You might want to run data check on your live data to be sure it's not getting DB error. And how are you exporting?  You should be using mysqldump and taking out the definer clauses. </p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-17T00:50:01.353Z
Answers.Text: How do I run a data check? I did the export through phpmyadmin but get the error on mysql import on ssh.
Answers.UserDisplayName: JustinL
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-20T19:46:27.497Z
Answers.LastActivityDate: 2018-06-20T19:46:27.497Z
Answers.UserDisplayName: Webmaster TheCMG
Body: <p>When we export our db and try restoring to another server we get the foreign key issue below so cant progress. Is it safe to Disable foreign key checks when importing into the new server?</p>
  <p>(we tried to fix the indexes through the api even)</p>
  <h1>ERROR:</h1>
  <p>SQL query:</p>
  <pre><code>ALTER TABLE `civicrm_activity`
   ADD CONSTRAINT `FK_civicrm_activity_campaign_id` FOREIGN KEY 
   (`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL,
   ADD CONSTRAINT `FK_civicrm_activity_original_id` FOREIGN KEY (`original_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE,
   ADD CONSTRAINT `FK_civicrm_activity_parent_id` FOREIGN KEY (`parent_id`) 
   REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE,
   ADD CONSTRAINT `FK_civicrm_activity_phone_id` FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone` (`id`) ON DELETE SET NULL,
   ADD CONSTRAINT `FK_civicrm_activity_relationship_id` FOREIGN KEY 
   (`relationship_id`) REFERENCES `civicrm_relationship` (`id`) ON DELETE SET NULL
  </code></pre>
  <p>MySQL said: Documentation</p>
  <blockquote>
  <p>1452 - Cannot add or update a child row: a foreign key constraint fails (<code>chercivi_life1</code>.<code>#sql-622_4c0a</code>, CONSTRAINT <code>FK_civicrm_activity_campaign_id</code> FOREIGN KEY (<code>campaign_id</code>) REFERENCES <code>civicrm_campaign</code> (<code>id</code>) ON DELETE SET NULL)</p>
  </blockquote>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-06-19T08:21:36.873Z
Comments.Text: Hi @JustinL, it would help, if you explain, how you do the export and the restore. In general, you can disable foreign keys checks, but if it is not possible to enable them again, you have trouble.
Comments.UserDisplayName: Kainuk - CiviCooP
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-06-19T23:11:35.543Z
Comments.Text: I'm using phpadmy admin at both ends to export from server 1 to server 2. So once the data is imported with the foreign keys turned off is there a way to turn them back on? Or a way to troubleshoot?
Comments.UserDisplayName: JustinL
Tags: <mysql>
Title: Foreign Key issues when restoring db
ThreadText: # Foreign Key issues when restoring db
  
  (Asked by: username_0 on 2018-06-19)
  
  When we export our db and try restoring to another server we get the foreign key issue below so cant progress. Is it safe to Disable foreign key checks when importing into the new server?
  (we tried to fix the indexes through the api even)
  ERROR:
  SQL query:
  ALTER TABLE `civicrm_activity`
   ADD CONSTRAINT `FK_civicrm_activity_campaign_id` FOREIGN KEY 
   (`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL,
   ADD CONSTRAINT `FK_civicrm_activity_original_id` FOREIGN KEY (`original_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE,
   ADD CONSTRAINT `FK_civicrm_activity_parent_id` FOREIGN KEY (`parent_id`) 
   REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE,
   ADD CONSTRAINT `FK_civicrm_activity_phone_id` FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone` (`id`) ON DELETE SET NULL,
   ADD CONSTRAINT `FK_civicrm_activity_relationship_id` FOREIGN KEY 
   (`relationship_id`) REFERENCES `civicrm_relationship` (`id`) ON DELETE SET NULL
  
  MySQL said: Documentation
  
  1452 - Cannot add or update a child row: a foreign key constraint fails (chercivi_life1.#sql-622_4c0a, CONSTRAINT FK_civicrm_activity_campaign_id FOREIGN KEY (campaign_id) REFERENCES civicrm_campaign (id) ON DELETE SET NULL)
  
  **Tags:** mysql
  
  > **Comments:**
  > **username_1**: Hi @username_0, it would help, if you explain, how you do the export and the restore. In general, you can disable foreign keys checks, but if it is not possible to enable them again, you have trouble.  
  > **username_0**: I'm using phpadmy admin at both ends to export from server 1 to server 2. So once the data is imported with the foreign keys turned off is there a way to turn them back on? Or a way to troubleshoot?  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2018-06-19. Score: 1 | Accepted answer
  
  I think there might me some orphan data in your civicrm_activity table. Can you run the below query to check if you get any result?
  SELECT * FROM `civicrm_activity` ca 
  LEFT JOIN civicrm_campaign cc ON cc.id = ca.campaign_id
  WHERE ca.campaign_id IS NOT NULL AND cc.id IS NULL;
  
  If you get result then probably you will need to delete this activity or make campaign_id to NULL for all this records.
  
  Note: Foreign key is important in CiviCRM, Many foreign keys are set to ON CASCADE DELETE or SET NULL. So its possible you might end up more orphan data in your database if you disable them which may have problem while upgrading CiviCRM in future.
  
  You can use below query to either update the civicrm_activity.campaign_id or delete campaign activities
  UPDATE `civicrm_activity` ca 
  LEFT JOIN civicrm_campaign cc ON cc.id = ca.campaign_id
  SET ca.campaign_id = NULL
  WHERE ca.campaign_id IS NOT NULL AND cc.id IS NULL;
  
  OR
  
  DELETE ca.* FROM `civicrm_activity` ca 
  LEFT JOIN civicrm_campaign cc ON cc.id = ca.campaign_id
  WHERE ca.campaign_id IS NOT NULL AND cc.id IS NULL;
  
  
  Note: Backup your database before you execute above sql.
  
  HTH
  Pradeep
  
  > **Comments:**
  > **username_0**: That brought up 270 results. Do you know how to easily clear them out? This isn't our main db so the data isn't overly crucial if we lose some here.   Any idea what might have caused it? Contacts deleted but still in the trash can maybe? We have about 25,000 contacts in the db.  
  > **username_2**: Can you run an query to set campaign_id to null? This might be because when you deleted campaign their data remained in db...  
  > **username_0**: Do you have a suggested query to do that? :-) There is nothing in civicrm_campaign. Might I have imported contacts from another db who were linked to a campaign in that db that didn't have the corresponding campaign in this db? Is that possible? I'm thinking that might be the problem.  
  > **username_2**: Might be possible. I have updated my answer to include query to update or delete activity. Please let me know how it goes.  
  > **username_0**: After running that UPDATE command and then exporting the db, the import worked. Thanks Pradeep.  
  > **username_2**: So can this marked as answered?  
  > **username_0**: Yeah I suppose so. Although I do have a few questions below. Seems I can't mark it answered due to lack of reputation :-\  
  
  ---
  
  ### Answer by username_3 on 2018-06-20. Score: 0
  
  I agree. It's safe to disable them temporarily but if you get errors when trying to enable them there is data missing issue. You might want to run data check on your live data to be sure it's not getting DB error. And how are you exporting? You should be using mysqldump and taking out the definer clauses.
  
  > **Comments:**
  > **username_0**: How do I run a data check? I did the export through phpmyadmin but get the error on mysql import on ssh.  
  
  ---
  
  