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
