MYSQL Update all foreign key values

Question

I have two identical tables that are located in two identical databases (with a different name). I want to combine these two tables, but their primary keys are used in other tables,

these tables are as follows:

Table a

id column1 column2 column3 ___ ________ _______ ________ 1 text text text 2 text text text 3 text text text 

Table B

 id column1 column2 column3 ___ ________ _______ ________ 2 text text text 3 text text text 4 text text text 

tables related to table A

Link A

 id column1 tableA_ID ___ ________ _______ 1 text 2 2 text 3 3 text 4 

Link B

 id column1 tableA_ID ___ ________ _______ 1 text 3 2 text 3 3 text 2 

Please note: the tables have identical id , this means that when I do the merge, I need to change the id second table. Remember that the primary keys of the second table are used in other tables.

I wrote this query to combine two tables:

 INSERT INTO db_A.`Table_A`(`column2`,`column3`) SELECT `column2`,`column3` FROM db_B.`Table_B`; 

This query will correctly copy the records of the second table to the first table.

Now I want to also move the data of the tables associated with Table B , I can use the same query, but now the foreign key will not match, since the id associated with it has been changed.

How to update them so that id again?

NB: I have no ON UPDATE CASCADE restrictions for these tables

I hope this makes sense, I will try to improve this issue so that everyone understands it.

Database Information

 Type : MySQL Engine: MyISAM 

Answer 1

An easy way would be to update the TableB ID to a unique range and then merge. If your foreign keys are correctly configured to cascade, your database will remain consistent in this operation.

You do not need to make any changes to the database schema this way, so there is no time when the data is not calibrated. You can also be sure that identifiers will not conflict. The easiest way to find unique values ​​is to take the maximum ID in TableA and add it to the ID in TableB .

Answer 2

my suggestion was:

• you remove LinkA foreign key constraint in database
• increase the foreign key of the table A: id AND LinkA: tableA_ID (the best way was with the join), say 1000 (or how many rows do you have in the database2)
• add restriction again (optional)
• import TableA and then LinkA into database2 from database1.

If you need more help, just ask.

Best wishes

======================================

Update Identity Update Example:

 UPDATE Table_A, Link_A SET Table_A.id = Table_A.id + 1000, Link_A.id = Link_A.tableA_ID + 1000, FROM Table_A JOIN Link_A ON Table_A.id = Link_A.tableA_ID 

Answer 3

If both db are the same, I believe you should call it db_B.Table_A not db_B.Table_B to avoid confusion ... but now I agree with him

 --get delta id, use biggest id from db_A and db_B --to avoid failure because of updating to existing primary key SELECT @dbBMax := MAX(id) FROM db_B.`Table_B`; SELECT @dbAMin := MIN(id), @dbAMax := MAX(id) FROM db_A.`Table_A`; SET @DeltaID := IF(@dbBMax > @dbAMax, @dbBMax, @dbAMax) - @dbAMin + 1; --drop constraint ALTER TABLE db_A.`Link_A` DROP FOREIGN KEY `constraint_name_A`; ALTER TABLE db_A.`Link_B` DROP FOREIGN KEY `constraint_name_B`; --update ids UPDATE db_A.`Table_A` SET id = id + @DeltaID; UPDATE db_A.`Link_A` SET tableA_ID = tableA_ID + @DeltaID; UPDATE db_A.`Link_B` SET tableA_ID = Link_A.tableA_ID + @DeltaID; --merge tables --assume id is auto-increment, don't use auto-increment value, --so id manually inserted INSERT INTO db_A.`Table_A`(`id`, `column1`, `column2`,`column3`) SELECT `id`, `column1`, `column2`,`column3` FROM db_B.`Table_B`; --assume id is auto-increment, use it, don't insert manually INSERT INTO db_A.`Link_A`(`column1`, `tableA_ID`) SELECT `column1`, `tableA_ID` FROM db_B.`Link_A`; --assume id is auto-increment, use it, don't insert manually INSERT INTO db_A.`Link_B`(`column1`, `tableA_ID`) SELECT `column1`, `tableA_ID` FROM db_B.`Link_B`; 

This code can add a big jump to id in db_B.Table_B if db_A.Table_A has much more data, which db_B.Table_B .., which can be easily fixed before / after the merge table. but I think this is optional ..

Answer 4

You can apply ON UPDATE CASCADE to each table with foreign keys associated with TableB.id in a temporary temporary database:

 ALTER TABLE db2.other_tables_with_fk DROP FOREIGN KEY fk_to_TableB; ALTER TABLE db2.other_tables_with_fk ADD CONSTRAINT fk_to_TableB FOREIGN KEY (TableB_id) REFERENCES TableB(id) ON UPDATE CASCADE; 

and then use the Sami Answer trick and then remove the temporary changes as follows:

 ALTER TABLE db2.other_tables_with_fk DROP FOREIGN KEY fk_to_TableB; ALTER TABLE db2.other_tables_with_fk ADD CONSTRAINT fk_to_TableB FOREIGN KEY (TableB_id) REFERENCES TableB(id); 

Then your second database will be ready to merge with the first.

---

For MyISM or situations that CASCADE not supported by the engine, you can simulate it manually by specifying Triggers :

 CREATE TRIGGER trigger1 AFTER UPDATE ON TableB FOR EACH ROW BEGIN UPDATE other_tables_with_fk1 SET TableB_id = NEW.id WHERE TableB_id = OLD.id UPDATE other_tables_with_fk2 SET TableB_id = NEW.id WHERE TableB_id = OLD.id ... END 

---

Even if triggers are not available, you can simply increase the number of row identifiers in the second database using some user sum (any amount exceeding the maximum row identifier used in the first database) in all tables, including the parent foreign key table in one and the same same time

 UPDATE TableB t SET t.id = (t.id + 10000); UPDATE related_table_1 t SET t.TableB_id = (t.TableB_id + 10000); UPDATE related_table_2 t SET t.TableB_id = (t.TableB_id + 10000); ... 

And then you can combine these databases.