title: How do I assign foreign keys to a MySQL table from a csv file?
body: <p>I am trying to set up a database for the first time using MySQL Workbench. I have 3 csv files: <code>parent.csv</code>, <code>child.csv</code>, <code>parent_to_child.csv</code>. The <code>parent.csv</code> and <code>child.csv</code> files simply contain 1 column with many rows (50,000+) of unique string identifiers (alphanumeric).</p>
  
  <p>parent.csv</p>
  
  <pre><code>parent_id
  parent001
  parent002
  parent003
  parent004
  ...
  </code></pre>
  
  <p>child.csv</p>
  
  <pre><code>child_id
  child001
  child002
  child003
  child004
  ...
  </code></pre>
  
  <p>So far, I managed to create <code>parent</code> and <code>child</code> tables with a surrogate auto-increment primary key (integer).</p>
  
  <p><code>parent</code> table</p>
  
  <pre><code>id_num parent_id
  1      parent001
  2      parent002
  3      parent003
  4      parent004
  ...    ...
  </code></pre>
  
  <p><code>child</code> table</p>
  
  <pre><code>id_num child_id
  1      child001
  2      child002
  3      child003
  4      child004
  5      child005
  ...    ...
  </code></pre>
  
  <p>I would now like to have each <code>child</code> in the table to reference its <code>parent</code>. The <code>child</code> to <code>parent</code> mappings are stored in <code>parent_to_child.csv</code>.</p>
  
  <p>parent_to_child.csv</p>
  
  <pre><code>parent_id, child_id
  parent001, child004
  parent001, child003
  parent002, child005
  parent003, child001
  parent003, child002
  ...      , ...
  </code></pre>
  
  <p>so the desired result would be something like:</p>
  
  <p><code>child</code> table where <code>id_num_parent</code> is a foreign key referencing the <code>parent</code> table</p>
  
  <pre><code>id_num child_id id_num_parent
  1      child001 2
  2      child002 3
  3      child003 1
  4      child004 1
  5      child005 2
  ...    ...      ...
  </code></pre>
  
  <p>So my approach was to alter the <code>child</code> table to include a new <code>id_num_parent</code> which would be a foreign key column referencing the <code>parent</code> table. Then, I can use <code>parent_to_child.csv</code> to assign the foreign keys to each <code>child_id</code>.</p>
  
  <p>This is my attempt that fails:</p>
  
  <pre><code>LOAD DATA LOCAL INFILE 'parent_to_child.csv'
  REPLACE
  INTO TABLE child
  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (@col1, @col2) set child_id=@col2, id_num_parent=@col1;
  </code></pre>
  
  <p>This returns an <code>Incorrect integer value</code> since <code>@col1</code> is the <code>parent_id</code> (string), not the <code>id_num</code> (int) of the parent.</p>
  
  <p>So I believe my main problem is figuring out how to get <code>id_num</code> from <code>parent_id</code> in the <code>parent</code> table and then assigning <code>id_num</code> to <code>id_num_parent</code> for <code>child_id</code> in the <code>child</code> table.</p>