title: Mysql primary key type change. Integer unsigned to Binary(16)
body: <p>I'm facing problems with Primary Key(PK) on my <em>Android</em> x <em>Web Server</em> system, given that the user can create new Objects on the client-side and Off-line.
  These objects will be sent to server when the user decides to.</p>
  
  <p>This synchronization caused lots of Foreign Key(FK) and PK issues, because my database uses Auto-increment PK.</p>
  
  <p>To solve these integrity issues, after some search, I've decided that the simplest way was to use UUID as my PK in both Android Client and Java Server.</p>
  
  <p><a href="https://stackoverflow.com/a/10951183/2865036">David Bélanger's answer</a> about UUID and index helped a lot.</p>
  
  <p>Now I have 1 question and 1 problem:</p>
  
  <ul>
  <li><p>Question: Is this an acceptable solution to my problem?. If its not.. could give me an alternative? (Realtime database seems to be a hard work to do with my system)</p></li>
  <li><p>Problem: Given that I already have a production database and I can't start over, how can I change the PK from integer unsigned to binary(16).</p></li>
  </ul>
  
  <p>My final select statement needs to be like that this:</p>
  
  <ul>
  <li>select id from table where id = hex('any ID')</li>
  </ul>
  
  <p>Because I'll start inserting data like this:</p>
  
  <ul>
  <li>insert into table(id) values (unhex(replace(uuid(), '-', '')));</li>
  </ul>
  
  <p>I tried update id using
   * unhex(id)
   * conv(id, 10, 16)</p>
  
  <p>What am I missing?</p>
  
  <blockquote>
    <p>EDIT:
    Using thease scripts, I was able to recreate all IDs and Foreign keys</p>
  </blockquote>
  
  <pre><code>select concat ('alter table ', TABLE_NAME, ' drop foreign key ', CONSTRAINT_NAME, ';') from information_schema.KEY_COLUMN_USAGE where table_schema = 'my_table_name' and CONSTRAINT_NAME like 'fk%'
  select concat('alter table ', TABLE_NAME, ' modify ', COLUMN_NAME, ' binary(16) ', IF (IS_NULLABLE = 'NO', 'not null', ''), ';') from information_schema.columns where TABLE_SCHEMA = 'my_table_name' and COLUMN_KEY in ('PRI', 'MUL') and COLUMN_NAME like 'id%'
  select concat ('alter table ', TABLE_NAME, ' add constraint ', CONSTRAINT_NAME, ' foreign key (', COLUMN_NAME, ') references ', REFERENCED_TABLE_NAME, ' (', REFERENCED_COLUMN_NAME, ') on update cascade;') from information_schema.KEY_COLUMN_USAGE where table_schema = 'my_table_name' and CONSTRAINT_NAME like 'fk%'
  select concat('update ', TABLE_NAME, ' set ', COLUMN_NAME, ' = unhex(replace(uuid(), ''-'', ''''));') from information_schema.COLUMNS where TABLE_SCHEMA = 'my_table_name' and md5(COLUMN_NAME) = md5('id')
  select concat ('alter table ', TABLE_NAME, ' drop foreign key ', CONSTRAINT_NAME, ';') from information_schema.KEY_COLUMN_USAGE where table_schema = 'my_table_name' and CONSTRAINT_NAME like 'fk%'
  select concat ('alter table ', TABLE_NAME, ' add constraint ', CONSTRAINT_NAME, ' foreign key (', COLUMN_NAME, ') references ', REFERENCED_TABLE_NAME, ' (', REFERENCED_COLUMN_NAME, ');') from information_schema.KEY_COLUMN_USAGE where table_schema = 'my_table_name' and CONSTRAINT_NAME like 'fk%'
  </code></pre>
  
  <blockquote>
    <p>Problem solved.</p>
  </blockquote>