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

Question

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 to assign referential integrity to bank_api_uat_user table each time i add constraint it gives below error MySQL said: Documentation

1215 - Cannot add foreign key constraint

Below query used to 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;

i have made both the columns identical still its giving the same error. — Jasshh Andrews, Mar 28 '17 at 11:35
Jasshh, welcome to stackoverflow. You can add an answer to your own question, just so you know. However I would try to provide detail why the column had to be Not Null if you choose to do so. (Basically if you make it a complete, well explained answer it makes it useful for the next person that might come along and find your question!) — Cody Ｇ, Mar 28 '17 at 12:02
@CodyG. Thanks for the beautiful explanation. I'll keep that in mind from next onward. — Jasshh Andrews, Mar 29 '17 at 05:54

score 0 · Accepted Answer · answered Mar 29 '17 at 06:00

Its important to make a child column Null for setting null reference option.

the above queries work if role is declared as 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 be find here : https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

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

1215 - Cannot add foreign key constraint

1 Answers1