CODEWITHSUNDEEP
mysqlauto-increment
blunders
blunders

Reputation: 3669

How is MySQL's AUTO_INCREMENT column reset

Few question about resetting MySQL's AUTO_INCREMENT column.

First without deleting/creating a table, or deleting rows via TRUNCATE TABLE (see footer for that code) -- is this the correct way to do it?

ALTER TABLE [insert_TABLE_name] AUTO_INCREMENT = 1

Next, is value set to 0 or 1 -- and why?

Bonus, any extra advice.

Upvotes: 1

Views: 569

Answers (2)

Marc B
Marc B

Reputation: 360862

It's set to whatever you use in the alter table query, which is 1 in your case. However, since you're not deleting rows, you've now probably set yourself up for a primary key violation, since your next inserted row will try to use that 1 and conflict with a previously created row with the same key.

Upvotes: 3

Tom
Tom

Reputation: 488

You cannot reset the counter to a value less than or equal to any that have already been used.

you are trying to put the auto_increment counter back to 1, but it's already higher than that value, and as you cannot reset it to a value that's less than any value that's already been used, it doesn't work.

Upvotes: 1

Related Questions