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
