Can I use zero in a column which has the AUTO_INCREMENT attribute

Question

Hi, I'm using a MySQL 5.0 server. My requirement is to add one special row to an existing table which has an auto-increment primary key.

It would be very useful for future maintenance and management of the project if we were able to make the id of this row 0 (because it's easy to remember and easy to spot in manual observations).

Now, I know that MySQL has no problem with you using your own value for an autoincrement column, and my tests have shown that I can set the autoincrement primary key of a row to 0 with an UPDATE query. However, some concerns have been raised about how this might affect the auto-increment functionality of the column in future INSERTs.

My (limited) experiments have shown nothing strange and I can't find anything specific warning against this in the MySQL docs. That is, apart from this (emphasis mine): http://dev.mysql.com/doc/refman/5.0/en/create-table.html

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.

I am unable to find an explanation for what is wrong with having a value of zero in an AUTO_INCREMENT column, so can anyone tell me if having an AUTO_INCREMENT column that contains 0 is a bad thing?

Answer 1

As you have already discovered, it's not possible to asign a 0 to an auto increment field with an INSERT, you need to use an UPDATE. AFAIK there is nothing wrong with having a 0 in a row except when you try to dump and import. But that can be avoided by first inserting the data and then later marking it as an auto increment field.