title: mysql load from dump: ERROR 1452 (23000) at line 13: Cannot add or update a child row: a foreign key constraint fails
body: <p>I dumped a mysql database using mysqldump with the following command:</p>
  
  <pre><code>mysqldump --no-create-db --no-create-info --compact --single-transaction --complete-insert --extended-insert=FALSE --flush-logs -uroot -ppassword databasename &gt; databasename.db.data.dump.sql
  </code></pre>
  
  <p>The reason why I used the particular options above was that I needed to:</p>
  
  <ol>
  <li>Dump ONLY the data</li>
  <li>Not create any DDL statements like CREATE TABLE etc</li>
  </ol>
  
  <p>I have a script that generates a CLEAN database (i.e. a database with the data structures - tables, indices etc, but NO data).</p>
  
  <p>I want to import the data dumped by using the command above, into the CLEAN database.</p>
  
  <p>I attempt to import the dumped data (i.e. restore the data) into the CLEAN database by typing the following command:</p>
  
  <pre><code>mysql -h hostname -u user --password=password databasename &lt; filename
  </code></pre>
  
  <p>When I run this command, I get the following error:</p>
  
  <pre><code>ERROR 1452 (23000) at line 13: Cannot add or update a child row: a foreign key constraint fails (`tpwsdb`.`sf_guard_group_permission`, CONSTRAINT `sf_guard_group_permission_FK_2` FOREIGN KEY (`permission_id`) REFERENCES `sf_guard_permission` (`id`) ON DELETE CASCADE)
  </code></pre>
  
  <p>Line 13 in the dumped data file is:</p>
  
  <pre><code>INSERT INTO `sf_guard_group_permission` (`group_id`, `permission_id`) VALUES (1,1);
  </code></pre>
  
  <p>the schema for sf_guard_group_permission is:</p>
  
  <pre><code>mysql&gt; describe sf_guard_group_permission;
  +---------------+---------+------+-----+---------+-------+
  | Field         | Type    | Null | Key | Default | Extra |
  +---------------+---------+------+-----+---------+-------+
  | group_id      | int(11) | NO   | PRI | NULL    |       |
  | permission_id | int(11) | NO   | PRI | NULL    |       |
  +---------------+---------+------+-----+---------+-------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>Permission_id is an FK into a table sf_guard_permission. From the error, it seems that mysql is attempting to populate a table with dependencies (a child table so to speak), BEFORE populating the parent table. </p>
  
  <p>Upon checking the dumped file contents, I can see that indeed, the INSERT statements for the dependent (child) table PRECEDE those for the parent table - which obviously violates the Referential Integrity constraints on the table.</p>
  
  <p>This error then seems to be caused by the order in which the INSERT statements are created by mysqldump, which surprisingly, seems to pay no heed to R.I.</p>
  
  <p>Is there anyway that I can force the ORDER in which mysqldump creates INSERT statements - i.e. so that tables that are FKs in other tables, appear before the tables that reference them?</p>
  
  <p>I would have manually changed the order the INSERT statements appear, but the file contains almost 10k lines, and I don't relish the prospect of doing that.</p>
  
  <p>Someone must have come across this issue before whilst using mysqldump - what is the way to solve this issue?</p>