2

I'm learning mysql and I try to create 2 tables which name student and course and column idnumber from student table and std_id from course table is foreign key.But when I want insert data to each of table,I got "error 1452<23000>cannot add or update a child row". These are my tables: student:

CREATE TABLE student(
name VARCHAR(20) NOT NULL,
idnumber INT(3) NOT NULL,
email VARCHAR(50) NOT NULL,
phone INT(10) NOT NULL,
PRIMARY KEY (idnumber));

CREATE TABLE course(
course_id INT(5) NOT NULL,
std_id INT(3) NOT NULL,
std_name VARCHAR(20) NOT NULL,
course_name VARCHAR(40) NOT NULL,
score INT(2) NOT NULL,
PRIMARY KEY (course_id),
FOREIGN KEY (std_id) REFERENCES student(idnumber));

ALTER TABLE student
ADD FOREIGN KEY (idnumber)
REFERENCES course(std_id);

this is my code in mysql.I am be grateful if someone help me.

I'm apologize for my bad English.

user3789719
  • 104
  • 1
  • 10
  • yes,I copy/paste from mysql.:) – user3789719 Jul 26 '14 at 21:21
  • You have not given all the relevant information. Show the table values and update code that caused the error. Read the link on how to ask a good question [on this page](https://stackoverflow.com/help). – philipxy Jul 26 '14 at 21:22
  • possible duplicate of [ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails](http://stackoverflow.com/questions/13891879/error-1452-23000-cannot-add-or-update-a-child-row-a-foreign-key-constraint-f) – philipxy Jul 26 '14 at 21:23
  • Also search on the error message before you ask a question. – philipxy Jul 26 '14 at 21:24
  • thanks for all comment. – user3789719 Jul 26 '14 at 21:26

1 Answers1

2

You've introduced a nice problem. With your ALTER TABLE you made sure that no row could inserted, if both tables are empty.

  • Either you begin with a student, then you must have a valid course, because you said that the column idnumber that references the courses table must not be NULL.
  • Or you begin with a course, then you must have a valid student, because you said that the column std_id must not be NULL.

The main issue is, that one student can only take part in one course - and each course can have only one student. In fact, a student can take part in several courses - and each course can have several students: a many-to-many relation

The solution for this is a junction table:

  • remove the column idnumber from your student table
  • remove the column std_id from your courses table
  • create a new table, say student2courses with the columns i.e. id_student and id_courses that references the student and courses table, with an unique index on (id_student, id_courses).

Now you can insert new students without having courses and insert new courses without having students and do the assignment of students to courses when needed.

Community
  • 1
  • 1
VMai
  • 10,156
  • 9
  • 25
  • 34