Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Unanswered: Question on auto increment

    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
    Cheers....

    baburajv

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2010
    Posts
    4
    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.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I have tried something similar here and do not have a problem:
    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)
    Last edited by it-iss.com; 12-10-10 at 11:24.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •