Merging two SQLite databases which both have junct

问题:

I got two SQLite databases which both have junction tables to describe the one-to-many relationship. Now these two DB need to be merged into a single one with some kind of import/export mechanism and still keep the relationships.

I tried to dump DB2 with .dump and then load it back in DB1 with .read, but always got PRIMARY KEY must be unique warnings.

Is there any best practice to handle this kind of situation?

Preferred not to use attach to avoid extra complexity.

---

DB1

Fruit

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

Juice

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

Recipe (Junction Table)

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

---

DB2

Fruit

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

Juice

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

Recipe (Junction Table)

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

回答1:

If you don't care about duplicates, you could get the maximum ID from DB1, and add it to every ID in DB2. However, you said that name could be unique, so let's do this right.

I'm assuming that all id columns are INTEGER PRIMARY KEY, i.e., autoincrementing.

Open DB1, and attach DB2:

ATTACH '...' AS db2;

Copy over all fruits and juices that do not yet exist in DB1 (these get new IDs):

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

Now copy over the recipes, while looking up the new ID values through the corresponding name:

INSERT INTO Recipe(juice_id, fruit_id)
SELECT (SELECT id
        FROM Juice
        WHERE name = (SELECT name
                      FROM db2.Juice
                      WHERE id = Recipe2.juice_id)),
       (SELECT id
        FROM Fruit
        WHERE name = (SELECT name
                      FROM db2.Fruit
                      WHERE id = Recipe2.fruit_id))
FROM db2.Recipe AS Recipe2;