CODEWITHSUNDEEP
mysqlauto-increment
Paddy O'Loughlin
Paddy O'Loughlin

Reputation: 1702

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

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?

Upvotes: 6

Views: 1271

Answers (2)

rubo77
rubo77

Reputation: 20865

Storing 0 is not a recommended practice. For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump now automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.

Upvotes: 1

e4c5
e4c5

Reputation: 53774

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.

Upvotes: 3

Related Questions