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 PROTECTED]
