-1

Hi all,
When you add integer ID primary key auto increment, it automatically makes ID for you. But I was pondering on what happens after deleting a record. Does the server automatically adjust all IDs for you or it remains un assigned?

For example, I have inserted as follows
+------------+-------------+
| 1 | Orange |
|-------------+-------------+
| 2 | Banana |
|-------------+-------------+
| 3 | Mango |
|-------------+-------------+

If I delete Banana, what happens to ID no 2

Edited by Stefano Mtangoo: n/a

3
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by Stefano Mtangoo
0

Once its deleted it won't be used again (unless you truncate the table). MySQL will not reassign the ids, as it would defeat the purpose of using them. Once you set an id, it should never change.

0

Good explanation. But what happens after a deleting and adding let say millions of user? Wont Database become clogged?

0

If you used an INT(11) data type I belive the max ID would be 4,294,967,295 which would be about the total online community of the entire planet, so a very large user base :-)

I think you'll hit other server side issue long before you hit the ID limit, i.e disk space, too many concurrent users etc.

Hope this helps you sleep easier.

0

If you used an INT(11) data type I belive the max ID would be 4,294,967,295 which would be about the total online community of the entire planet, so a very large user base :-)

I think you'll hit other server side issue long before you hit the ID limit, i.e disk space, too many concurrent users etc.

Hope this helps you sleep easier.

Thanks all guys. It have been alot of learning and fun:)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.