CODEWITHSUNDEEP
mysql
munsifali
munsifali

Reputation: 1731

Cannot add or update a child row: a foreign key constraint fails mysql

CREATE TABLE `class` (
  `class_id` int(11) NOT NULL AUTO_INCREMENT,
  `section_name` varchar(50) NOT NULL,
  `class_alias` varchar(200) NOT NULL,
  `grading_scheme` int(11) NOT NULL DEFAULT '0',
  `year` year(4) NOT NULL,
  `grade_calc_method_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=48819 DEFAULT CHARSET=latin1;



CREATE TABLE `teachers` (
  `teacher_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `teacher_subject` varchar(20) NOT NULL DEFAULT 'None',
  PRIMARY KEY (`teacher_id`),
  KEY `user_id` (`user_id`,`school_id`)
) ENGINE=InnoDB AUTO_INCREMENT=48606 DEFAULT CHARSET=latin1;

CREATE TABLE `teacher_classes` (
  `teacher_class_id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`teacher_class_id`),
  UNIQUE KEY `teacher_id_class_id` (`teacher_id`,`class_id`),
  KEY `teacher_id` (`teacher_id`,`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=46707 DEFAULT CHARSET=latin1;

Trying to insure data consistency between the tables by using foreign key so that the DBMS can check for errors.I have another junction table teacher_classes
Here is my query to add foreign keys constraint

 ALTER TABLE teacher_classes
 ADD CONSTRAINT `tc_fk_class_id` FOREIGN KEY (`class_id`)
 REFERENCES class (`class_id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
 ADD CONSTRAINT `tc_fk_teacher_id` FOREIGN KEY (`teacher_id`)
 REFERENCES teachers (`teacher_id`) ON UPDATE NO ACTION ON DELETE NO ACTION;

've seen the other posts on this topic, but no luck, getting following error.

Cannot add or update a child row: a foreign key constraint fails (DB_NAME.#sql-403_12, CONSTRAINT tc_fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teachers (teacher_id) ON DELETE NO ACTION ON UPDATE NO ACTION)

Upvotes: 1

Views: 4269

Answers (2)

Hassan Ahsan
Hassan Ahsan

Reputation: 33

Too late to Answer. I just had the same problem the solution is easy.

You're getting this error because you're trying to or UPDATE a row to teacher_classes doesn't match the id in table teachers.

A simple solution is disable foreign key checks before performing any operation on the table.

SET FOREIGN_KEY_CHECKS = 0;

After you are done with the table enable it again.

SET FOREIGN_KEY_CHECKS = 1;

Or you can remove not null constraint and insert a NULL value in it.

Upvotes: 3

Rahul
Rahul

Reputation: 77876

That's most probably the column definition doesn't match properly. For table teachers the PK column definition is as below.

`teacher_id` int(11) NOT NULL AUTO_INCREMENT

Make sure you have the same definition in your child table teacher_classes

Upvotes: 1

Related Questions