1,105,328 Community Members

Relational Database Queries - How to insert?

Member Avatar
dhatsah
Newbie Poster
21 posts since Oct 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

This is a school project which I am a bit lost on.

I have two tables with a relationship between

course

+-------------+----------------+
|id(primary)  |coursename      |
+-------------+----------------+

enrolled
+-------------+----------------+----------------+
|id(primary)  |studentName     |courseid(FK_from|
|             |                |course.id)      | 
+-------------+----------------+----------------+ 

I can insert a new course into the course table using the INSERT syntax however when I cannot find how I would go about inserting a new student that is enrolled onto the newly inserted course, I have looked at the MySQL docs and tried to hunt down tutorials, though none seem to be of any use. So if anyone could point me in the direction of tutorials that would be awesome.

Thanks in advance.

EDIT: After digging deeper - It would seem my understanding of FK is a bit warped. I was under the impression that I could Cascade on INSERT, but it only works on UPDATE and DELETE. Time to rethink.

LastMitch
Deleted Member
 
0
 

I can insert a new course into the course table using the INSERT syntax however when I cannot find how I would go about inserting a new student that is enrolled onto the newly inserted course, I have looked at the MySQL docs and tried to hunt down tutorials, though none seem to be of any use. So if anyone could point me in the direction of tutorials that would be awesome.

A query would be nice to see what you are talking about.

Member Avatar
diafol
Where are my eyes?
12,943 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
1
 

I think a better method would be:

students

student_id (PK)
firstname
surname
...

courses

course_id (PK)
title
description

enrolled

enrol_id (PK/optional)
student_id
course_id

This way a student can be enrolled on multiple courses (if that's useful). Also, you can add students and courses independently - which allows far more flexibility. Although you can add constraints (FKs), they didn't used to be implemented. MyISAM tables don't support constraints, and as these were the default table types then constraints weren't (couldn't) be used. InnoDB however does have support for constraints, and you'd do well to use them if possible. The newer versions of MySQL do default to InnoDB though and I think this engine now supports fulltext indexing, whereas it didn't before.

having said all that, I never had an issue with MyISAM tables and the lack of constraints.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: