Question on auto increment

baburajv · #1 (**permalink**) 12-09-10, 22:04

Hello,

i have a table, "test" with the following columns

id int auto_increment not null primary key,
name varchar(25) not null unique.

i executed the following statements twice.

insert into test(name) values ('ABCD'); /*executed twice*/
as expected the first run was successful and the second run failed due to unique constraint.

i inserted another record in the table and did a SELECT and i see that the id value is now 3.

i would like to know how id value get incremented to 3. As per my understanding, auto increment value will be generated when the record is inserted into the table. am i missing something here?

-Baburaj

healdem · #2 (**permalink**) 12-10-10, 03:42

Im guessing but when the SQL engine tried to insert a row it retrieved the next autonumber in the sequence, when the insert failed the new row was deleted, however the internal counter had alredy been incremented. that sort of behaviour is fairly common.

the only time it becomes a problem is if you try an coerce some other meaning on an autonumber column. autonumber columns exists purely to make certain a unique value is available to the db. it should not be used in any situation where there is a demand outside the system for a sequential number, say for invoice numbers, GRN's delivery notes and so on. if thats what you need then you must create your own sequential number process.

darek82 · #3 (**permalink**) 12-10-10, 08:05

baburajv, what mysql version are you using? I tested it a minute ago, just to make sure. Any error doesn't increment auto_increment value.

it-iss.com · #4 (**permalink**) 12-10-10, 10:11

I have tried something similar here and do not have a problem:

Quote:

mysql> create table a(id int auto_increment, name varchar(255), primary key (id), unique key(name));
Query OK, 0 rows affected (0.08 sec)

mysql> desc a;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | UNI | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into a(name) values ('1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into a(name) values ('1');
ERROR 1062 (23000): Duplicate entry '1' for key 2
mysql> insert into a(name) values ('2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
+----+------+
2 rows in set (0.00 sec)

Internet Services

Web Development

Digital Marketing

Consumer Tech