List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 23 2002 9:31pm
Subject:Re: InnoDB 3.23.52, foreign keys and update/cascade problem
View as plain text  
At 12:10 -0700 9/23/02, j.random.programmer wrote:
>Hi all:
>
>I'm seeing a strange problem updating a field if
>that field is referenced as a FK in another table.
>
>Consider:
>
>CREATE TABLE A (
>  id                   INTEGER NOT NULL AUTO_INCREMENT,
>  name                 VARCHAR(20),
>  PRIMARY KEY (id)
>);
>
>CREATE TABLE B (
>  id                   INTEGER NOT NULL AUTO_INCREMENT,
>  AID                  INTEGER,
>  name                 VARCHAR(20),
>  PRIMARY KEY (id),
>  INDEX(AID) ,
>  FOREIGN KEY (AID) REFERENCES A  (id)
>   ON UPDATE CASCADE
>);

I believe that ON UPDATE CASCADE doesn't work yet.

>
>
>mysql> insert into A values
>(null, 'one'), (null, 'two');
>
>mysql> select * from A;
>+----+------+
>| id | name |
>+----+------+
>|  1 | one  |
>|  2 | two  |
>+----+------+
>2 rows in set (0.00 sec)
>
>mysql> insert into B values
>(null, 1, 'hello'), (null, 1, 'world');
>
>
>mysql> select * from B;
>+----+------+-------+
>| id | AID  | name  |
>+----+------+-------+
>|  1 |    1 | hello |
>|  2 |    1 | world |
>+----+------+-------+
>2 rows in set (0.00 sec)
>
>AND HERE IS THE PROBLEM:
>
>mysql> update A set id = 5 where id = 1;
>ERROR 1217: Cannot delete a parent row: a foreign key
>constraint fails
>
>---- Note, the innodb manual says: ----
>If you defined ON DELETE CASCADE or SET NULL and
>updated the referenced key in the parent row, InnoDB
>deleted or updated the child row. This is now changed
>to conform to SQL-92: you get the error 'Cannot delete
>parent row'.
>---------------------------------------------------
>
>NOTE, There is no 'ON DELETE' constraint in my example
>so what on earth is going on ?
>
>Can anyone explain why an update is not getting
>cascaded ? I mean, that's the whole POINT of the
>update constraint. What am I doing wrong ?
>
>Best regards,
>
>--j

Thread
mysql_install_db not createdDaniel) (Blalock8 Sep
  • mysql_install_db not createdMichael Widenius8 Sep
  • Re: optimizer bug in selecting fields that donĀ“t belong to the index used by mysql/innodbMichael Widenius18 Sep
  • InnoDB 3.23.52, foreign keys and update/cascade problemj.random.programmer22 Sep
    • Re: InnoDB 3.23.52, foreign keys and update/cascade problemj.random.programmer23 Sep
      • Re: InnoDB 3.23.52, foreign keys and update/cascade problemPaul DuBois24 Sep