0

My Table Primary Key is user_id and starts with 101, 102...

The client wants me to now start with 60001001, 60001002, and so forth.

How would a MySql query be written to update these primary key values?

Thanks

Hector
  • 682
  • 3
  • 14
  • 27
  • Are there related tables? – maszter Sep 23 '14 at 22:03
  • NOTE: Resetting the `AUTO_INCREMENT` for a table will not cause any existing `user_id` values to be updated. (And this is particularly true if `user_id` is NOT an AUTO_INCREMENT column.) To change the user_id values on existing rows, you'd need to use an UPDATE statement, e.g. **`UPDATE mytable SET user_id = user_id + (60001001-101)`**. Note that if there are any foreign key values that reference this column, and the constraint is not defined with ON UPDATE CASCADE rule, this operation will fail. If there are other references that are *not* foreign keys, those will need to be updated separate – spencer7593 Sep 23 '14 at 22:09
  • 1
    You do not make the primary key "respect" the wishes of your client. Primary key has a specific purpose completely internal to the data model. What you need is another column that you update with some sort of incrementing count. That column should not, must not be primary key. There are multiple reasons for that, from performance to data integrity. Please be smart and research before you attempt to make primary key perform a role of some sort of client imagined display thingy instead of what it's supposed to do - **uniquely identify a row**. That's all it should do. – N.B. Sep 23 '14 at 22:27
  • N.B. This makes the most sense, i don't know why i didn't think of that. Must have been a long day yesterday. Thanks – Hector Sep 24 '14 at 14:23

0 Answers0