Problem
Suppose there is a column in a table which is set to auto-increment in MySQL.
Now when you try to insert a record with 0 in auto-increment column, it will save the record with the next higher value of that column, but NOT 0.
For Example,
you have a table student with 2 columns, studentid and name with studentid set to auto-increment.
Suppose there are two records already present in the table with their studentid values as 1 and 2.
Now when you execute the following query,
INSERT INTO `student` (`studentid`,`name`) VALUES(0, ‘codippa’);
The record created with this query will have studentid as 3 and not 0 which we wanted.
Reason
When a column is set to auto-increment in MySQL, then whenever a record is inserted with the value of that column as NULL or 0, MySQL generates its value automatically depending on the highest value of this column for the records already present in the table.
MySQL ignores 0 for such columns and inserts a value generated on its own.
Solution
In order to insert 0 in an auto-increment value, a solution is to set NO_AUTO_VALUE_ON_ZERO sql_mode property.
SQL modes affects the syntax and validation checks that MySQL(or any database) performs before data insertion.
Normally MySQL generates a sequence number for auto generated columns whenever a record is inserted.
This is done when 0 or NULL values are inserted in auto generated columns.
Setting NO_AUTO_VALUE_ON_ZERO SQL mode suppresses this behavior for 0 so that only NULL value generates a sequence number. This means that 0 can be inserted into an auto generated column.
In order to set NO_AUTO_VALUE_ON_ZERO SQL mode, execute the following query at MySQL command execution prompt :
SET sql_mode=’NO_AUTO_VALUE_ON_ZERO’;
From MySQL documentation,
NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns.
Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it.
NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.
