Member Avatar for rotten69

Hi everyone,

I have created tables and deleted some rows and then inserted new rows. The IDs of the later created rows didn't start at 0 or 1. I dropped my entire database then recreated the same one with the same tables and fields. Unfortunately, newly entered rows started from where they were left at. For example, in the previous database, I had 9 rows in users table. After the database creation, the first row started at 10 instead of starting at 0 or 1. Now, I'd like to reset the auto-increment ID. Is there a way of resetting the ID after dropping and recreating a table with the same fields ?

I tried this line but it didn't work !!

ALTER TABLE `users` AUTO_INCREMENT = 1;

Thanks, folks.

  • 2 Contributors
  • 3 Replies
  • 170 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by rotten69
Member Avatar for pritaeas

In one of the user comments on this page it is suggested to drop your auto-inc column, and re-add it.

I'm not sure how you recreated your database, but if you did it with an sql dump, the original auto-inc may have been in the create table query. Depending on how you exported, there should be a possibility to turn that off.

Member Avatar for rotten69

ID is a primary key in the parent table and causes a referential integrity issue if I attempt to delete it and recreate it.

Member Avatar for rotten69

I'm not sure how you recreated your database, but if you did it with an sql dump,

I dropped all constraints and FKs from the tables and then dropped the database.

DROP DATABASE database_name

I noticed that this command is disabled when the DB has tables in it. So, I had to drop all tables then drop the DB.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.