本文介绍了mysql外键约束错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我有这些表: CREATE TABLE `faculty_personal_info`(`id` int ( 11 ) NOT NULL AUTO_INCREMENT,`Designation` varchar ( 30 ) NOT NULL ,`First_Name` varchar ( 20 ) NOT NULL ,`Middle_Name` varchar ( 20 ) DEFAULT NULL ,`Last_Name` varchar ( 20 )不 NULL ,`Date_of_Birth` date NOT NULL ,`地址` varchar ( 100 ) NOT NULL ,`City` varchar ( 30 ) NOT NULL ,`Pincode` int ( 11 ) NOT NULL ,`Email`` span class =code-keyword> varchar ( 40 ) NOT NULL ,`Contact_Number` int ( 11 )不是 NULL , PRIMARY KEY (`id`) USING BTREE )ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8 创建 表`faculty_edu_qual`(`id` int ( 11 ) NOT NULL AUTO_INCREMENT,` faculty_id` int ( 11 ) NOT NULL , `度数` varchar ( 15 ) NOT NULL ,`Specialisation` varchar ( 40 ) NOT NULL , `University` varchar ( 40 ) NOT NULL ,`等级` varchar ( 10 ) NOT NULL ,`La year_of_Passing` int ( 11 ) NOT NULL , PRIMARY KEY (`id`) USING BTREE, KEY `FK_faculty_edu_qual_faculty_personal_info`(`faculty_id`), CONSTRAINT `FK_faculty_edu_qual_faculty_personal_info` FOREIGN KEY (`faculty_id`) REFERENCES `faculty_personal_info`(` id`) ON DELETE CASCADE ON UPDATE CASCADE )ENGINE = InnoDB DEFAULT CHARSET = utf8 我已成功在教师个人信息表中插入记录。现在当我使用这个命令时, 设置@faculty_id = last_insert_id(); 插入faculty_equ_qual值('1',@ faculty_id,'B.Tech',' CSE','PEC','S','2015'); 我收到此错误: ERROR 1452 ( 23000 ):无法添加或更新子行:外键约束f ails(`faculty_student``faculty_edu_qual`,CONSTRAINT`FK_faculty_edu_qual_facu lty_personal_info` FOREIGN KEY(`faculty_id`)参考文献`faculty_personal_info` (`id`) ON DELETE CASCADE ON UPDATE CA) 解决方案 如果您先将记录插入 faculty_personal_info 表中,然后立即插入 faculty_edu_qual 表查询应该有效。验证您之间没有运行任何插入查询。 如果您按顺序插入记录并仍然得到相同的错误,那么请确保您有身份为该列设置的值,否则您将获得变量@faculty_id的空值 I have these tables:CREATE TABLE `faculty_personal_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Designation` varchar(30) NOT NULL, `First_Name` varchar(20) NOT NULL, `Middle_Name` varchar(20) DEFAULT NULL, `Last_Name` varchar(20) NOT NULL, `Date_of_Birth` date NOT NULL, `Address` varchar(100) NOT NULL, `City` varchar(30) NOT NULL, `Pincode` int(11) NOT NULL, `Email` varchar(40) NOT NULL, `Contact_Number` int(11) NOT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8CREATE TABLE `faculty_edu_qual` ( `id` int(11) NOT NULL AUTO_INCREMENT, `faculty_id` int(11) NOT NULL, `Degree` varchar(15) NOT NULL, `Specialisation` varchar(40) NOT NULL, `University` varchar(40) NOT NULL, `Grade` varchar(10) NOT NULL, `Year_of_Passing` int(11) NOT NULL, PRIMARY KEY (`id`) USING BTREE, KEY `FK_faculty_edu_qual_faculty_personal_info` (`faculty_id`), CONSTRAINT `FK_faculty_edu_qual_faculty_personal_info` FOREIGN KEY (`faculty_id`) REFERENCES `faculty_personal_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8I have inserted a record in the faculty personal info table successfully. Now when I use this commands,set @faculty_id = last_insert_id();insert into faculty_equ_qual values('1',@faculty_id,'B.Tech','CSE','PEC','S','2015');I get this error:ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`faculty_student`.`faculty_edu_qual`, CONSTRAINT `FK_faculty_edu_qual_faculty_personal_info` FOREIGN KEY (`faculty_id`) REFERENCES `faculty_personal_info` (`id`) ON DELETE CASCADE ON UPDATE CA) 解决方案 If you are inserting record into faculty_personal_info table first and then immediately thereafter you are inserting into faculty_edu_qual table the query should work. Verify that you are not running any insert queries in between.In case you are inserting record sequentially and still getting the same error, then please make sure that you have identity value set for the column else you will be getting null value for variable @faculty_id 这篇关于mysql外键约束错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!