Mysql cannot insert record with unsigned primary key equal to zero

Question

I am trying to insert one record into the dim_channel table with zero for the primary key (unsigned int).

Mysql command:

INSERT INTO dim_channel set channel_id=0,name='Other',parent_channel_id=0,parent_channel_name='Other'; 

Results:

 select * from dim_channel; +------------+-------+-------------------+---------------------+ | channel_id | name | parent_channel_id | parent_channel_name | +------------+-------+-------------------+---------------------+ | 1 | Other | 0 | Other | +------------+-------+-------------------+---------------------+ 

Note that channel_id got a value of 1, not 0, as I expected.

Does anyone know why this is happening.

By the way, I can update the record as: update dim_channel set channel_id = 0 where channel_id = 1;

I just want to know why I cannot insert a record with channel_id = 0 in the first place.

Many thanks.

====== MySQL command for testing ====

- Create a table

 CREATE TABLE `dim_channel` ( `channel_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` char(80) DEFAULT NULL, `parent_channel_id` int(10) unsigned NOT NULL DEFAULT '0', `parent_channel_name` varchar(80) DEFAULT NULL, PRIMARY KEY (`channel_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 

- insert record

 INSERT INTO dim_channel set channel_id=0,name='Other',parent_channel_id=0,parent_channel_name='Other'; 

- see result

 select * from dim_channel; 

Answer 1

This is because in this field there is a primary key with automatic increment. If you assign NULL or 0 to this value when pasting, it will explicitly tell you the next number in the sequence for the table.

Answer 2

I know this is an old post, but anyway:

Using:

 SET sql_mode='NO_AUTO_VALUE_ON_ZERO'; 

before embedding the value 0 in your sizes.