Sqlite将具有唯一值的数据库合并到一个数据库中，从而保留前向键关系

Question

提供了两种解决方案（请参阅底部的链接），但是两种解决方案都无法满足我的要求：

1.数据库中的给定表（相同）结构如下：

DB1

水果

--------------
| id | name  |
--------------
| 1  | Apple |
| 2  | Lemon |
| 3  | Kiwi  |
| 4  | Banana|
--------------

Juice

----------------
| id | name    |
----------------
| 1  | Juice A |
| 2  | Juice B |
----------------

配方（连接表）

----------------------------
| id | juice_id | fruit_id |
----------------------------
| 1  | 1        | 1        |
| 2  | 1        | 2        |
| 3  | 2        | 1        |
| 4  | 2        | 3        |
----------------------------

DB2

水果

---------------
| id | name   |
---------------
| 1  | Kiwi   |
| 2  | Lemon  |
| 3  | Apple  |
| 4  | Orange |
| 5  | Lime   |
---------------

果汁

----------------
| id | name    |
----------------
| 1  | Juice C |
| 2  | Juice D |
----------------

配方（连接表）

----------------------------
| id | juice_id | fruit_id |
----------------------------
| 1  | 1        | 1        |
| 2  | 1        | 3        |
| 3  | 2        | 2        |
| 4  | 2        | 4        |
----------------------------

2.将它们转换为：

DB3

水果

|----------------|
| id | name      |
|----------------|
| 1  | Kiwi      |
| 2  | Lemon     |
| 3  | Apple     |
| 4  | Orange    |
| 5  | Lime      |
| 6  | Banana    |
------------------

果汁

|--------------|
| id | name    |
|--------------|
| 1  | Juice A |
| 2  | Juice B |
| 3  | Juice C |
| 4  | Juice D |
----------------

配方（连接表）

----------------------------
| 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        |
----------------------------

请注意，即使水果ID有所更改，水果ID也会相应更改，从而保持关系。ID是主要整数自动递增密钥，并且Recipe包含两个foregin密钥（加上自己的主要整数自动递增密钥）。

3.只有两个建议的解决方案是：

一种）

$sqlite3 database1.db '.dump' >> tmp
$sqlite3 database2.db '.dump' >> tmp
$sqlite3 database3.db '.import tmp'
$ #sometimes sqlite3 database3.db < 'tmp' instead of last row

来自类似的问题，以及：

b）

$ sqlite3 newdb
attach 'b.db' as toMerge;   
BEGIN; 
insert into newdb select * from toMerge.sometable; 
COMMIT;

所以，问题是。 如何在Linux中合​​并sqlite DB，保持关系，但不保留标签/注释中的重复值？
我以为保持组织结构是将事物保留在数据库中的主要原因，但似乎我不明白，如果将两个篮子合并在一起，为什么还要在一个篮子中保留两个相同的水果。我的篮子里应该装满独特的水果和食谱，我想吃那只奇异果:)

Answer 1

PRAGMA foreign_keys = on;

ATTACH DATABASE 'db1.sqlite' AS db1;

ATTACH DATABASE 'db2.sqlite' AS db2;

BEGIN;

CREATE TABLE Fruit      (
                          id            INTEGER PRIMARY KEY NOT NULL,
                          name          TEXT    UNIQUE ON CONFLICT IGNORE
                          )
                          ;

CREATE TABLE Juice      (
                          id            INTEGER PRIMARY KEY NOT NULL,
                          name          TEXT    UNIQUE ON CONFLICT IGNORE
                        )
                        ;

CREATE TABLE Recipe     (
                          id            INTEGER PRIMARY KEY NOT NULL,
                          juice_id      INTEGER NOT NULL,
                          fruit_id      INTEGER NOT NULL,
                          FOREIGN KEY   ( juice_id ) REFERENCES Juice ( id )
                                        ON UPDATE CASCADE
                                        ON DELETE CASCADE,
                          FOREIGN KEY   ( fruit_id ) REFERENCES Fruit ( id )
                                        ON UPDATE CASCADE
                                        ON DELETE CASCADE
                        )
                        ;


INSERT INTO Fruit  ( id, name )               SELECT id, name FROM db1.Fruit;
INSERT INTO Juice  ( id, name )               SELECT id, name FROM db1.Juice;
INSERT INTO Recipe ( id, juice_id, fruit_id ) SELECT id, juice_id, fruit_id FROM db1.Recipe;

INSERT INTO Fruit ( name ) SELECT name FROM db2.Fruit;
INSERT INTO Juice ( name ) SELECT name FROM db2.Juice;

CREATE TEMPORARY TABLE Recipe_tmp AS
                                    SELECT Juice.name AS j_name, Fruit.name AS f_name
                                      FROM db2.Recipe, db2.Fruit, db2.Juice
                                        WHERE db2.Recipe.juice_id = db2.Juice.id AND db2.Recipe.fruit_id = db2.Fruit.id
;

INSERT INTO Recipe ( juice_id, fruit_id ) SELECT j.id, f.id
                                            FROM Recipe_tmp AS r, Juice AS j, Fruit AS f
                                              WHERE r.j_name = j.name AND r.f_name = f.name
;


DROP TABLE Recipe_tmp;

COMMIT;

DETACH DATABASE db1;
DETACH DATABASE db2;