title: Normalizing CSV data with multiple entities
body: <p>I'm importing a CSV into a database that contains multiple entities: <code>City</code>, <code>State</code>, and <code>Country</code>.</p>
  
  <p>I'd like to have a hierarchy of references using foreign keys across the tables:</p>
  
  <ul>
  <li><code>City.State_Ref</code> -> <code>State.ID</code></li>
  <li><code>State.Country_Ref</code> -> <code>Country.ID</code></li>
  </ul>
  
  <p>So the <code>City</code> table will have the following schema in the end:</p>
  
  <pre><code>CREATE TABLE "City" (
    "ID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "Name" INTEGER NOT NULL,
    "State_Ref" INTEGER,
    FOREIGN KEY("State_Ref") REFERENCES "State"("ID")
  )
  </code></pre>
  
  <p>Setting up the <code>State</code> and <code>Country</code> tables is easy enough, but when there is the same <code>State</code> name that occurs in different <code>Country</code> records, I'm unable to pick the correct one:</p>
  
  <pre><code>UPDATE City SET State_Ref = (SELECT ID from State WHERE City.State = State.Name);
  </code></pre>
  
  <p>This will match multiple records and <code>LIMIT</code> just grabs the first one (which isn't always right).</p>
  
  <p>I have also tried <code>JOIN</code> to create a new table with the correct references:</p>
  
  <pre><code>SELECT City.Name as Name, State.ID as State_Ref
  
  FROM City
      INNER JOIN State ON City.State = State.Name
      INNER JOIN Country ON City.Country = Country.Name
  
  ORDER BY City.Name ASC;
  </code></pre>
  
  <p>But this results in multiple records for all of the <code>State</code> duplicates.</p>
  
  <p>Sample data (the problem happens with "Wonderland" since "Colorado" exists in multiple countries):</p>
  
  <pre><code>Country         State         Name
  United States   New York      Kingston  
  United States   Texas         Paris 
  England         Kent          Kingston  
  United States   Maine         Paris 
  France          Île-de-France Paris 
  United States   Colorado      Denver    
  Canada          Colorado      Wonderland
  </code></pre>
  
  <p>Any pointers for setting <code>State_Ref</code> properly would be much appreciated!</p>