-1

I searched in the web and I don't find a solution without to delete the field which is auto_increment.But, I remarked that in phpmyadmin, when I click to Options I see the Auto_increment, I tried to put it in 1 but nothing changed. So, are there a solution to reset auto_increment ?

Thanks

PS : InnoDB

Yasmine Yesse
  • 5
  • 5
  • 1
    http://stackoverflow.com/questions/1485668/how-to-set-initial-value-and-auto-increment-in-mysql – Mihai Aug 28 '15 at 07:38
  • It does not work for InnoDB – Yasmine Yesse Aug 28 '15 at 07:43
  • 2
    Regardless if it is possible or not, the right question here should be: "Am I doing something wrong?". Usually an auto-increment is used for PK columns, which you should not care what values they have - they are mostly for internal database working (FK, to guarantee uniqueness and data consistency, etc). The only reason I can think of such request is index optimization (in case of large gaps), which most databases have a way to handle – Ivaylo Slavov Aug 28 '15 at 07:43
  • 1
    @YasmineYesse: It certainly does work UNLESS you have foreign key constraints preventing it. Is this your actual problem? That you are using the InnoDB engine and you have constraints defined between the tables, and now you want to zero out data in a number of tables and reset? – gview Aug 28 '15 at 07:47
  • @gview : Yes, I have constraints but my table dosen't have values null, I imported an SQL which has auto_increment = 425, so I don't change it. – Yasmine Yesse Aug 28 '15 at 07:52

5 Answers5

2

DBCC CHECKIDENT ('tablename', RESEED, 0);

Irfan Shaikh
  • 90
  • 6
1

I am not sure what you mean by reset? but if you setted up the AUTO_INCREMENT and now you want to remove it. you can use this:

ALTER TABLE mytable CHANGE my_col my_col INT(10) UNSIGNED NOT NULL

If you want to reser the counter:

ALTER TABLE tablename AUTO_INCREMENT = 1

InnoDB

ALTER TABLE a AUTO_INCREMENT=1 ENGINE=innoDB;
Ilanus
  • 6,690
  • 5
  • 13
  • 37
1

Before everyone gets on their high horse in regards to whether anyone should have ever have a valid reason for reseting an AUTO_INCREMENT counter, there are scenarios where it makes sense. A production db isn't the place for this, but development and QA environments are different.

I have created code to do this myself for Fixtures in a development environment, not to mention unit test databases that need to be repeatedly cleared. Sometimes you want to be able to truncate tables rather than doing a complete recreation.

It's a completely reasonable request, and there might be good reason for it, especially if you're counting bytes and making use of an UNSIGNED tinyint and similar byte optimization with some of your foreign keys.

As stated the solution is:

ALTER TABLE tablename AUTO_INCREMENT = 1

However, this will not work, if there are foreign key constraints defined.

The magic "get around fk constraints and other chicken-and-egg problems" that can happen with InnoDB constraints is:

SET foreign_key_checks = 0;

When things are zeroed back out the way you need them to be, you can then turn constraint checking back on with:

SET foreign_key_checks = 1;

This seems like it may be the actual issue the OP is having.

gview
  • 14,876
  • 3
  • 46
  • 51
0

as long there is a data on the table you cannot reset it or else you need to delete all the tables first. 

ALTER TABLE tablename AUTO_INCREMENT = value;
kim de castro
  • 299
  • 6
  • 19
0

execute the following query:

ALTER TABLE your table name auto_increment = 1;

Gaurav Srivastava
  • 3,232
  • 3
  • 16
  • 36