Updating MySQL primary key

Question

I have a table user interactions with 4 columns     user 1  user 2  type  timestamp   The primary key is  user 1 user 2 type  and I want to change to  user 2 user 1 type     So what I did was       drop primary key       add primary key  user 2 user 1 type       and voila       The problem is that database is live on a server     So before I could update the primary key  many duplicates already crept in  and they are continuously creeping in     What to do     What I want to do now is to remove duplicates and keep the ones with the latest timestamp  which is a column in the table      And then somehow update the primary key again

User · Answer

You can use the IGNORE keyword too  example    update IGNORE table set primary field    value

User · Answer

Next time  use a single  alter table  statement to update the primary key   alter table xx drop primary key  add primary key k1  k2  k3     To fix things   create table fixit  user 2  user 1  type  timestamp  n  primary key  user 2  user 1  type     lock table fixit write  user interactions u write  user interactions write   insert into fixit  select user 2  user 1  type  max timestamp   count    n from user interactions u  group by user 2  user 1  type having n  gt  1   delete u from user interactions u  fixit  where fixit user 2   u user 2    and fixit user 1   u user 1    and fixit type   u type    and fixit timestamp    u timestamp   alter table user interactions add primary key  user 2  user 1  type     unlock tables    The lock should stop further updates coming in while your are doing this   How long this takes obviously depends on the size of your table   The main problem is if you have some duplicates with the same timestamp

User · Answer

If the primary key happens to be an auto increment value  you have to remove the auto increment  then drop the primary key then re-add the auto-increment  ALTER TABLE  xx  MODIFY  auto increment field  INT   DROP PRIMARY KEY   ADD PRIMARY KEY  new primary key     then add back the auto increment  ALTER TABLE  xx  ADD INDEX  auto increment field   auto increment field   MODIFY  auto increment field  int auto increment    then set auto increment back to previous value  ALTER TABLE  xx  AUTO INCREMENT   5

[mysql] Updating MySQL primary key

Examples related to mysql

Examples related to primary-key