Answers.Body: <p>You can add this to the top of your .sql file, then try loading it again.</p>
  
  <pre><code>SET FOREIGN_KEY_CHECKS = 0;
  </code></pre>
  
  <p>This will permit MySQL to load the data while ignoring the foreign key checks.</p>
  
  <p>Since CiviCRM uses foreign key checks, this is a good idea only if the checks throw errors when loading the DB but not when using CiviCRM afterwards.</p>
  
  <p>Alternatively, to ensure foreign key integrity and handle existing tables not wanting to be deleted, prefix your .sql with</p>
  
  <pre><code>DROP DATABASE nameofdb;
  CREATE DATABASE nameofdb;
  </code></pre>
  
  <p>(where <em>nameofdb</em> is your actual DB name of course).</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-15T14:17:28.140Z
Answers.Text: Thanks. Since I had no errors and the existing db was fresh, I disabled the foreign key check and imported just fine. No problems seen since.
Answers.UserDisplayName: GrannyKat
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-20T14:37:11.673Z
Answers.Text: @GrannyKat - if this answer worked for you, please mark it as accepted!
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-15T05:55:51.337Z
Answers.LastActivityDate: 2015-05-15T05:55:51.337Z
Answers.UserDisplayName: Chris Burgess
Answers.Body: <ol>
  <li><p>You might want to ensure that the DB you are importing into is a brand new db (rather than a brand new civi install which will install data into the db)</p></li>
  <li><p>If you cannot do the above, there is a script in sql/civicrm_drop.mysql which you can run on mysql shell to drop all civicrm standard tables. This script will not drop any custom tables created</p></li>
  </ol>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-15T14:17:59.450Z
Answers.Text: Thanks for the heads-up on the script -- could come in handy.
Answers.UserDisplayName: GrannyKat
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-15T04:21:50.670Z
Answers.LastActivityDate: 2015-05-15T04:21:50.670Z
Answers.UserDisplayName: Donald Lobo
Body: <p>I exported all the civi tables from one database and saved as a sql file, including a droptable statement. I then tried to import the data into a fresh site with brand new civi installation.  The import fails with a foreign key constraint failure.
  When that failed I tried to import a sql file with data only, no structure, no drop table. Also failed with </p>
  
  <p>1st SQL query (with structure &amp; data):</p>
  
  <pre><code>-- --------------------------------------------------------
  --
  -- Table structure for table `civicrm_action_mapping`
  --
  DROP TABLE IF EXISTS `civicrm_action_mapping` ;
  
  MySQL said: Documentation
  #1217 - Cannot delete or update a parent row: a foreign key constraint fails 
  </code></pre>
  
  <p>2nd SQL Query (data only)</p>
  
  <pre><code>-- Dumping data for table `civicrm_acl_entity_role`
  --
  INSERT INTO `civicrm_acl_entity_role` ( `id` , `acl_role_id` , `entity_table` , `entity_id` , `is_active` )
  VALUES ( 1, 3, 'civicrm_group', 1, 1 ) , ( 2, 1, 'civicrm_group', 15, 1 ) , ( 3, 4, 'civicrm_group', 2, 1 ) , ( 4, 2, 'civicrm_group', 2, 1 ) ;
  
  MySQL said: Documentation
  #1062 - Duplicate entry '1' for key 'PRIMARY' 
  </code></pre>
  
  <p>Any help is appreciated.
  Kate</p>
  
Tags: <import><sql>
Title: Import from one db to another fails
ThreadText: # Import from one db to another fails
  
  (Asked by: username_0 on 2015-05-15)
  
  I exported all the civi tables from one database and saved as a sql file, including a droptable statement. I then tried to import the data into a fresh site with brand new civi installation. The import fails with a foreign key constraint failure.
  When that failed I tried to import a sql file with data only, no structure, no drop table. Also failed with
  
  1st SQL query (with structure & data):
  
  ```
  -- --------------------------------------------------------
  --
  -- Table structure for table `civicrm_action_mapping`
  --
  DROP TABLE IF EXISTS `civicrm_action_mapping` ;
  
  MySQL said: Documentation
  #1217 - Cannot delete or update a parent row: a foreign key constraint fails 
  
  ```
  
  2nd SQL Query (data only)
  
  ```
  -- Dumping data for table `civicrm_acl_entity_role`
  --
  INSERT INTO `civicrm_acl_entity_role` ( `id` , `acl_role_id` , `entity_table` , `entity_id` , `is_active` )
  VALUES ( 1, 3, 'civicrm_group', 1, 1 ) , ( 2, 1, 'civicrm_group', 15, 1 ) , ( 3, 4, 'civicrm_group', 2, 1 ) , ( 4, 2, 'civicrm_group', 2, 1 ) ;
  
  MySQL said: Documentation
  #1062 - Duplicate entry '1' for key 'PRIMARY' 
  
  ```
  
  Any help is appreciated.
  Kate
  
  **Tags:** import, sql
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2015-05-15. Score: 3
  
  You can add this to the top of your .sql file, then try loading it again.
  
  ```
  SET FOREIGN_KEY_CHECKS = 0;
  
  ```
  
  This will permit MySQL to load the data while ignoring the foreign key checks.
  
  Since CiviCRM uses foreign key checks, this is a good idea only if the checks throw errors when loading the DB but not when using CiviCRM afterwards.
  
  Alternatively, to ensure foreign key integrity and handle existing tables not wanting to be deleted, prefix your .sql with
  
  ```
  DROP DATABASE nameofdb;
  CREATE DATABASE nameofdb;
  
  ```
  
  (where *nameofdb* is your actual DB name of course).
  
  > **Comments:**
  > **username_0**: Thanks. Since I had no errors and the existing db was fresh, I disabled the foreign key check and imported just fine. No problems seen since.  
  > **username_2**: @username_0 - if this answer worked for you, please mark it as accepted!  
  
  ---
  
  ### Answer by username_3 on 2015-05-15. Score: 0
  
  1. You might want to ensure that the DB you are importing into is a brand new db (rather than a brand new civi install which will install data into the db)
  2. If you cannot do the above, there is a script in sql/civicrm\_drop.mysql which you can run on mysql shell to drop all civicrm standard tables. This script will not drop any custom tables created
  
  > **Comments:**
  > **username_0**: Thanks for the heads-up on the script -- could come in handy.  
  
  ---
  
  