Foreign Key Error: Cannot Add or Update a Child Row

[atikah8890]

Hi all. I'm developing a quiz application consisting of multiple choice questions. Here are the tables that I've come up with.

CREATE TABLE IF NOT EXISTS `student` (
  `s_id` int(10) NOT NULL,
  `s_name` varchar(30) NOT NULL,
  `s_email` varchar(30) NOT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `instructor` (
  `i_id` int(6) NOT NULL,
  `i_name` varchar(30) NOT NULL,
  `i_email` varchar(30) NOT NULL,
  PRIMARY KEY (`i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `result` (
  `r_id` int(6) NOT NULL AUTO_INCREMENT,
  `s_id` int(10) NOT NULL,
  `i_id` int(6) NOT NULL,
  `r_status` text NOT NULL,
  `r_score` decimal(6,0) NOT NULL,
  PRIMARY KEY (`r_id`),
  UNIQUE KEY `s_id` (`s_id`),
  UNIQUE KEY `i_id` (`i_id`)
  ADD CONSTRAINT `result_ibfk_1` FOREIGN KEY (`s_id`) REFERENCES `student` (`s_id`),
  ADD CONSTRAINT `result_ibfk_2` FOREIGN KEY (`i_id`) REFERENCES `instructor` (`i_id`);
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `eval_set` (
  `set_id` int(11) NOT NULL AUTO_INCREMENT,
  `r_id` int(11) NOT NULL,
  `q_id` int(11) NOT NULL,
  `response` varchar(255) NOT NULL,
  `created` date NOT NULL,
  PRIMARY KEY (`set_id`),
  UNIQUE KEY `r_id` (`r_id`,`q_id`),
  KEY `q_id` (`q_id`)
  ADD CONSTRAINT `eval_set_ibfk_1` FOREIGN KEY (`r_id`) REFERENCES `result` (`r_id`),
  ADD CONSTRAINT `eval_set_ibfk_2` FOREIGN KEY (`q_id`) REFERENCES `question` (`q_id`);
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `question` (
  `q_id` int(10) NOT NULL AUTO_INCREMENT,
  `a_id` int(6) NOT NULL,
  `cr_id` int(6) NOT NULL,
  `q_qstn_no` int(11) NOT NULL,
  `q_text` varchar(100) NOT NULL,
  `q_chpt` int(11) NOT NULL,
  PRIMARY KEY (`q_id`),
  UNIQUE KEY `a_id` (`a_id`,`cr_id`),
  KEY `cr_id` (`cr_id`)
  ADD CONSTRAINT `question_ibfk_2` FOREIGN KEY (`cr_id`) REFERENCES `chapter_rel` (`cr_id`),
  ADD CONSTRAINT `question_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `answer` (`a_id`);
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE IF NOT EXISTS `answer` (
  `a_id` int(6) NOT NULL AUTO_INCREMENT,
  `q_id` int(10) NOT NULL,
  `a_text` varchar(255) NOT NULL,
  `a_value` tinyint(1) NOT NULL,
  PRIMARY KEY (`a_id`),
  UNIQUE KEY `q_id` (`q_id`)
  ADD CONSTRAINT `answer_ibfk_1` FOREIGN KEY (`q_id`) REFERENCES `question` (`q_id`);
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `chapter_rel` (
  `cr_id` int(6) NOT NULL AUTO_INCREMENT,
  `q_id` int(10) NOT NULL,
  `chapter_no` int(6) NOT NULL,
  PRIMARY KEY (`cr_id`),
  UNIQUE KEY `q_id` (`q_id`)
  ADD CONSTRAINT `chapter_rel_ibfk_1` FOREIGN KEY (`q_id`) REFERENCES `question` (`q_id`);
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

There foreign key error occurred when I want to add q_text in 'question'. I'm aware there are two FKs (a_id and cr_id) whose parent tables are not yet populated. How do I go about it? The way I see it, I need to insert the questions first, then the multiple choice of answers and related chapters (hence the separate tables).

Would omitting the two FKs in 'question' and just have the q_id in 'answer' and 'chapter_rel' work? I'm using phpmyadmin and all the constraints are set to ON DELETE RESTRICT and ON UPDATE RESTRICT.

Hope someone can point me in the right direction. Thank you.

Regards,
Atikah

[adam_k]

You can skip the 2 FK and still join your tables. In fact you can omit all FKs and still be just fine. They are there to make sure that the user will enter valid data or that relative data won't be left behind when deleting or that an update will "destroy" the links between 2 tables.
In your case you really don't want to set a FK in table question pointing to table answer.

[atikah8890]

In fact you can omit all FKs and still be just fine.

Were you referring to table question specifically or the database in general? I'm going through the database schema and see that I've used FKs in quite a few places (eg. q_id in tables eval_set, answer and chapter_related). I've removed the two FKs in table question but so far maintained q_id as FKs in the three tables above. Haven't had much luck when it comes to database so I wanted to clarify this. Thanks! :)

[adam_k]

I was talking about the whole database. And I'll repeat it just to make it clear: "They are there to make sure that the user will enter valid data or that relative data won't be left behind when deleting or that an update will "destroy" the links between 2 tables."

The constraints you are adding are not part of the join itself. It's an added measure that ensures that your data will be "correct". There are other ways to achieve this, or in same cases there are no correct and wrong data.
In example table1 has column1 that holds the id available in table2, column2. You can join the 2 tables without constraint:

select * from table1 inner join table2 on column1 = column2 

What the constraint will do is not allow you to insert or update a value in column1 that doesn't exist in column2.
It won't join the 2 tables on its own and just because you've got a FK doesn't mean that your joins will be perfect.

When I'm saying that you don't want a FK pointing to the answer, that is because you don't have answers yet, so it would be imposible to ask the questions (you can't insert a value in column1=question that doesn't exist in column2=answer).

[atikah8890]

Thank you for the explanation, Adam. I used JOIN like you suggested and it worked. Obviously I need to brush up on things and you've helped a lot. Appreciate it, thanks again.

Regards,
Atikah