Sqlite merging databases into one, with unique values, preserving foregin key relation

Question

There are two solutions provided (see links at the bottom), but both fail in what i'm trying to do: 1. Given tables (identical) structure in databases like for example this: DB1 Fruit ``` -------------- | id | name | -------------- | 1 | Apple | | 2 | Lemon | | 3 | Kiwi | | 4 | Banana| -------------- ``` 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 | | 5 | Lime | --------------- ``` Juice ``` ---------------- | id | name | ---------------- | 1 | Juice C | | 2 | Juice D | ---------------- ``` Recipe (Junction Table) ``` ---------------------------- | id | juice_id | fruit_id | ---------------------------- | 1…

Accepted Answer

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

Sqlite merging databases into one, with unique values, preserving foregin key relation

Solution

Problem

Related problems