mysql - Default value for foreign key column - InnoDB -

take following sql fiddle:

http://sqlfiddle.com/#!2/ae0df/1

set @old_unique_checks=@@unique_checks, unique_checks=0; set @old_foreign_key_checks=@@foreign_key_checks, foreign_key_checks=0; set @old_sql_mode=@@sql_mode, sql_mode='traditional,allow_invalid_dates';   create table if not exists `vatbands` (   `vatbands_id` int unsigned not null auto_increment,   `code` enum('a', 'b', 'c', 'd', 'e', 'f') not null,   `client_id` int(11) unsigned not null,   primary key (`vatbands_id`, `code`, `client_id`),   index `vatcode_vatbands` (`code` asc, `client_id` asc)) engine = innodb;  create table if not exists `item` (   `item_id` int(11) unsigned not null auto_increment,   `client_id` int(11) unsigned not null comment 'customer id',   `vatcode` enum('a', 'b', 'c', 'd', 'e', 'f') default 'a',   primary key (`item_id`, `client_id`),   index `vatcode_item` (`vatcode` asc, `client_id` asc),   constraint `vatcode_item`     foreign key (`vatcode` , `client_id`)     references `sbs_node`.`vatbands` (`code` , `client_id`)     on delete no action     on update no action) engine = innodb;  set sql_mode=@old_sql_mode; set foreign_key_checks=@old_foreign_key_checks; set unique_checks=@old_unique_checks;  start transaction; insert `vatbands` (`client_id`, `code`) values ('1', 'a'); insert `item` (`client_id`, `vatcode`) values ('1', null); commit; 

why vatcode column 'null' when default value of 'a' specified?

the documentation of enum type states:

if enum column declared permit null, null value valid value column, , default value null. if enum column declared not null, default value first element of list of permitted values.

your insert using null value:

insert `item` (`client_id`, `vatcode`) values ('1', null); 

change create table statement to

create table if not exists `item` (   `item_id` int(11) unsigned not null auto_increment,   `client_id` int(11) unsigned not null comment 'customer id',   `vatcode` enum('a', 'b', 'c', 'd', 'e', 'f') not null,        -- no default, not null              primary key (`item_id`, `client_id`),   index `vatcode_item` (`vatcode` asc, `client_id` asc),   constraint `vatcode_item`     foreign key (`vatcode` , `client_id`)     references `vatbands` (`code` , `client_id`)     -- corrected 1     on delete no action     on update no action) engine = innodb; 

and insert statement to:

insert `item` (`client_id`, `vatcode`) values ('1', default); 

to result want, see my updated fiddle.