First, I create a student table in mysql to store student information
<code>CREATE TABLE IF NOT EXISTS `student`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT
);
</code>
<code>CREATE TABLE IF NOT EXISTS `student`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT
);
</code>
CREATE TABLE IF NOT EXISTS `student`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT
);
Then, another courses table was created to store the student’s course information
<code>CREATE TABLE IF NOT EXISTS `courses`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
price DOUBLE NOT NULL DEFAULT 0
);
</code>
<code>CREATE TABLE IF NOT EXISTS `courses`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
price DOUBLE NOT NULL DEFAULT 0
);
</code>
CREATE TABLE IF NOT EXISTS `courses`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
price DOUBLE NOT NULL DEFAULT 0
);
Finally, I wanted to create a join between the `student` and `courses` tables, so I added two foreign keys to the newly created table, but mysql prompted me that the creation failed, and I searched a lot and couldn’t find the problem. Thanks for any help you can give me!
<code>CREATE TABLE
IF
NOT EXISTS `student_select_courses` (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
FOREIGN KEY ( student_id ) REFERENCES student ( id ) ON UPDATE CASCADE ON DELETE CASCADED,
FOREIGN KEY ( course_id ) REFERENCES courses ( id ) ON UPDATE CASCADE ON DELETE CASCADED
);
</code>
<code>CREATE TABLE
IF
NOT EXISTS `student_select_courses` (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
FOREIGN KEY ( student_id ) REFERENCES student ( id ) ON UPDATE CASCADE ON DELETE CASCADED,
FOREIGN KEY ( course_id ) REFERENCES courses ( id ) ON UPDATE CASCADE ON DELETE CASCADED
);
</code>
CREATE TABLE
IF
NOT EXISTS `student_select_courses` (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
FOREIGN KEY ( student_id ) REFERENCES student ( id ) ON UPDATE CASCADE ON DELETE CASCADED,
FOREIGN KEY ( course_id ) REFERENCES courses ( id ) ON UPDATE CASCADE ON DELETE CASCADED
);
error message
<code>1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASCADED,
FOREIGN KEY ( course_id ) REFERENCES courses ( id ) ON UPDATE CASCAD' at line 7
</code>
<code>1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASCADED,
FOREIGN KEY ( course_id ) REFERENCES courses ( id ) ON UPDATE CASCAD' at line 7
</code>
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASCADED,
FOREIGN KEY ( course_id ) REFERENCES courses ( id ) ON UPDATE CASCAD' at line 7
successful creation of a relational table
New contributor
Xu Han is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
The issue in your SQL script is with the use of CASCADED instead of CASCADE. The correct syntax should be ON DELETE CASCADE.
Here is the corrected code:
<code>CREATE TABLE IF NOT EXISTS `student_select_courses` (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
FOREIGN KEY (student_id) REFERENCES student(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
);
</code>
<code>CREATE TABLE IF NOT EXISTS `student_select_courses` (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
FOREIGN KEY (student_id) REFERENCES student(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
);
</code>
CREATE TABLE IF NOT EXISTS `student_select_courses` (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
FOREIGN KEY (student_id) REFERENCES student(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
);
2