mysql primary key auto increment issue

Question

I have a table (entry_id, user_id, event_id)with entry_id set to auto increment. But i had forgotten to make entry_id as primary key. Now i have about 400 entries with same entry_id. It hasnt incremented. How do i change it so that entry_id is unique without deleting the table?

If i do the following query will i lose anything?

ALTER TABLE your_table
ADD PRIMARY KEY (entry_id);

Note that all my entry_ids have 1 as value.

Answer 1

Try creating a new table with the autoincrement value, then do an INSERT SELECT with the existing table (excluding the column with the incorrect key values) into the new one. That should populate the key automatically. Finally, drop the old table and rename the new one.

Answer 2

1. Reset all entry_id values to NULL
2. Make field entry_id PRIMARY+AUTO_INCREMENT, and MySQL will do everething for you.

Here it is a script -

UPDATE your_table SET entry_id = NULL;

ALTER TABLE your_table
  CHANGE COLUMN entry_id entry_id INT(11) NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (entry_id);

Answer 3

I think, first you should run a script which loop through each of the rows and set the entry_id in auto incremented fashion. and the run the query

ALTER TABLE your_table
ADD PRIMARY KEY (entry_id);

Answer 4

you can use the row number to set your primary key column:

update your_table set entry_id=@curRow;

Answer 5

You will not lose anything, as the query will not run:

mysql> alter table test add primary key (entry_id);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Either re-number your entry_id column, or copy the entire table in a new (correct) table without the entry_id and let it AUTO_INCREMENT for you.

Answer 6

Always have a backup of a table and work in this query it will not affect any table . Also if you have the auto increment as 1 ,once you add record it would return an error as duplicate if 1 is exists in the table .so it would be simple to start with maximum value of the entity_id for auto_increment to avoid such duplicate value insertion.

Correct me if 'm wrong

Answer 7

Alternatively you could do this in two steps:

Run the following SQL to increment your IDs:

begin
declare entry_id_upd, numberOfRecords, cursor int;
set numberOfRecords = (select count(*) from mytable);
set cursor = 0;
while cursor <= numberOfRecords do
set entry_id_upd = ( select entry_id from mytable where id = cursor ) + 1;
update mytable set entry_id = entry_id_upd where id = cursor;
set cursor = ( cursor + 1 );
end while;
end

And then set primary key:

ALTER TABLE mytable ADD PRIMARY KEY (entry_id);