Hey folks -
I'm having a little problem understanding ON DELETE foreign key constraints. Here's my options from the manual:
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]When do I use each one? I can find anything in the online manual that suggests how to construct a database using this.
Let's take two examples, and here are my guesses:
1. A simple accounting database, with a clients, invoices, and line_items table.
My guess is that you would give invoices a foreign key in clients with ON DELETE CASCADE, meaning that when you delete a client record, it deletes its children in the invoices table. Also, you would give line_items a foreign key in the invoices table with ON DELETE CASCADE, so that deleting an invoice also causes its children in the line items table to be deleted. Is this right?
2. A class scheduling program, with a table of students, classes, and a 'merge' table called registration. That way we can have a many-to-many relationship, where one student has many classes, and one class has many students. The registration table would have only two foreign keys, and an index making them unique.
Hm, I seem to only want to have parent rows delete their children. When would I use these other key restraint types?
Steve Lefevre
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
