on update cascade problem

[rhinoferoce9]

Hello all.

Here are simplified versions of my two tables:

CREATE TABLE players (name VARCHAR(50) PRIMARY KEY NOT NULL) TYPE = INNODB;

CREATE TABLE deaths (killer VARCHAR(50) NOT NULL, victim VARCHAR(50) NOT NULL, dateofdeath DATE NOT NULL, PRIMARY KEY (killer, victim, dateofdeath), INDEX (killer), FOREIGN KEY (killer) REFERENCES players (name) ON UPDATE CASCADE ON DELETE CASCADE, INDEX (victim), FOREIGN KEY (victim) REFERENCES players (name) ON UPDATE CASCADE ON DELETE CASCADE) TYPE = INNODB;

N.B.: When killer = victim, it means it was a suicide.

Let's say I insert Bob, Bill and Paul in players.

Now, in deaths I could have something like this:

Bob, Bill, 2005-08-01
Bob, Paul, 2005-08-01
Bill, Paul, 2005-08-02
Bill, Bob, 2005-08-02
...
Bob, Bob, 2005-08-03
...

If I decide to change Bill's name (in table players) there will not be any problem.

But if I decide to change Bob's name (in table players) I will get a 1216 error because of the record (Bob, Bob, 2005-08-03).

I am pretty sure my relational model is OK and I made the same experience in MS-Access and it worked.

Can someone help?