Re-use Auto_increment field

[ozzii]

Hello, Can autonumber be reused if the record to which it was assigned is deleted?

I have a database table in which autonumber is used to uniquely identify records. However this table is appended and deleted with 10000 or more records daily. Is there a limit to autonumber in MySql becuse I may run out numbers at this rate!

Or would you suggest using an alternative datatype number automatically generated by the database to uniquely identify records?

[ronverdonk]

No, you cannot re-use the auto_increment number just like that.

But why worry? With an INT auto_increment field with data type INT you can have 2147483647 as the maximum value.
10,000 changes per day, even inserts, means that you can live happily for the coming century.

And if you do get worried, and want to renumber the index:
- DROP the field you are auto_incrementi ng.
- ALTER the table to ADD the field again with the same attributes.
All existing rows are renumbered and the next auto_increment number will be equal to the row count plus 1.

Ronald :cool:

[ozzii]

No, you cannot re-use the auto_increment number just like that.

But why worry? With an INT auto_increment field with data type INT you can have 2147483647 as the maximum value.
10,000 changes per day, even inserts, means that you can live happily for the coming century.

And if you do get worried, and want to renumber the index:
- DROP the field you are auto_incrementi ng.
- ALTER the table to ADD the field again with the same attributes.
All existing rows are renumbered and the next auto_increment number will be equal to the row count plus 1.

Ronald :cool:

Thanks for the info. The drop and alter field solutions seems workable! Just one question the autonumber is also the primary key. Fortunatley the primary key isnt linked to any other table but what is the sql syntax to make it the primary key after it has been dropped and then added?

[ronverdonk]

Just using the standard alter table

ALTER TABLE xxx ADD COLUM id PRIMARY KEY AUTO_INCREMENT

statement.

Ronald :cool: