make an ID in a mysql table auto increment after the fact

Question

I acquired a database from another developer   He didn t use auto incrementers on any tables   They all have primary key ID s  but he did all the incrementing manually  in code   Can I turn those into Auto incrementers now     Wow  very nice  thanks a ton  It worked without a hitch on one of my tables  But a second table  i m getting this error   Error on rename of    DBNAME sql-6c8 62259c  to    DBNAME dealer master events

User · Answer

As long as you have unique integers  or some unique value  in the current PK  you could create a new table  and insert into it with IDENTITY INSERT ON  Then drop the old table  and rename the new table   Don t forget to recreate any indexes

User · Answer

For example  here s a table that has a primary key but is not AUTO INCREMENT   mysql gt  CREATE TABLE foo     id INT NOT NULL    PRIMARY KEY  id     mysql gt  INSERT INTO foo VALUES  1    2    5     You can MODIFY the column to redefine it with the AUTO INCREMENT option   mysql gt  ALTER TABLE foo MODIFY COLUMN id INT NOT NULL AUTO INCREMENT    Verify this has taken effect   mysql gt  SHOW CREATE TABLE foo    Outputs   CREATE TABLE foo      id  INT 11  NOT NULL AUTO INCREMENT    PRIMARY KEY   id     ENGINE MyISAM AUTO INCREMENT 6 DEFAULT CHARSET latin1   Note that you have modified the column definition in place  without requiring creating a second column and dropping the original column   The PRIMARY KEY constraint is unaffected  and you don t need to mention in in the ALTER TABLE statement   Next you can test that an insert generates a new value   mysql gt  INSERT INTO foo    VALUES     -- yes this is legal syntax mysql gt  SELECT   FROM foo    Outputs    ----    id    ----     1       2       5       6     ----  4 rows in set  0 00 sec    I tested this on MySQL 5 0 51 on Mac OS X   I also tested with ENGINE InnoDB and a dependent table   Modifying the id column definition does not interrupt referential integrity     To respond to the error 150 you mentioned in your comment  it s probably a conflict with the foreign key constraints   My apologies  after I tested it I thought it would work   Here are a couple of links that may help to diagnose the problem    What does mysql error 1025  HY000   Error on rename of   39   foo  39   errorno  150  mean  http   www simplicidade org notes archives 2008 03 mysql errno 150 html

User · Answer

I m guessing that you don t need to re-increment the existing data so  why can t you just run a simple ALTER TABLE command to change the PK s attributes   Something like   ALTER TABLE  content  CHANGE  id   id  SMALLINT  5   UNSIGNED NOT NULL AUTO INCREMENT    I ve tested this code on my own MySQL database and it works but I have not tried it with any meaningful number of records  Once you ve altered the row then you need to reset the increment to a number guaranteed not to interfere with any other records   ALTER TABLE  content  auto increment   MAX  id     1   Again  untested but I believe it will work

User · Answer

I m guessing that you don t need to re-increment the existing data so  why can t you just run a simple ALTER TABLE command to change the PK s attributes   Something like   ALTER TABLE  content  CHANGE  id   id  SMALLINT  5   UNSIGNED NOT NULL AUTO INCREMENT    I ve tested this code on my own MySQL database and it works but I have not tried it with any meaningful number of records  Once you ve altered the row then you need to reset the increment to a number guaranteed not to interfere with any other records   ALTER TABLE  content  auto increment   MAX  id     1   Again  untested but I believe it will work

User · Answer

Yes  easy  Just run a data-definition query to update the tables  adding an AUTO INCREMENT column    If you have an existing database  be careful to preserve any foreign-key relationships that might already be there on the  artificially created  primary keys

User · Answer

For example  here s a table that has a primary key but is not AUTO INCREMENT   mysql gt  CREATE TABLE foo     id INT NOT NULL    PRIMARY KEY  id     mysql gt  INSERT INTO foo VALUES  1    2    5     You can MODIFY the column to redefine it with the AUTO INCREMENT option   mysql gt  ALTER TABLE foo MODIFY COLUMN id INT NOT NULL AUTO INCREMENT    Verify this has taken effect   mysql gt  SHOW CREATE TABLE foo    Outputs   CREATE TABLE foo      id  INT 11  NOT NULL AUTO INCREMENT    PRIMARY KEY   id     ENGINE MyISAM AUTO INCREMENT 6 DEFAULT CHARSET latin1   Note that you have modified the column definition in place  without requiring creating a second column and dropping the original column   The PRIMARY KEY constraint is unaffected  and you don t need to mention in in the ALTER TABLE statement   Next you can test that an insert generates a new value   mysql gt  INSERT INTO foo    VALUES     -- yes this is legal syntax mysql gt  SELECT   FROM foo    Outputs    ----    id    ----     1       2       5       6     ----  4 rows in set  0 00 sec    I tested this on MySQL 5 0 51 on Mac OS X   I also tested with ENGINE InnoDB and a dependent table   Modifying the id column definition does not interrupt referential integrity     To respond to the error 150 you mentioned in your comment  it s probably a conflict with the foreign key constraints   My apologies  after I tested it I thought it would work   Here are a couple of links that may help to diagnose the problem    What does mysql error 1025  HY000   Error on rename of   39   foo  39   errorno  150  mean  http   www simplicidade org notes archives 2008 03 mysql errno 150 html

