Can someone help type up my correct SQL code? I\'ve been getting this error: 1)
ID: 3904205 • Letter: C
Question
Can someone help type up my correct SQL code? I've been getting this error:
1)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`course`, CONSTRAINT `course_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `Department` (`dept_id`))
2)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`section`, CONSTRAINT `section_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `Course` (`course_id`))
3)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`registration`, CONSTRAINT `registration_ibfk_2` FOREIGN KEY (`section_id`) REFERENCES `Section` (`section_id`))
--------------
SQL Insert Code;
INSERT INTO Course VALUES ('AC265', 'Accounting', 3, 3, 'EN100');
INSERT INTO Section VALUES (1101, 'AC265', 01, 'WN17', 97302, 'MW', 30, '09:00:00', '10:30:00', 13);
INSERT INTO Registration VALUES (24746, 1102, 'B', 'B');
------------------
Create table
/* Query 2 */
CREATE TABLE Course (
course_id VARCHAR(15) PRIMARY KEY,
title VARCHAR(30) NOT NULL,
credits INT,
dept_id INT,
prerequisite VARCHAR(30),
FOREIGN KEY (dept_id)
REFERENCES Department (dept_id)
);
/* Query 6 */
CREATE TABLE Section (
section_id INT PRIMARY KEY,
course_id VARCHAR(15),
section_number INT NOT NULL,
term_id VARCHAR(15),
faculty_id INT,
day VARCHAR(3),
max_count INT NOT NULL,
start_time time,
end_time time,
room_id INT,
FOREIGN KEY (course_id)
REFERENCES Course(course_id),
FOREIGN KEY (term_id)
REFERENCES Term(term_id),
FOREIGN KEY (faculty_id)
REFERENCES Faculty (faculty_id)
);
/* Query 7 */
CREATE TABLE Registration (
student_id INT,
section_id INT,
midterm_grade VARCHAR(5),
final_grade VARCHAR(5),
PRIMARY KEY (student_id, section_id),
FOREIGN KEY (student_id)
REFERENCES Student (student_id),
FOREIGN KEY (section_id)
REFERENCES Section (section_id)
);
------------------------
Explanation / Answer
Since University table is having foreign key relationship with Course, Section and Registration tables first of all we need to have a coresspondng row in university table before we insert into one of these tables.that is first the paret table needs to be updated berfore inserting it into child table.
Example: in the above example we are inerting a recored into course table with courseId 'AC265' before inserting this university table should have corresponding row with this courseid.