title: Sqlite merging databases into one, with unique values, preserving foregin key relation
body: <p>There are two solutions provided (see links at the bottom), but both fail in what i'm trying to do:</p>
  
  <h2>1. Given tables (identical) structure in databases like for example this:</h2>
  
  <h3>DB1</h3>
  
  Fruit
  
  <pre><code>--------------
  | id | name  |
  --------------
  | 1  | Apple |
  | 2  | Lemon |
  | 3  | Kiwi  |
  | 4  | Banana|
  --------------
  </code></pre>
  
  Juice
  
  <pre><code>----------------
  | id | name    |
  ----------------
  | 1  | Juice A |
  | 2  | Juice B |
  ----------------
  </code></pre>
  
  Recipe (Junction Table)
  
  <pre><code>----------------------------
  | id | juice_id | fruit_id |
  ----------------------------
  | 1  | 1        | 1        |
  | 2  | 1        | 2        |
  | 3  | 2        | 1        |
  | 4  | 2        | 3        |
  ----------------------------
  </code></pre>
  
  <h3>DB2</h3>
  
  Fruit
  
  <pre><code>---------------
  | id | name   |
  ---------------
  | 1  | Kiwi   |
  | 2  | Lemon  |
  | 3  | Apple  |
  | 4  | Orange |
  | 5  | Lime   |
  ---------------
  </code></pre>
  
  Juice
  
  <pre><code>----------------
  | id | name    |
  ----------------
  | 1  | Juice C |
  | 2  | Juice D |
  ----------------
  </code></pre>
  
  Recipe (Junction Table)
  
  <pre><code>----------------------------
  | id | juice_id | fruit_id |
  ----------------------------
  | 1  | 1        | 1        |
  | 2  | 1        | 3        |
  | 3  | 2        | 2        |
  | 4  | 2        | 4        |
  ----------------------------
  </code></pre>
  
  <h2>2. convert them into this:</h2>
  
  <h3>DB3</h3>
  
  Fruit
  
  <pre><code>|----------------|
  | id | name      |
  |----------------|
  | 1  | Kiwi      |
  | 2  | Lemon     |
  | 3  | Apple     |
  | 4  | Orange    |
  | 5  | Lime      |
  | 6  | Banana    |
  ------------------
  </code></pre>
  
  Juice
  
  <pre><code>|--------------|
  | id | name    |
  |--------------|
  | 1  | Juice A |
  | 2  | Juice B |
  | 3  | Juice C |
  | 4  | Juice D |
  ----------------
  </code></pre>
  
  Recipe (Junction Table)
  
  <pre><code>----------------------------
  | id | juice_id | fruit_id |
  ----------------------------
  | 1  | 1        | 3        |
  | 2  | 1        | 2        |
  | 3  | 2        | 3        |
  | 4  | 2        | 1        |
  ++++++++++++++++++++++++++++
  | 5  | 3        | 1        |
  | 6  | 3        | 3        |
  | 7  | 4        | 2        |
  | 8  | 4        | 4        |
  ----------------------------
  </code></pre>
  
  <p>Please notice that fruit id's changed accordingly, preserving relations despite change in fruit ID. ID is primary integer autoincrementing key, and Recipe contains two foregin keys (plus own  primary integer autoincrementing key).</p>
  
  <h2>3. only two proposed solutions are:</h2>
  
  <p>a)</p>
  
  <pre><code>$sqlite3 database1.db '.dump' &gt;&gt; tmp
  $sqlite3 database2.db '.dump' &gt;&gt; tmp
  $sqlite3 database3.db '.import tmp'
  $ #sometimes sqlite3 database3.db &lt; 'tmp' instead of last row
  </code></pre>
  
  <p>from <a href="https://stackoverflow.com/questions/8264569/merging-sqlite-databases-is-driving-me-mad-help">4</a>, and simmilar questions, and:</p>
  
  <p>b)</p>
  
  <pre><code>$ sqlite3 newdb
  attach 'b.db' as toMerge;   
  BEGIN; 
  insert into newdb select * from toMerge.sometable; 
  COMMIT;
  </code></pre>
  
  <p>Other answers ie. <a href="https://stackoverflow.com/a/9057584/3264463">1</a>, <a href="https://stackoverflow.com/questions/9048711/merging-databases">2</a>, <a href="https://stackoverflow.com/questions/80801/how-can-i-merge-many-sqlite-databases">3</a>, <a href="https://stackoverflow.com/questions/8264569/merging-sqlite-databases-is-driving-me-mad-help">4</a>, <a href="https://stackoverflow.com/questions/3232900/how-to-merge-n-sqlite-database-files-into-one-if-db-has-the-primary-field">5</a>, <a href="https://stackoverflow.com/questions/13572343/how-to-select-distinct-records-from-two-tables-combine-into-one-column-and-eli">6</a>, <a href="https://stackoverflow.com/questions/9349659/fastest-way-merge-two-sqlite-databases">7</a> just repeat same pattern. Perhaps <a href="https://stackoverflow.com/questions/13572343/how-to-select-distinct-records-from-two-tables-combine-into-one-column-and-eli">6</a> might be solution but i couldnt tell, the lenght of that query frightened me...</p>
  
  <p>Moreover, as <a href="https://stackoverflow.com/a/9057584/3264463">1</a> states there is no gui compare &amp; merge solution. At least besides 2011 sqlitecompare, which probably works in Windows anyway (i'm looking for Linux tool)
  Some opinions in <a href="https://stackoverflow.com/questions/835069/which-sqlite-administration-console-do-you-recommend">8</a> also confirm that hardly any userfriendly gui has merge option.</p>
  
  <p>So, question is. 
  <em>How to merge sqlite DB in Linux, keeping relations, but not keeping duplicate values from tags / notes?</em> I'd thought that keeping organized structure is the main reason for keeping things in database, but it seems I fail to understand, why should i keep two same fruits in one basket if combining from two baskets. My basket should contain unique fruits and recipies, I want to eat that kiwi :)</p>