CODEWITHSUNDEEP
mysqlsqlforeign-keys
Luthando Ntsekwa
Luthando Ntsekwa

Reputation: 4218

MYSQL Update all foreign key values

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

Upvotes: 7

Views: 16076

Answers (4)

Mojtaba Rezaeian
Mojtaba Rezaeian

Reputation: 8736

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.

Upvotes: 4

Susilo
Susilo

Reputation: 866

If both db are identical, I believe you should name it db_B.Table_A not db_B.Table_B to avoid confusion..but for now I go along with it

--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 may add big leap on id at db_B.Table_B if db_A.Table_A have much more data that db_B.Table_B..that can be fixed easily before/after merge table..but I think its optional..

Upvotes: 2

Alexander Khomenko
Alexander Khomenko

Reputation: 597

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

Upvotes: 3

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31143

A simple way would be to update the TableB's IDs to unique range and then do the merge. If your foreign keys are properly set to cascade the change, your database will stay consistent through this operation.

You don't need to do any changes to the database schema this way, so there is no point in time when the data is not calid. You can also be sure that the IDs won't clash. Easiest way to find unique values is to take the maximum of the ID in TableA and add that to the IDs in TableB.

Upvotes: 1

Related Questions