3

Possible Duplicate:
How to force MySQL to take 0 as a valid auto-increment value

I know I can do an update on an existing row, but is it possible to INSERT a row with the id 0?

mysql> create table inc( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, sz TEXT);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into inc(id, sz) select 25, "a";
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into inc(id, sz) select 25, "a";
ERROR 1062 (23000): Duplicate entry '25' for key 'PRIMARY'
mysql> insert into inc(id, sz) select 27, "b";
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into inc(id, sz) select -5, "c";
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into inc(id, sz) select 0, "d";
Query OK, 1 row affected (0.15 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from inc;
+----+------+
| id | sz   |
+----+------+
| -5 | c    |
| 25 | a    |
| 27 | b    |
| 28 | d    |
+----+------+
4 rows in set (0.00 sec)
Community
  • 1
  • 1
  • 2
    http://stackoverflow.com/questions/1142472/how-to-force-mysql-to-take-0-as-a-valid-auto-increment-value – whardier Oct 07 '12 at 02:40

1 Answers1

3

From http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

"No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers."

So it seems using 0 just tells it to auto_increment.

But if you are assigning ID numbers, I'm not sure why you have defined the column to use AUTO_INCREMENT?

mary
  • 174
  • 10
  • 1
    You can assign 0, see post here for how: http://stackoverflow.com/questions/1142472/how-to-force-mysql-to-take-0-as-a-valid-auto-increment-value – CrazyCasta Oct 07 '12 at 02:57
  • 1
    Ah ha you got it. Remove AUTO_INCREMENT. I personally think 0 should either be 0 no matter what or an error. But seeing that other sql engines can do negative and 0 it should just insert 0 rather then IGNORE WHAT I SAY and give it a different value. –  Oct 07 '12 at 03:11