MYSQL Update all foreign key values

Question

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

these tables look like this:

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 that are linked 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's, this means when I do the merge, I have to change the id's of the second table. Remember the second table's primary keys are used in other tables.

I wrote this query to merge the 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 that are linked with Table B, I can use the same query, but now the foreign key will not match, because the ID they were linked with has been changed.

How do I update them so that the ID will match again?

NB: I do not have the ON UPDATE CASCADE constraint on those tables

I hope this make sense, I will try to improve this question so that everyone understands it.

Database Info

Type  : MySQL
Engine: MyISAM

Answer 1

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

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 afterwards use the trick in Sami's Answer and then remove temporary changes like this:

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

---

For MyISM or situations that CASCADE is not supported by engine you can simulate it manually by defining 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 id number of rows in second database by some custom amount(any amount greater than max row id which used in first database) in all tables including foreign key parent table at a 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 merge those databases.

Answer 2

my suggestion were:

1. you drop the foreign key constraint of LinkA in database1
2. increase the foreign key of the TableA:id AND LinkA:tableA_ID (the best way were with a join) by lets say 1000 (or how much rows you have in database2)
3. add the constraint again (optional)
4. import TableA and then LinkA to database2 from database1.

If you need more help, just ask.

Best Regards

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

Update. Example for the update of the ids:

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