I'm facing problems with Primary Key(PK) on my Android x Web Server 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.
This synchronization caused lots of Foreign Key(FK) and PK issues, because my database uses Auto-increment PK.
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.
David Bélanger's answer about UUID and index helped a lot.
Now I have 1 question and 1 problem:
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)
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).
My final select statement needs to be like that this:
- select id from table where id = hex('any ID')
Because I'll start inserting data like this:
- insert into table(id) values (unhex(replace(uuid(), '-', '')));
I tried update id using * unhex(id) * conv(id, 10, 16)
What am I missing?
EDIT: Using thease scripts, I was able to recreate all IDs and Foreign keys
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%'
Problem solved.
