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

    Unanswered: Question on auto increment


    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?



  2. #2
    Join Date
    Nov 2004
    out on a limb
    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
    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
    San Sebastian, Spain
    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; 12-10-10 at 10:24.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    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