User · Answer

Yes  easy  Just run a data-definition query to update the tables  adding an AUTO INCREMENT column    If you have an existing database  be careful to preserve any foreign-key relationships that might already be there on the  artificially created  primary keys

User · Answer

ALTER TABLE  foo  MODIFY COLUMN  bar id  INT NOT NULL AUTO INCREMENT    or   ALTER TABLE  foo  CHANGE  bar id   bar id  INT UNSIGNED NOT NULL AUTO INCREMENT    But none of these will work if your bar id is a foreign key in another table  you ll be getting   an error 1068  Multiple primary key defined   To solve this  temporary disable foreign key constraint checks by  set foreign key checks   0    and after running the statements above  enable them back again    set foreign key checks   1

User · Answer

ALTER TABLE  foo  MODIFY COLUMN  bar id  INT NOT NULL AUTO INCREMENT    or   ALTER TABLE  foo  CHANGE  bar id   bar id  INT UNSIGNED NOT NULL AUTO INCREMENT    But none of these will work if your bar id is a foreign key in another table  you ll be getting   an error 1068  Multiple primary key defined   To solve this  temporary disable foreign key constraint checks by  set foreign key checks   0    and after running the statements above  enable them back again    set foreign key checks   1

User · Answer

I m guessing that you don t need to re-increment the existing data so  why can t you just run a simple ALTER TABLE command to change the PK s attributes   Something like   ALTER TABLE  content  CHANGE  id   id  SMALLINT  5   UNSIGNED NOT NULL AUTO INCREMENT    I ve tested this code on my own MySQL database and it works but I have not tried it with any meaningful number of records  Once you ve altered the row then you need to reset the increment to a number guaranteed not to interfere with any other records   ALTER TABLE  content  auto increment   MAX  id     1   Again  untested but I believe it will work

User · Answer

I m guessing that you don t need to re-increment the existing data so  why can t you just run a simple ALTER TABLE command to change the PK s attributes   Something like   ALTER TABLE  content  CHANGE  id   id  SMALLINT  5   UNSIGNED NOT NULL AUTO INCREMENT    I ve tested this code on my own MySQL database and it works but I have not tried it with any meaningful number of records  Once you ve altered the row then you need to reset the increment to a number guaranteed not to interfere with any other records   ALTER TABLE  content  auto increment   MAX  id     1   Again  untested but I believe it will work

User · Answer

This worked for me  i wanted to make id primary and  set auto increment   ALTER TABLE table name CHANGE id id INT PRIMARY KEY AUTO INCREMENT

User · Answer

Yes  easy  Just run a data-definition query to update the tables  adding an AUTO INCREMENT column    If you have an existing database  be careful to preserve any foreign-key relationships that might already be there on the  artificially created  primary keys

User · Answer

As long as you have unique integers  or some unique value  in the current PK  you could create a new table  and insert into it with IDENTITY INSERT ON  Then drop the old table  and rename the new table   Don t forget to recreate any indexes

User · Answer

Yes  easy  Just run a data-definition query to update the tables  adding an AUTO INCREMENT column    If you have an existing database  be careful to preserve any foreign-key relationships that might already be there on the  artificially created  primary keys

User · Answer

This worked for me  i wanted to make id primary and  set auto increment   ALTER TABLE table name CHANGE id id INT PRIMARY KEY AUTO INCREMENT

User · Answer

As long as you have unique integers  or some unique value  in the current PK  you could create a new table  and insert into it with IDENTITY INSERT ON  Then drop the old table  and rename the new table   Don t forget to recreate any indexes

User · Answer

As long as you have unique integers  or some unique value  in the current PK  you could create a new table  and insert into it with IDENTITY INSERT ON  Then drop the old table  and rename the new table   Don t forget to recreate any indexes

User · Answer

