How to reset AUTO INCREMENT field in a table?
Consider the given below scenerio in which the record with the ISBN 113 is deleted. The last record with the ISBN is 112. But as we insert a new record the last record with the ISBN becomes 114.
Table structure:
Code: Select all
mysql> desc book;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| ISBN | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| title | char(20) | YES | | NULL | |
| mrp | smallint(5) unsigned | YES | | 100 | |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql>Code: Select all
mysql> select * from book;
+------+-------+------+
| ISBN | title | mrp |
+------+-------+------+
| 111 | C | 500 |
| 112 | VC++ | 100 |
+------+-------+------+
2 rows in set (0.00 sec)
mysql> insert into book (title) values("VB");
Query OK, 1 row affected (0.00 sec)
mysql> select * from book;
+------+-------+------+
| ISBN | title | mrp |
+------+-------+------+
| 111 | C | 500 |
| 112 | VC++ | 100 |
| 113 | VB | 100 |
+------+-------+------+
3 rows in set (0.00 sec)
mysql> delete from book where title="VB";
Query OK, 1 row affected (0.01 sec)
mysql> select * from book;
+------+-------+------+
| ISBN | title | mrp |
+------+-------+------+
| 111 | C | 500 |
| 112 | VC++ | 100 |
+------+-------+------+
2 rows in set (0.00 sec)
mysql> insert into book (title) values("VB");
Query OK, 1 row affected (0.00 sec)
mysql> select * from book;
+------+-------+------+
| ISBN | title | mrp |
+------+-------+------+
| 111 | C | 500 |
| 112 | VC++ | 100 |
| 114 | VB | 100 |
+------+-------+------+
3 rows in set (0.00 sec)
mysql>So, how can we reset all the ISBN values so that every record has a consistently increment ISBN value?