0

I have three tables in MySQL (innodb) (X, Y and Z). X is a table with more than 10 million rows, and has primary key of Y as foreign key. Similarly, Z is table with more than 30 million rows and has primary key of Y as foreign key.

Now the problem is that primary key of Y is VARCHAR (something like a md5 hash or GUID). I want to move this key to INT (AUTO_INCREMENT). What is a way to achieve this in mysql, without writing a script in any other language?

Additionally, primary key of table Z is also a VARCHAR (md5/GUID). I would like to change that to integer as well. (It's not a foreign key in any table).

Tim
  • 2,123
  • 4
  • 27
  • 44
G.D. Singh
  • 173
  • 1
  • 18

2 Answers2

3

(This may or may not be any better than Ritobroto's suggestion.)

Assuming X links to Y. (Adapt as needed for all the FKs.)

  1. Do something like this for each table.

    ALTER TABLE X
DROP FOREIGN KEY ...,    -- We'll add it back later
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,  -- Replacement PK
DROP PRIMARY KEY,        -- Assuming it was `guid`
ADD PRIMARY KEY(id),
ADD INDEX(X_guid),       -- Lost the FK; still (for now) need an index
ADD COLUMN Y_id INT UNSIGNED NOT NULL  -- future FK to Y
;

  2. Get the new ids linked up (to replace the guids). For each link:

    UPDATE X JOIN Y ON X.Y_guid = Y.guid
SET x.y_id = y.id;

    (This will take a long time.

  3. Re-establish the FKs. For each table:

    ALTER TABLE ...
ADD FOREIGN KEY ...,  -- to tie `id` instead of `guid`
DROP INDEX(X_guid);   -- unless you need it for something else

Practice it on a test machine !!

The Impaler
  • 45,731
  • 9
  • 39
  • 76
Rick James
  • 135,179
  • 13
  • 127
  • 222
0

Step 1
Create tables with same column names but the datatype of primary key of Y should be in INT AUTO INCREMENT,Same goes for table Z.

Step 2
Use this query: 

INSERT INTO table_name (column_names of present table except the primary key column_name) SELECT all the columns except the primary key column FROM Y/Z(which ever table you want from the data to be inserted).

Then you can drop the original table.
It's a painful process for such amount of data,but will do what you have wanted.

Ritobroto Mukherjee
  • 111
  • 1
  • 13