Answers.Body: <p>I managed to resolve this dilemma.</p>
  
  <p>The goal was to move a 2 database Drupal/CiviCRM website into the CiviCRM Starterkit platform which requires that Drupal and CiviCRM use a single database. I was not able to import my database dump into the existing Drupal website because although I had set foreign_key check to zero, I was receiving a "Cannot add foreign key constraint" error.   </p>
  
  <p>My solution was as follows:</p>
  
  <ol>
  <li><p>Download a dump of the CiviCRM database with ignore foreign keys, drop tables, triggers etc. </p></li>
  <li><p>The dump does not include tables for: civicrm_acl_cache, civicrm_acl_contact_cache, civicrm_cache, civicrm_domain, civicrm_group_contact_cache</p></li>
  <li><p>Remove the drop tables statements before importing. Leave in: IF NOT EXISTS create table.</p></li>
  <li><p>It was important to Truncate tables before each table's data insert statement.</p></li>
  <li><p>When I ran the import shell command the import would stop because the Log Tables didn't exit. They are in the Dump but they were not being created so I ran all of the IF NOT EXISTS create table commands before running the import command. </p></li>
  </ol>
  
  <p>Once I had accomplished the above 5 items, I was able to run the import and populate the tables. All is well.</p>
  
  <p>I hope this helps anyone else who runs into this challenge.  </p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-05-04T01:04:04.430Z
Answers.LastActivityDate: 2019-05-04T01:04:04.430Z
Answers.UserDisplayName: Andrew Wasson
Answers.Body: <p>'Cannot add foreign key constraint' means that the data referencing a column is missing. You can either make sure that you delete orphan data or set null before exporting them or you can <code>SET FOREIGN_KEY_CHECKS=0;</code> before importing and then <code>SET FOREIGN_KEY_CHECKS=1;</code> once imported.</p>
  
  <p>Cheers</p>
  
  <p>Pradeep</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-04-15T22:12:19.683Z
Answers.Text: Hi Pradeep. Yes,  FOREIGN_KEY_CHECKS=0; is set on my SQL import file but it does not appear to be recognized when I try to import. I'm not sure why that is.
Answers.UserDisplayName: Andrew Wasson
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-04-15T21:32:34.647Z
Answers.LastActivityDate: 2019-04-15T21:32:34.647Z
Answers.UserDisplayName: Pradeep Nayak
Body: <p>I am replicating a Drupal 7 CiviCRM installation into a Pantheon CiviCRM Kickstart site. I have gone through the tedious effort of moving the Drupal content and settings from a default Drupal profile into the CiviCRM Kickstart profile and I have a happy, empty installation of CiviCRM living beside my Drupal site but now I need to move my CiviCRM data into the consolidated database. </p>
  
  <p>I have tried unsuccessfully to export the database with SET FOREIGN_KEY_CHECKS=0; and without the cache and domain tables but on my import I am receiving a  "Cannot add foreign key constraint" error.</p>
  
  <p>Has anyone come up with a cookbook for making this migration?</p>
  
  <p>Thanks.</p>
  
Tags: <drupal><database><migration><data-import>
Title: Is there a best practice or recipe for migrating a two database CiviCRM installation into one database?
ThreadText: # Is there a best practice or recipe for migrating a two database CiviCRM installation into one database?
  
  (Asked by: username_0 on 2019-04-15)
  
  I am replicating a Drupal 7 CiviCRM installation into a Pantheon CiviCRM Kickstart site. I have gone through the tedious effort of moving the Drupal content and settings from a default Drupal profile into the CiviCRM Kickstart profile and I have a happy, empty installation of CiviCRM living beside my Drupal site but now I need to move my CiviCRM data into the consolidated database.
  
  I have tried unsuccessfully to export the database with SET FOREIGN\_KEY\_CHECKS=0; and without the cache and domain tables but on my import I am receiving a "Cannot add foreign key constraint" error.
  
  Has anyone come up with a cookbook for making this migration?
  
  Thanks.
  
  **Tags:** drupal, database, migration, data-import
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_0 on 2019-05-04. Score: 1
  
  I managed to resolve this dilemma.
  
  The goal was to move a 2 database Drupal/CiviCRM website into the CiviCRM Starterkit platform which requires that Drupal and CiviCRM use a single database. I was not able to import my database dump into the existing Drupal website because although I had set foreign\_key check to zero, I was receiving a "Cannot add foreign key constraint" error.
  
  My solution was as follows:
  
  1. Download a dump of the CiviCRM database with ignore foreign keys, drop tables, triggers etc.
  2. The dump does not include tables for: civicrm\_acl\_cache, civicrm\_acl\_contact\_cache, civicrm\_cache, civicrm\_domain, civicrm\_group\_contact\_cache
  3. Remove the drop tables statements before importing. Leave in: IF NOT EXISTS create table.
  4. It was important to Truncate tables before each table's data insert statement.
  5. When I ran the import shell command the import would stop because the Log Tables didn't exit. They are in the Dump but they were not being created so I ran all of the IF NOT EXISTS create table commands before running the import command.
  
  Once I had accomplished the above 5 items, I was able to run the import and populate the tables. All is well.
  
  I hope this helps anyone else who runs into this challenge.
  
  
  ---
  
  ### Answer by username_1 on 2019-04-15. Score: 0
  
  'Cannot add foreign key constraint' means that the data referencing a column is missing. You can either make sure that you delete orphan data or set null before exporting them or you can `SET FOREIGN_KEY_CHECKS=0;` before importing and then `SET FOREIGN_KEY_CHECKS=1;` once imported.
  
  Cheers
  
  username_1
  
  > **Comments:**
  > **username_0**: Hi username_1. Yes,  FOREIGN_KEY_CHECKS=0; is set on my SQL import file but it does not appear to be recognized when I try to import. I'm not sure why that is.  
  
  ---
  
  