Menu
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.
458
Reset the auto increment fieldALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
Using TRUNCATE TABLE Statement. The TRUNCATE TABLE statement in MySQL completely deletes the table's data without removing a table's structure and always resets the auto-increment column value to zero.
InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the auto-increment counter, as described earlier. The auto_increment_offset variable determines the starting point for the AUTO_INCREMENT column value. The default setting is 1.
FIRST you remove all the reference records. After that do this
alter table maintable auto_increment=1;
It will work.
Please Refer Click here
156
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
