Hi Paul, You can't update or delete any information in a column if there is a pk/fk mapping. SQL needs to maintain the integrity of the information and this is one of the ways it does it. If you want to keep the group name in the employees table why do you have the employees group at all. You need to normalize your database as this is the way databases should be designed (meaning splitting the employee information and the employee group information with links to the id's). Therefore you need to set it up like the example I gave you. Why do you want to avoid a union select? If it is the same as MSSQL you would use a "LEFT JOIN employees.emp_groupid ON employee_groups.grp_id" to get your information (this basically says include all the records from employees to all the groups/id's that referenced in employee_groups)
Just a note. I find this easier to work with. If you have employee_group.grp_id as the column name, use the same grp_id name in your employees table. Therefore if you go back at a later stage you know exactly where what links to. I hope this is clear Sonja -----Original Message----- From: Paul Larue [mailto:[EMAIL PROTECTED] Sent: 19 March 2003 04:13 AM To: [EMAIL PROTECTED] Subject: [wdvltalk] RE: [MySQL] InnoDB - Updating a parent table won't update the child table - HELP Sonja, I thought about that one but the result will be that if I want to have, let's say, a list of employees and their associated groups, I would have to make a UNION select on the 2 tables to get the associated employee_groups.grp_name since employees.emp_group only contains the ID of the group name. So only to avoid that, I would prefer to keep the actual pk/fk mapping. Your solution is somehow interesting since updating employee_groups.grp_name DOES NOT affect the employees table. What do you think is better? Keep the existing setup and bang my head against the wall to find out why it won't work or change to the one you proposed? Taking into consideration the issue I pointed out above (the UNION stuff). Thanks Paul -----Original Message----- From: Van Der Westhuizen, Sonja [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 5:35 AM To: [EMAIL PROTECTED] Subject: [wdvltalk] RE: [MySQL] InnoDB - Updating a parent table won't update the child table - HELP 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.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]
