Deleting Database entries and associated Junction table data in MYSQL and PHP

Question

A have a situation where Lessons are created, teachers are assigned to teach said lessons, and students can then book them. These are both many to many. Lesson <-> Teachers and Lesson<->Students. There is a lesson table and two junction tables linking them.

Here is the structure:

Lesson Table - lesson.lessonID
Lesson/Student Junction Table - lsj.lessonFK 
Lesson/Teacher Junction Table - ltj.lessonFK

I'm looking to delete a lesson from the lessons table but can't because of the junction table associations. I've tried deleting the associations first and then the lesson but it doesn't seem to work.

DELETE
FROM junc_lesson_teacher, junc_lesson_student, lesson
JOIN user ON user.userID = junc_lesson_teacher.teacherFK
JOIN lesson ON lesson.lessonID = junc_lesson_teacher.lessonFK
WHERE lessonID = {$id}//PHP $_GET id from a form

What is the best way to go about deleting a lesson with teacher and student associations? Thanks for any input!

EDIT:

I've also tried sending this as a query but it doesn't seem to work:

DELETE FROM junc_lesson_teacher WHERE junc_lesson_teacher.lessonFK = {$id};
DELETE FROM junc_lesson_student WHERE junc_lesson_student.lessonFK = {$id};
DELETE FROM lesson WHERE lesson.lessonID = {$id};

What I understood from the above query is that you want to delete the records from the Lesson table and its associated entry from the student and teacher table. So I guess you need to first delete the records from the Student and teacher table and then delete the record from Lesson table — Prals
Basically what I thought too, however the query didn't work... See edit. — Guy

Justin Paul Paño Justin Paul Paño · Accepted Answer · 2013-12-22T15:40:09

That query can be executed successfully if you will alter the foreign key behavior of your tables. Go to this link and take not of these keywords below in the CREATE TABLE statement. This also has other variants in ON DELETE NO ACTION and ON DELETE SET NULL, among others that I may have not known yet.

ON DELETE CASCADE

This is also applicable with an ALTER TABLE statement if you have built your schema already. But be careful because once you deleted a row that has many associated foreign key relationships with other tables. Those rows are also deleted from other tables.

Deleting Database entries and associated Junction table data in MYSQL and PHP

1 Answers