How to renumber primary index.

Question

I have got a simple MySQL table and primary index (id) is not numbered one by one (1, 31, 35, 100 etc.). I want them to be numbered like (1, 2, 3, 4). Please tell me how to do it. I would also like to point that I am aware of possible consequences of the operation, but I just want to tidy up the table.

Answer 1

You have to define the auto-increment on your primary key (if it is a primary key)

Here's a link:

sql autoincrement

Answer 2

No, you don't want it. Primary key is not a number. It is meaningless unique sequence.

Answer 3

CREATE TABLE newtable 
  LIKE oldtable 
  SELECT NULL, col1, col2, col3, etc FROM oldtable;

Without including the primary key column in the select, but all the other columns.

And the primary key column should be first in the table, or you'll need to set the NULL to the position of the primary key column. And the column should have a "NOT NULL" setting. Otherwise it will just be set as null, and that's kind of pointless.

The reason this works is because MySQL likes to ignore NULL inserts on auto_increments, and instead create a new number for it (provided that null isn't a valid value on that column).

Answer 4

1. Delete the "id" column.
2. Create column named id with auto increment on(and do not allow nulls), it will have the new values you want.
3. Assign this new "id" column as PK.

edited later thanks to comments.

Answer 5

The easiest Solution is:

• Rename the table to tablename_temp
• Create a new table with the old name and the same structure
• INSERT INTO tablename (id, field1, field2, field3) SELECT NULL, field1, field2, field3, ... FROM tablename_temp;
• DROP tablename_temp

This will destroy all of your data if you are using foreign keys and i strongly suggest you leave the ids as they are. A Database is not untidy because the primary keys are not in sequence, a Database is untidy if it's referential integrity (id values pointing to the wrong or non-existant row in other tables) is broken.

Answer 6

I agree other methods will work but I'm just giving a different idea. This will do without any temp table creation requirements::

set @a=0;
update TABLENAME set COLUMNNAME=(@a:=@a+1);

Answer 7

Well i had the same Problem in ORACLE and i used this:

CREATE TABLE Table_Test (id1 NUMBER(1) NOT NULL UNIQUE, column1 VARCHAR2(10), column2 VARCHAR2(10) )

INSERT INTO Table_Test ( id1,column1,column2 )
VALUES ( 1,'test','test2');
INSERT INTO Table_Test ( id1,column1,column2 )
VALUES ( 2,'test','test2');
INSERT INTO Table_Test ( id1,column1,column2 )
VALUES ( 5,'test','test2');
INSERT INTO Table_Test ( id1,column1,column2 )
VALUES ( 9,'test','test2');

COMMIT;

here is the Procedure i used:

create 
PROCEDURE  reCalc_ID

IS 
v_NewID NUMBER   :=1; 
BEGIN   

FOR rec IN (SELECT ID1, column1,column2
           FROM Table_test)
LOOP

update Table_test
    set id1 = v_NewID
    where id1 = rec.ID1;
v_newid := v_newid +1;
END LOOP;

Commit; 

END;

but be carefull if other tables got a fk to this Table it wont work, except all fks are Cascade but im not sure if its the same on MySql