Re: Foreign Keys Question

'Twas brillig, and Chris at 12/12/08 01:20 did gyre and gimble:

Micah Gersten wrote:

Colin Guthrie wrote:

The ON DELETE CASCADE option is key here... "DELETE FROM students where student_id=1" will remove all traces of that student from the db... all the course they've attended, all the instructors who have taught them etc. keeps things nice and tidy without having to put the structure in your code all over the place. Col

Why would you want to delete the instructors when deleting the student?

I think he meant the link between the student & instructor (in the student_instructor table), not the instructor itself.

lol, indeed, that's what I meant... Sorry I thought it was implied in the context! Say you have the following layouts instructors: instructor_id, name students: student_id, name instructor_students: instructor_id, student_id This structure would hold a list of instructors and a list of studends and also a one to many mapping of instructors to students. If you delete a student the FK can cascade to the instructor_students table and thus delete the records that indicate a given instructor (or instructors) taught them. Col -- Colin Guthrie gmane(at)colin.guthr.ie http://colin.guthr.ie/ Day Job: Tribalogic Limited [http://www.tribalogic.net/] Open Source: Mandriva Linux Contributor [http://www.mandriva.com/] PulseAudio Hacker [http://www.pulseaudio.org/] Trac Hacker [http://trac.edgewall.org/]