Bug #58696 Fields don't update their values in a foreign key update
Submitted: 3 Dec 2010 13:37 Modified: 3 Dec 2010 19:29
Reporter: Mohammad Saberi Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S1 (Critical)
Version:5.0.30 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: foreign key, UPDATE, workbench

[3 Dec 2010 13:37] Mohammad Saberi
Description:
Hi.
I have 2 tables that have a relation between each other.
+-----------+
| tbllogs:  |
+-----------+
| log_id    |
| user_id   |
| log_action|
+-----------+

+-----------+
| tblusers: |
+-----------+
| user_id   |
| user_name |
+-----------+

I made a foreign key in tbllogs on user_id to tblusers.user_id on update cascade.
But when I change tblusers.user_id, any change occur on tbllogs.user_id
What's the matter ?
Is it a bug ?

How to repeat:
.
[3 Dec 2010 18:21] MySQL Verification Team
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.
[3 Dec 2010 19:29] Mohammad Saberi
Let's see the generated code on tblpersonnels and tbllogs:

/////////////////////////////////////////////////////
-- -----------------------------------------------------
-- Table `dbcartable`.`tblpersonnels`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dbcartable`.`tblpersonnels` (
`uid` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`p_code` VARCHAR(50) NOT NULL ,
`p_name` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`uid`) ,
UNIQUE INDEX `code_UNIQUE` (`p_code` ASC) ,
INDEX `fk_personnel_units` (`unit_id` ASC) ,
CONSTRAINT `fk_personnel_units`
FOREIGN KEY (`unit_id` )
REFERENCES `dbcartable`.`tblunits` (`unit_id` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_persian_ci;

-- -----------------------------------------------------
-- Table `dbcartable`.`tbllog`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dbcartable`.`tbllog` (
`log_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`uid` SMALLINT UNSIGNED NOT NULL ,
`log_date` VARCHAR(10) NULL ,
`log_en_date` DATE NULL ,
`log_hour` TIME NOT NULL ,
`log_ip` VARCHAR(15) NOT NULL ,
`log_action` VARCHAR(3) NOT NULL ,
`log_des` VARCHAR(500) NULL ,
`log_status` VARCHAR(1) NOT NULL ,
PRIMARY KEY (`log_id`) ,
INDEX `fk_loxs_personnels` (`uid` ASC) ,
CONSTRAINT `fk_loxs_personnels`
FOREIGN KEY (`uid` )
REFERENCES `dbcartable`.`tblpersonnels` (`uid` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_persian_ci;
/////////////////////////////////////////////////////

I expect that when I change a uid from tblpersonnels, all tbllogs.uid that are matched with it update themselves. But any change don't happen when I do it.