合并两个具有联结表的SQLite数据库 [英] Merging two SQLite databases which both have junction tables
问题描述
我有两个SQLite数据库，它们都有连接表来描述<一>一对多的关系。现在这两个DB需要被合并到一个具有某种导入/导出机制的单一的DB，并仍然保持关系。
我试图用 .dump 转储 DB2 strong> DB1 与 .read ，但始终获得 PRIMARY KEY必须是唯一警告。 >
有没有最佳做法来处理这种情况？
不要使用附加以避免额外的复杂性。
DB1
水果
--------------
| id |名称|
--------------
| 1 |苹果|
| 2 |柠檬|
| 3 | Kiwi |
--------------
果汁
----------------
| id |名称|
----------------
| 1 |果汁A |
| 2 |果汁B |
----------------
配方（接合表）
----------------------------
| id | juice_id | fruit_id |
----------------------------
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
----------------------------
DB2
水果
<
---------------
| id |名称|
---------------
| 1 | Kiwi |
| 2 |柠檬|
| 3 |苹果|
| 4 |橙色|
---------------
果汁
----------------
| id |名称|
----------------
| 1 |果汁C |
| 2 |果汁D |
----------------
配方（接合表）
----------------------------
| id | juice_id | fruit_id |
----------------------------
| 1 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 2 | 2 |
| 4 | 2 | 4 |
----------------------------
如果你不关心重复，你可以从DB1获取最大ID，并将其添加到DB2中的每个ID。
然而，你说 name 可以是唯一的，所以让我们这样做。
假设所有 id 列都是 INTEGER PRIMARY KEY ，即自动增量。
打开DB1并附加DB2：
ATTACH'...'AS db2;
复制DB1中尚不存在的所有水果和果汁（这些获取新ID）：
INSERT INTO水果（名称）
SELECT名称
FROM db2.Fruit
WHERE名称NOT IN（SELECT name
FROM Fruit）;
INSERT INTO Juice（name）
SELECT name
FROM db2.Juice
WHERE name NOT IN（SELECT name
FROM Juice）;
现在复制食谱，同时通过相应的名称查找新的ID值：
INSERT INTO配方（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;
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 | ----------------------------
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;
这篇关于合并两个具有联结表的SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
