CODEWITHSUNDEEP
mysqlsql
Afridi
Afridi

Reputation: 25

What could be the code for putting existing auto increment id in order?

I am trying to learn more about SQL, now I see there is something, when I delete something from the table it leaves that id empty like if I delete 8 and then insert new data it creates id 10. But learned that it can be fixed by this code

ALTER TABLE mytable AUTO_INCREMENT = 1;

But what I can't figure out, what if I have a table that contains id -16-17-18- and so on an on.

How do I make my SQL command to turn 16 into 1, 17 into 2, and 18 into 3.

Is there any code for that?

ALTER TABLE mytable AUTO_INCREMENT = 1;

Upvotes: 1

Views: 76

Answers (1)

computercarguy
computercarguy

Reputation: 2454

If you are using that id for a relational lookup value in another table, you don't want to do this. Reordering your ids will scramble the relationships.

Also, you generally shouldn't be deleting anything from databases. It removes historical data that can't be retrieved.

https://softwareengineering.stackexchange.com/questions/159232/should-we-ever-delete-data-in-a-database

Doing what you're asking is only an effect of your OCD kicking in and wanting an Order To Everything. Databases don't care about that. The data type for the id should have enough numbers to reach a huge amount of rows before you have to think about reordering anything, and it'd be easier to simply turn an int to a bigint than reordering anything. In SQL Server, you need over 2 billion rows to have to worry about this. It's likely you'll have to worry about disk size of your server before you have to worry about this.

https://www.connectionstrings.com/sql-server-data-types-reference/

Upvotes: 2

Related Questions