CODEWITHSUNDEEP
mysqlinnodbauto-increment
user3035623
user3035623

Reputation: 183

reset auto_increment in innodb

There are two tables:

maintable(id int primary key auto_increment, name char);

subtable(id int primary key, maintable_id int, index mianid (maintable_id), constraint mainid foreign key (maintable_id) references maintable(id) );

after some add and delete in maintable, i wanna reset the auto_increment of maintable, then i use "alter table maintable auto_increment = 1",but i just got "query ok ,0 rows affected". And "truncate maintable" was forbidden because it is referenced by subtable.

so my question is: how can i reset the auto_increment of maintable? appreciate for any thoughts!! thanks a lot !!!

ps: mysql5.6; InnoDB.

Upvotes: 6

Views: 6852

Answers (1)

VDN
VDN

Reputation: 320

FIRST you remove all the reference records. After that do this 

alter table maintable auto_increment=1;

It will work.

Please Refer Click here

Upvotes: 4

Related Questions