mysql can't insert record with unsigned primary key being zero

Question

I am trying to insert one record into 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               |
+------------+-------+-------------------+---------------------+

Please note that channel_id got value 1, not 0 as I expected.

Any one knows why this happens.

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

Just want to know why I can't insert the record with channel_id=0 at the first place.

Thanks a lot.

====== MySQL command for you to test ====

-- Create 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

I know this is an old post but anyhow:

Use:

SET sql_mode='NO_AUTO_VALUE_ON_ZERO';

before inserting the 0 value in your Dimensions.

Answer 2

It is because you have an auto-increment primary key on that field. If you assign NULL or 0 for that value on insert it will explicitly give you the next number in the sequence for the table.