List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 27 1999 6:46pm
Subject:Re: auto_increment behaviour
View as plain text  
At 8:46 PM +0300 10/27/99, Indrek Siitan wrote:
>Hey,
>
>is this documented behaviour? and if it is, then why?
>
>CREATE TABLE tmp (
>  id int not null auto_increment primary key,
>  something varchar(255)
>);
>
>INSERT INTO tmp VALUES (0,'blah');
>Query OK, 1 row affected (0.02 sec)

For an AUTO_INCREMENT column, inserting 0 has the same effect as
inserting NULL.  So this generates a key with value 1.

>
>INSERT INTO tmp VALUES (1,'bulbul');
>ERROR 1062: Duplicate entry '1' for key 1
>
>SELECT * FROM tmp;
>+----+-----------+
>| id | something |
>+----+-----------+
>|  1 | blah      |
>+----+-----------+
>1 row in set (0.00 sec)

That's correct; you already have a row with a key value of 1.

>
>shouldn't auto_increment field automatically insert the value
>only if the column is not included in the INSERT statement or
>left NULL?

It does (or if you insert 0).  But your column is a PRIMARY KEY, so it cannot
contain duplicate values and you already have a row with a
key value of 1.

>
>if i do UPDATE tmp SET id=0 where id=1, i can insert the
>second row.

Sure, it'll automatically insert the next AUTO_INCREMENT value.


-- 
Paul DuBois, paul@stripped
Thread
auto_increment behaviourIndrek Siitan27 Oct
  • Re: auto_increment behaviourPaul DuBois27 Oct
  • Re: auto_increment behaviourBob Kline27 Oct
RE: auto_increment behaviourIndrek Siitan27 Oct
  • RE: auto_increment behaviourMichael Widenius8 Nov