Insert auto increment primary key to existing table

Question

I am trying to alter a table which has no primary key nor auto increment column  I know how to add an primary key column but I was wondering if it s possible to insert data into the primary key column automatically  I already have 500 rows in DB and want to give them id but I don t want to do it manually   Any thoughts  Thanks a lot

User · Answer

yes  something like this would do it  might not be the best though  you might wanna make a backup   get query   mysql query  SELECT  any field  FROM  your table       auto increment id   1   while  row   mysql fetch assoc  get query        update query   mysql query  UPDATE  your table  SET  auto increment id   auto increment id WHERE  any field        row  any field            auto increment id        notice that the the any field you select must be the same when updating

User · Answer

suppose you don t have column for auto increment like id  no  then you can add using following query   ALTER TABLE table name ADD id int NOT NULL AUTO INCREMENT primary key FIRST   If you ve column  then alter to auto increment using following query    ALTER TABLE table name MODIFY column name datatype length  AUTO INCREMENT PRIMARY KEY

User · Answer

I was facing the same problem so what I did I dropped the field  for the primary key then I recreated it and made sure that it is auto incremental   That worked for me   I hope it helps others

User · Answer

I was able to adapt these instructions take a table with an existing non-increment primary key  and add an incrementing primary key to the table and create a new composite primary key with both the old and new keys as a composite primary key using the following code   DROP TABLE  IF EXISTS SAKAI USER ID MAP   CREATE TABLE SAKAI USER ID MAP          USER ID             VARCHAR  99  NOT NULL         EID                 VARCHAR  255  NOT NULL         PRIMARY KEY  USER ID      INSERT INTO SAKAI USER ID MAP VALUES   admin    admin    INSERT INTO SAKAI USER ID MAP VALUES   postmaster    postmaster     ALTER TABLE  SAKAI USER ID MAP    DROP PRIMARY KEY     ADD  USER ID INT AUTO INCREMENT NOT NULL FIRST     ADD PRIMARY KEY    USER ID  USER ID      When this is done  the  USER ID field exists and has all number values for the primary key exactly as you would expect   With the  DROP TABLE  at the top  you can run this over and over to experiment with variations   What I have not been able to get working is the situation where there are incoming FOREIGN KEYs that already point at the USER ID field   I get this message when I try to do a more complex example with an incoming foreign key from another table    1025 - Error on rename of    zap  sql-da07 6d  to    zap SAKAI USER ID MAP   errno  150    I am guessing that I need to tear down all foreign keys before doing the ALTER table and then rebuild them afterwards    But for now I wanted to share this solution to a more challenging version of the original question in case others ran into this situation

User · Answer

How to write PHP to ALTER the already existing field  name  in this example  to make it a primary key  W o  of course  adding any additional  id  fields to the table    This a table currently created - Number of Records found  4 name VARCHAR 20  YES breed VARCHAR 30  YES color VARCHAR 20  YES weight SMALLINT 7  YES  This an end result sought  TABLE DESCRIPTION  -   Number of records found  4  name VARCHAR 20  NO PRI breed VARCHAR 30  YES color VARCHAR 20  YES weight SMALLINT 7  YES  Instead of getting this -   Number of Records found  5 id int 11  NO PRI name VARCHAR 20  YES breed VARCHAR 30  YES color VARCHAR 20  YES weight SMALLINT 7  YES  after trying     query    ALTER TABLE racehorses ADD id INT NOT NULL AUTO INCREMENT FIRST  ADD PRIMARY KEY  id     how to get this  -  Number of records found  4 name VARCHAR 20  NO PRI breed VARCHAR 30  YES color VARCHAR 20  YES weight SMALLINT 7  YES  i e  INSERT ADD   etc  the primary key INTO the first field record  w o adding an additional  id  field  as stated earlier

User · Answer

The easiest and quickest I find is this  ALTER TABLE mydb mytable  ADD COLUMN mycolumnname INT NOT NULL AUTO INCREMENT AFTER updated  ADD UNIQUE INDEX mycolumnname UNIQUE  mycolumname ASC

User · Answer

In order to make the existing primary key as auto increment  you may use  ALTER TABLE table name MODIFY id INT AUTO INCREMENT

User · Answer

For those like myself getting a Multiple primary key defined error try   ALTER TABLE  myTable  ADD COLUMN  id  INT AUTO INCREMENT UNIQUE FIRST NOT NULL    On MySQL v5 5 31 this set the id column as the primary key for me and populated each row with an incrementing value

User · Answer

Export your table  then empty your table  then add field as unique INT  then change it to AUTO INCREMENT  then import your table again that you exported previously

User · Answer

Well  you have multiple ways to do this  -if you don t have any data on your table  just drop it and create it again  Dropping the existing field and creating it again like this ALTER TABLE test DROP PRIMARY KEY  DROP test id  ADD test id int AUTO INCREMENT NOT NULL FIRST  ADD PRIMARY KEY  test id    Or just modify it ALTER TABLE test MODIFY test id INT AUTO INCREMENT NOT NULL  ADD PRIMARY KEY  test id

User · Answer

You can add a new Primary Key column to an existing table  which can have sequence numbers  using command    ALTER TABLE mydb mytable ADD pk columnName INT IDENTITY

User · Answer

ALTER TABLE tableName MODIFY tableNameID MEDIUMINT NOT NULL AUTO INCREMENT   Here tableName is name of your table   tableName is your column name which is primary has to be modified  MEDIUMINT is a data type of your existing primary key  AUTO INCREMENT you have to add just auto increment after not null  It will make that primary key auto increment        Hope this is helpful

User · Answer

An ALTER TABLE statement adding the PRIMARY KEY column works correctly in my testing   ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO INCREMENT    On a temporary table created for testing purposes  the above statement created the AUTO INCREMENT id column and inserted auto-increment values for each existing row in the table  starting with 1

[mysql] Insert auto increment primary key to existing table

Examples related to mysql

Examples related to primary-key