Menu
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?
867
You don't have to specify NOT NULL on the column definition with AUTO_INCREMENT . You can leave it off, and MySQL will make the column NOT NULL . And if you specify NULL in place of NOT NULL , MySQL will accept the syntax, but it will ignore that, and make the column NOT NULL anyway.
In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
By default, the AUTO_INCREMENT column begins at 1. You can also explicitly assign 0 to the column to generate the next sequence number unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. Assigning NULL to the column will also generate the next sequence number, as long as the column was declared NOT NULL.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
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.
172
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