For example  here s a table that has a primary key but is not AUTO INCREMENT   mysql gt  CREATE TABLE foo     id INT NOT NULL    PRIMARY KEY  id     mysql gt  INSERT INTO foo VALUES  1    2    5     You can MODIFY the column to redefine it with the AUTO INCREMENT option   mysql gt  ALTER TABLE foo MODIFY COLUMN id INT NOT NULL AUTO INCREMENT    Verify this has taken effect   mysql gt  SHOW CREATE TABLE foo    Outputs   CREATE TABLE foo      id  INT 11  NOT NULL AUTO INCREMENT    PRIMARY KEY   id     ENGINE MyISAM AUTO INCREMENT 6 DEFAULT CHARSET latin1   Note that you have modified the column definition in place  without requiring creating a second column and dropping the original column   The PRIMARY KEY constraint is unaffected  and you don t need to mention in in the ALTER TABLE statement   Next you can test that an insert generates a new value   mysql gt  INSERT INTO foo    VALUES     -- yes this is legal syntax mysql gt  SELECT   FROM foo    Outputs    ----    id    ----     1       2       5       6     ----  4 rows in set  0 00 sec    I tested this on MySQL 5 0 51 on Mac OS X   I also tested with ENGINE InnoDB and a dependent table   Modifying the id column definition does not interrupt referential integrity     To respond to the error 150 you mentioned in your comment  it s probably a conflict with the foreign key constraints   My apologies  after I tested it I thought it would work   Here are a couple of links that may help to diagnose the problem    What does mysql error 1025  HY000   Error on rename of   39   foo  39   errorno  150  mean  http   www simplicidade org notes archives 2008 03 mysql errno 150 html

User · Answer

None of the above worked for my table  I have a table with an unsigned integer as the primary key with values ranging from 0 to 31543  Currently there are over 19 thousand records  I had to modify the column to AUTO INCREMENT  MODIFY COLUMN id INTEGER UNSIGNED NOT NULL AUTO INCREMENT  and set the seed AUTO INCREMENT   31544  in the same statement   ALTER TABLE   TableName   MODIFY COLUMN   id   INTEGER UNSIGNED NOT NULL AUTO INCREMENT  AUTO INCREMENT   31544

User · Answer

None of the above worked for my table  I have a table with an unsigned integer as the primary key with values ranging from 0 to 31543  Currently there are over 19 thousand records  I had to modify the column to AUTO INCREMENT  MODIFY COLUMN id INTEGER UNSIGNED NOT NULL AUTO INCREMENT  and set the seed AUTO INCREMENT   31544  in the same statement   ALTER TABLE   TableName   MODIFY COLUMN   id   INTEGER UNSIGNED NOT NULL AUTO INCREMENT  AUTO INCREMENT   31544

User · Answer

For example  here s a table that has a primary key but is not AUTO INCREMENT   mysql gt  CREATE TABLE foo     id INT NOT NULL    PRIMARY KEY  id     mysql gt  INSERT INTO foo VALUES  1    2    5     You can MODIFY the column to redefine it with the AUTO INCREMENT option   mysql gt  ALTER TABLE foo MODIFY COLUMN id INT NOT NULL AUTO INCREMENT    Verify this has taken effect   mysql gt  SHOW CREATE TABLE foo    Outputs   CREATE TABLE foo      id  INT 11  NOT NULL AUTO INCREMENT    PRIMARY KEY   id     ENGINE MyISAM AUTO INCREMENT 6 DEFAULT CHARSET latin1   Note that you have modified the column definition in place  without requiring creating a second column and dropping the original column   The PRIMARY KEY constraint is unaffected  and you don t need to mention in in the ALTER TABLE statement   Next you can test that an insert generates a new value   mysql gt  INSERT INTO foo    VALUES     -- yes this is legal syntax mysql gt  SELECT   FROM foo    Outputs    ----    id    ----     1       2       5       6     ----  4 rows in set  0 00 sec    I tested this on MySQL 5 0 51 on Mac OS X   I also tested with ENGINE InnoDB and a dependent table   Modifying the id column definition does not interrupt referential integrity     To respond to the error 150 you mentioned in your comment  it s probably a conflict with the foreign key constraints   My apologies  after I tested it I thought it would work   Here are a couple of links that may help to diagnose the problem    What does mysql error 1025  HY000   Error on rename of   39   foo  39   errorno  150  mean  http   www simplicidade org notes archives 2008 03 mysql errno 150 html

[mysql] make an ID in a mysql table auto_increment (after the fact)

Examples related to mysql

Examples related to primary-key

Examples related to auto-increment