| Bug #46474 | Foreign keys: some forms of cascading changes are disallowed | ||
|---|---|---|---|
| Submitted: | 30 Jul 2009 11:03 | Modified: | 20 Dec 2013 6:51 |
| Reporter: | Dmitry Lenev | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[30 Jul 2009 12:25]
MySQL Verification Team
Thank you for the bug report. Verified as described.
miguel@lara:~/dbs/6.1stage$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.1.0-alpha-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set storage_engine= Falcon;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> create table t1 (pkfk int primary key);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t1 values (1);
Query OK, 1 row affected (0.01 sec)
mysql> create table t2 (pk int primary key, fk int references t1 (pkfk));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t2 values (1, 1);
Query OK, 1 row affected (0.01 sec)
mysql> alter table t1 add constraint c foreign key (pkfk) references t2 (pk)
-> on update cascade;
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> # Fails with ER_FK_CHANGE_BY_CASCADE although it can be executed
mysql> # without problems.
mysql> update t2 set pk = 2, fk = 2;
ERROR 1809 (HY000): Foreign key error: cascading action will change table 't1' which is also used elsewhere in statement
mysql> drop tables t1, t2;
Query OK, 0 rows affected (0.10 sec)
mysql>
[20 Dec 2013 6:51]
Erlend Dahl
6.x project was abandoned years ago.
Description: I'm running MySQL server built from mysql-6.1-fk-stage tree in --foreign-key-all-engines=1 mode. I have created two tables each of which references another and foreign key defined on one of which has ON UPDATE CASCADE as referential action. When trying to perform an UPDATE on one of those table that will require using simultaneous using another table for check as parent and for cascading action as a child I get unexpected ER_FK_CHANGE_BY_CASCADE error. There is no apparent reason for prohibiting such kind of updates. See How-to-repeat for details. How to repeat: set storage_engine= Falcon; create table t1 (pkfk int primary key); insert into t1 values (1); create table t2 (pk int primary key, fk int references t1 (pkfk)); insert into t2 values (1, 1); alter table t1 add constraint c foreign key (pkfk) references t2 (pk) on update cascade; # Fails with ER_FK_CHANGE_BY_CASCADE although it can be executed # without problems. update t2 set pk = 2, fk = 2; drop tables t1, t2;