Paul, which MySQL version you are using?
http://www.innodb.com/ibman.html#InnoDB_foreign_keys " Corresponding ON UPDATE options are available starting from 4.0.8. " If you are using >= 4.0.8, can you create a small repeatable test case? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query ----- Original Message ----- From: ""Paul Larue"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, March 18, 2003 8:02 AM Subject: [MySQL] InnoDB - Updating a parent table won't update the child table > Hi all, > I created 2 tables in MySQl with the following statements > ================================================== > CREATE TABLE employees ( > emp_id INT NOT NULL AUTO_INCREMENT, > emp_last_name TINYTEXT NOT NULL, > emp_first_name TINYTEXT NOT NULL, > emp_nick_name TINYTEXT NOT NULL, > emp_date_joined DATE NOT NULL, > emp_date_left DATE, > emp_title TINYTEXT NOT NULL, > emp_group CHAR(50), > emp_address TINYTEXT NOT NULL, > emp_city TINYTEXT NOT NULL, > emp_phone CHAR(7) NULL, > emp_mobile CHAR(7) NULL, > emp_national_id CHAR(14) NOT NULL, > emp_social_security CHAR(8) NOT NULL, > emp_tax_ac CHAR(8) NULL, > PRIMARY KEY (emp_id), > KEY (emp_group), > FOREIGN KEY (emp_group) REFERENCES employee_groups(grp_name) > ON DELETE SET NULL > ON UPDATE CASCADE > ) > TYPE=InnoDB > COMMENT="Stores information about employees in company" > > CREATE TABLE employee_groups ( > grp_id INT UNSIGNED NOT NULL AUTO_INCREMENT, > grp_name CHAR(50) NOT NULL, > grp_overtime_paid BOOL NOT NULL, > PRIMARY KEY (grp_id), > INDEX (grp_name)) > TYPE=InnoDB > COMMENT="Stores group names and their properties" > ================================================== > The field employee_groups.grp_name is 'mapped' to employees.emp_group and > the referential integrity is set all the associated fileds to NULL when the > parent record is DLETED and to CASCADE when the parent record is UPDATED. > > Creating the tables is fine. Inserting data in the tables is fine too. But > when I try to update a record in employee_groups, MySQL returns the > following error > > mysql> UPDATE employee_groups SET grp_name = "FOO" WHERE grp_id = 1; > ERROR 1217: Cannot delete a parent row: a foreign key constraint fails > > Why is it telling me that I'm trying to delete the record when I'm only > doing a simple update? > > Deleting the records is ok, MySQl sets the associated fields to NULL. But > the update WON'T work... > > Any clue? > > Thanks in advance > > Paul > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
