Auto Increment Inconsitent

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
devarishi
Forum Contributor
Posts: 101
Joined: Fri Feb 05, 2010 7:15 pm

Auto Increment Inconsitent

Post by devarishi »

Hi,


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?
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Auto Increment Inconsitent

Post by JakeJ »

Export the table without the isbn field and then then put it all in to a new table of the same name.

If you're using phpMyAdmin, it's pretty easy to do.

Be careful though, if you have another table that references back to ISBN it could change those references, so doing this on a one time basis might be fine, you're not going to want to do it all the time. It would be better to assign those numbers in a way have more consistent control over.
devarishi
Forum Contributor
Posts: 101
Joined: Fri Feb 05, 2010 7:15 pm

Re: Auto Increment Inconsitent

Post by devarishi »

[1] How to export a table with projection (selected colums) as well as selection (selected records)?

[2] phpMyAdmin = What is that?

[3] ON CASCADE UPDATE = Something which we have in other RDBMSes, such as Microsoft Access, is found in MySQL too? So that when ISBN is changed to some other value it is automatically updated in the tables that have references to this ISBN.

[4] How can we do it (Database Maintenance) offline so that changes made to a table for testing or temporarily or deletion of a table would not affect other tables and the referential integrity constraints? That way, we can copy the table "book", in our example, without the ISBN column. Delete this original table and name the duplicate table "book" and add a new column / field to it with the name "ISBN".

[5] How do we take a complete backup of a Databse before we perform any maintenance activity on it?

I may sound to have put up too many questions that relate to the main problem! :D
Post Reply