Cannot delete foreign key constraint fails

Question

When I try to delete the question from the test, the sql show :

 `Cannot delete or update a parent row: a foreign key constraint fails (`oes`.`studentquestion`,     
 CONSTRAINT `studentquestion_ibfk_2` FOREIGN KEY (`testid`, `qnid`) REFERENCES `question` 
 (`testid`, `qnid`))`

Here will be my table:

student table

 CREATE TABLE `student` ( 
 `stdid` bigint(20) NOT NULL, 
 `subid` int(11) NOT NULL, 
 `stdname` varchar(40) default NULL, 
 `stdpassword` varchar(40) default NULL, 
 `emailid` varchar(40) default NULL, 
 `contactno` varchar(20) default NULL, 
 `address` varchar(40) default NULL, 
 `city` varchar(40) default NULL, 
 `pincode` varchar(20) default NULL, 
 PRIMARY KEY (`stdid`), 
 UNIQUE KEY `stdname` (`stdname`), 
 UNIQUE KEY `emailid` (`emailid`) 
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

studentquestion table

  CREATE TABLE `studentquestion` ( 
  `stdid` bigint(20) NOT NULL default '0', 
  `testid` bigint(20) NOT NULL default '0', 
  `qnid` int(11) NOT NULL default '0',
 `answered` enum('answered','unanswered','review') DEFAULT NULL,
 `stdanswer` enum('optiona','optionb','optionc','optiond') DEFAULT NULL,
  PRIMARY KEY (`stdid`,`testid`,`qnid`), 
  KEY `testid` (`testid`,`qnid`) 
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

studenttest table

    `stdid` bigint(20) NOT NULL default '0', 
    `testid` bigint(20) NOT NULL default '0', 
    `starttime` timestamp NOT NULL default CURRENT_TIMESTAMP, 
    `endtime` timestamp NOT NULL default '0000-00-00 00:00:00', 
    `correctlyanswered` int(11) default NULL, 
    `status` enum('over','inprogress') default NULL, 
    PRIMARY KEY (`stdid`,`testid`), 
     KEY `testid` (`testid`) 
     ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

question table

      CREATE TABLE `question` ( 
      `testid` bigint(20) NOT NULL default '0', 
       `qnid` int(11) NOT NULL default '0', 
       `question` varchar(500) default NULL, 
      `optiona` varchar(100) DEFAULT NULL,
      `optionb` varchar(100) DEFAULT NULL,
      `optionc` varchar(100) DEFAULT NULL,
      `optiond` varchar(100) DEFAULT NULL,
       `correctanswer` enum ('optiona','optionb','optionc','optiond') DEFAULT NULL,
        `marks` int(11) DEFAULT NULL,
       PRIMARY KEY (`testid`,`qnid`) 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

test table

      CREATE TABLE `test` ( 
     `testid` bigint(20) NOT NULL, 
     `testname` varchar(30) NOT NULL, 
     `testdesc` varchar(100) default NULL, 
     `testdate` date default NULL, 
     `testtime` time default NULL, 
     `subid` int(11) default NULL, 
     `testfrom` timestamp NOT NULL default CURRENT_TIMESTAMP,
     `testto` timestamp NOT NULL default '0000-00-00 00:00:00', 
     `duration` int(11) default NULL, 
     `totalquestions` int(11) default NULL, 
     `attemptedstudents` bigint(20) DEFAULT NULL,
     `testcode` varchar(40) NOT NULL, 
     `tcid` bigint(20) default NULL, 
     `minimunscore` int(11) NOT NULL, 
     PRIMARY KEY (`testid`), 
     UNIQUE KEY `testname` (`testname`), 
     KEY `test_fk1` (`subid`), 
     KEY `test_fk2` (`tcid`) 
     ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

subject table

     CREATE TABLE `subject` ( 
     `subid` int(11) NOT NULL, 
     `subname` varchar(40) default NULL, 
     `subdesc` varchar(100) default NULL, 
     `tcid` bigint(20) default NULL, 
     PRIMARY KEY (`subid`), 
     UNIQUE KEY `subname` (`subname`), 
     KEY `subject_fk1` (`tcid`) 
     ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

     ALTER TABLE `studentquestion` 
     ADD CONSTRAINT `studentquestion_ibfk_1` FOREIGN KEY (`stdid`) REFERENCES `student`                              
     (`stdid`),   
     ADD CONSTRAINT `studentquestion_ibfk_2` FOREIGN KEY (`testid`, `qnid`) REFERENCES `question`              
     (`testid`, `qnid`); 
     ALTER TABLE `studenttest`
     ADD CONSTRAINT `studenttest_ibfk_1` FOREIGN KEY (`stdid`) REFERENCES `student` (`stdid`), 
     ADD CONSTRAINT `studenttest_ibfk_2` FOREIGN KEY (`testid`) REFERENCES `test` (`testid`); 
     ALTER TABLE `question` 
     ADD CONSTRAINT `question_ibfk_1` FOREIGN KEY (`testid`) REFERENCES `test` (`testid`); 
     ALTER TABLE `test` 
     ADD CONSTRAINT `test_fk1` FOREIGN KEY (`subid`) REFERENCES `subject` (`subid`), 
     ADD CONSTRAINT `test_fk2` FOREIGN KEY (`tcid`) REFERENCES `testconductor` (`tcid`); 
     ALTER TABLE `subject` 
     ADD CONSTRAINT `subject_fk1` FOREIGN KEY (`tcid`) REFERENCES `testconductor` (`tcid`);

     INSERT INTO `adminlogin` VALUES ('001','root','root');

     INSERT INTO `studenttest` (`stdid`, `testid`, `starttime`, `endtime`, `correctlyanswered`,          
     `status`) VALUES
     (1, 1, '2014-10-15 09:11:24', '2014-10-15 09:21:24', 0, 'over');

     INSERT INTO `subject` (`subid`, `subname`, `subdesc`, `tcid`) VALUES
     (1, 'fref', 'few', NULL);

     INSERT INTO `test` (`testid`, `testname`, `testdesc`, `testdate`, `testtime`, `subid`,          
     `testfrom`, `testto`, `duration`, `totalquestions`, `attemptedstudents`, `testcode`, `tcid`) 
     VALUES

     (1, 'gregre', 'greger', '2014-10-15', '17:08:16', 1, '2014-10-15 03:08:16', '2014-10-16          
     15:59:59', 10, 2, 0, '.»Å', NULL);

When the student has complete the test, then the question will not be able to delete. Neither the test or subject.

you can use `ON DELETE CASCADE` and `ON UPDATE CASCADE` on child table. You can get better idea for [http://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete]. — Hardik Visa, Oct 15 '14 at 10:14

score 0 · Answer 1 · answered Oct 15 '14 at 10:09

It is because the studentquestion table will be left with a foreign key that points to a non existent record. You'll either need to remove rows which have a foreign key to the record you want to delete or, alternatively set up CASCADE rules so that when parent rows are deleted action can be taken.

Cannot delete foreign key constraint fails

1 Answers1