it is possible to reorder/reset database table id?

hi likewise as the subject says, it is possible to reset table id? for example, table forum ids.. resetting id 3,5,6,8.. etc to 1,2,3,4.. so on..

could anyway give me a query code on how to do it if it is possible then?

thank you in advance..

 

Well, if you have foreign keys this won't work well.. It would mean that threads would link to the wrong forum_id etc. But if you don't, you can just drop the primary key and then recreate it. Or rename it and add a new primary key.

 

Unless you're a crazy numerologist who thinks certain numbers have mystical healing powers, you shouldn't do this. The id numbers that have been assigned should mean nothing and have no value to you nor anyone else.

They exist only to provide a way to make each row of data unique and thereby easy to reference within the database itself. Don't make your id numbers have external meaning, instead make a new field to hold whatever external meaning it is you want to capture.

 

Best way would be to duplicate the table, and copy all the rows from one table to the other. Delete the original table, and rename the new one.

You can easily copy all the data, less the id column in a single statement. You will need to explicitely name the columns so that you don't copy the id's.

Do a backup before running this, but these queries should do exactly what you want. As stated above if you have foreign keys, this will not work.

CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table (name, other_columns) SELECT name, other columns FROM old_table;
DROP old_table;
RENAME TABLE new_table TO old_table;

There is logical reasoning to do this if you have deleted a lot of rows from a very large table.

 

Then you should use OPTIMIZE TABLE.

 

Yes, this will clear out the free space and correct the cardinality, but won't reorder the identity column. If you're running out of available numbers recreating the table is the only way to reset the identity column.

 

Well, you won't normally run out of numbers use UNSIGNED integers or bigints if you reach the limit.

 

I agree. Unisgned INT is far more than I have ever needed.

 

I dont think there is a way to do it but integer is having enough values than you need

 

i'm afraid it cannot be done. but you can try to optimize the database. It also depend to the database engine.

 

I agree that you should never change your PK's of your database table. The whole normalization concept says that they should never change.

If you are using them for your URLs or something that can be seen by the end user, you might want to consider adding another column to the table for storing this, perhaps as a human-readable string.

If, however, you've got a bunch of unordered records (DELETEing and INSERTing will do this), you can clean up the order and make your SELECTs faster by issuing this command:

ALTER TABLE `tablename` ORDER BY `id`

Code (markup):

 

I agree with you. Do not need to change or reorder id, you should add new field and order database following to the new one.