php - Not able to assign On Delete Set null and on updated set Null in mysql -

bank_api_uat_user table

create table if not exists `bank_api_uat_user` (   `id` int(11) not null auto_increment,   `bank_name` varchar(255) default null,   `role` varchar(10) not null,   `bank_code` char(10) not null,   `user_name` varchar(255) not null,   `password` varchar(20) not null,   `api_key` varchar(255) not null,   `client_secret` varchar(255) not null,   primary key (`id`),   key `user_name` (`user_name`),   key `user_name_2` (`user_name`),   key `id` (`id`,`user_name`),   key `role` (`role`) ) engine=innodb  default charset=latin1 auto_increment=36 ; 

create_role table

create table if not exists `create_role` (   `date` datetime not null,   `role_name` varchar(50) not null,   `role_code` varchar(5) not null,   primary key (`role_code`) ) engine=innodb default charset=latin1; 

i want assign referential integrity bank_api_uat_user table each time add constraint gives below error mysql said: documentation

1215 - cannot add foreign key constraint

below query used create foreign key.

alter table `bank_api_uat_user` add  constraint `const_file_role` foreign key (`role`) references `test`.`create_role`(`role_code`) on delete set null on update set null; 

its important make child column null setting null reference option.

the above queries work if role declared null

create table if not exists `bank_api_uat_user` (   `id` int(11) not null auto_increment,   `bank_name` varchar(255) default null,   `role` varchar(10) null,   `bank_code` char(10) not null,   `user_name` varchar(255) not null,   `password` varchar(20) not null,   `api_key` varchar(255) not null,   `client_secret` varchar(255) not null,   primary key (`id`),   key `user_name` (`user_name`),   key `user_name_2` (`user_name`),   key `id` (`id`,`user_name`),   key `role` (`role`) ) engine=innodb  default charset=latin1 auto_increment=36 ; 

full explanation can find here : https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html