Hi Paul, I hope I understand correctly (little sleep can make you see things)...
In MSSQL when a record gets updated it "deletes" it first and then it "inserts" the new information. To us it looks like it just updated the information. So I assume it will happen the same in MySQL. The reason you cant update the record is becuase you "mapped" (setting up primary and secondary key contraints - employees.emp_group and employee_groups.grp_name). I suggest you rather set up your constraints using employees.emp_group (make it an INTEGER) and employee_groups.grp_id. That way if you want to update the group name you only have to do it in employee_groups.grp_name. While you are updating your employees table with the group information you can drop the constraints and then activating it again when you are finished. Im not quite sure what the MySQl commands for that will be. I hope this helps. Sonja -----Original Message----- From: Paul Larue [mailto:[EMAIL PROTECTED] Sent: 19 March 2003 02:29 AM To: [EMAIL PROTECTED] Subject: [wdvltalk] RE: [MySQL] InnoDB - Updating a parent table won't update the child table - HELP Me again, Anybody can help? Paul -----Original Message----- From: Paul Larue [mailto:[EMAIL PROTECTED] Sent: Thursday, March 20, 2003 9:58 AM To: [EMAIL PROTECTED] Subject: [wdvltalk] [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 ____ * The WDVL Discussion List from WDVL.COM * ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to %%email.unsub%% ____ * The WDVL Discussion List from WDVL.COM * ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to %%email.unsub%% ___________________________________________________________________________________________________ The views expressed in this email are, unless otherwise stated, those of the author and not those of the FirstRand Banking Group or its management. The information in this e-mail is confidential and is intended solely for the addressee. Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted in reliance on this, is prohibited and may be unlawful. Whilst all reasonable steps are taken to ensure the accuracy and integrity of information and data transmitted electronically and to preserve the confidentiality thereof, no liability or responsibility whatsoever is accepted if information or data is, for whatever reason, corrupted or does not reach its intended destination. ________________________________ ____ • The WDVL Discussion List from WDVL.COM • ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
