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.

Recommended Answers

All 2 Replies

Member Avatar for LastMitch

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 for diafol

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.