CODEWITHSUNDEEP
phpmysqlsql
mavsman
mavsman

Reputation: 23

Deleting last row in MySQL table

When I delete the last row in a MySQL table and then insert a new one, why is the id still incremented as though the first weren't deleted? Is there a way to prevent this from happening so it can be as though the original row weren't there? Here's an example

Teachers
id   first_name
0    joe
1    mike
2    jim

If I delete "jim" then insert "bob" then bob's id will be 3 and there will be no entry with an id of 2.

Upvotes: 1

Views: 1562

Answers (4)

osmancode
osmancode

Reputation: 291

you need to execute this command after every delete operation , this will reset your auto_increment to 1 witch will be set automatically the the last available id ( 2 in your example )

ALTER TABLE table_name AUTO_INCREMENT = 1

Upvotes: 0

stk
stk

Reputation: 1

you may also use ALTER TABLE tablename AUTO_INCREMENT = 2 if after deleting the row and before inserting the new one, in order to make the id incrementation start at 2

Upvotes: 0

Andy Jones
Andy Jones

Reputation: 6275

Deleting any given row will not change the current auto increment value.

You can view the current value of the auto increment variable for the table by running

SHOW TABLE STATUS LIKE 'mytable';

And you can change the auto increment value by running

ALTER TABLE 'mytable' AUTO_INCREMENT = '1234';

Upvotes: 1

Ignacio Ocampo
Ignacio Ocampo

Reputation: 2713

Because your id column was configured as autoincremental, and can't have the same value, unless you specify this manually

Upvotes: 2

Related Questions