CODEWITHSUNDEEP
mysqlforeign-keys
Lalhriatpuii Mapuii
Lalhriatpuii Mapuii

Reputation: 85

Troubleshooting unmatched foreign keys in mysql tables

I have two myIsam tables student and fee which were later changed into InnoDB engine and assigned foreign key to section field in fee table which points to section field in student table with UPDATE and DELETE CASCADE. My problem is that the existing section field values does not match with the existing student section values. This happened because before I had changed my engine, I had updated student table section field, and thought that section field values in fee table would also changed when I converted both the tables and assigned foreign keys to section field in both the tables. I am not sure whether synchronization will solve it. I am newbie and have no idea how I would solve this. Please help me.

Fee table:

CREATE TABLE IF NOT EXISTS `fee` (
  `f_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `regd` bigint(20) unsigned NOT NULL,
  `Class` varchar(20) COLLATE latin1_general_ci NOT NULL,
  `Section` varchar(10) COLLATE latin1_general_ci NOT NULL,
  `Amount` int(11) NOT NULL,
  `Balance` int(11) NOT NULL,
   PRIMARY KEY (`f_id`),
   KEY `Section` (`Section`),
  KEY `Rollno` (`Rollno`),
  KEY `regd` (`regd`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci 
 AUTO_INCREMENT=2500;

ALTER TABLE `fee`
  ADD CONSTRAINT `fee_ibfk_1` FOREIGN KEY (`Section`) 
  REFERENCES `student` (`Section`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fee_ibfk_2` FOREIGN KEY (`Rollno`) 
  REFERENCES `student` (`Roll_no`) ON DELETE CASCADE ON UPDATE CASCADE;

Student Table:

CREATE TABLE IF NOT EXISTS `student` (
  `regd` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE latin1_general_ci NOT NULL,
  `Date_of_birth` date NOT NULL,
  `Sex` varchar(20) COLLATE latin1_general_ci NOT NULL,
  `Roll_no` int(11) DEFAULT NULL,
  `Section` varchar(20) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`regd`),
  KEY `Section` (`Section`),
  KEY `Roll_no` (`Roll_no`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci 
 AUTO_INCREMENT=2500;

Upvotes: 0

Views: 169

Answers (1)

Arth
Arth

Reputation: 13110

Unless you have some record of which fee should point to which student, I'm afraid you can't do this.

You'll have to recreate the links; either using logs of which UPDATEs you ran, from a backup or manually.

Upvotes: 1

Related Questions