Description:
According to documentation athttp://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html and bug #14353 it should not be possible to create a foreign key constraint that references a partitioned table.
I assume this is not supported because is can cause some problems. However, it's still possible to create tables in a way that a foreign key references a partitioned table.
Executing the statements in this order causes an error to be issued:
create table t1 (id int, primary key (id)) engine=innodb;
alter table t1 partition by hash(id) partitions 5;
create table t2 (id int, primary key (id), foreign key (id) references t1 (id)) engine=innodb;
mysql 5.1.22-rc (root) [test]> create table t1 (id int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql 5.1.22-rc (root) [test]> alter table t1 partition by hash(id) partitions 5;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql 5.1.22-rc (root) [test]> create table t2 (id int, primary key (id), foreign key (id) references t1 (id) on delete cascade) engine=innodb;
ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150)
But it's possible to execute the statements in a different order, creating a situation where a partitioned table is referenced by a foreign key:
create table t1 (id int, primary key (id)) engine=innodb;
create table t2 (id int, primary key (id), foreign key (id) references t1 (id)) on delete cascade engine=innodb;
alter table t1 partition by hash(id) partitions 5;
mysql 5.1.22-rc (root) [test]> create table t1 (id int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.04 sec)
mysql 5.1.22-rc (root) [test]> create table t2 (id int, primary key (id), foreign key (id) references t1 (id)) on delete cascade engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql 5.1.22-rc (root) [test]> alter table t1 partition by hash(id) partitions 5;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
After partitioning t1, inserts into t2 will fail and changes to t1 will not cascade to t2. That's a big problem, and it's unexpected from the user's side since they were able to successfully partition t1.
How to repeat:
create table t1 (id int, primary key (id)) engine=innodb;
create table t2 (id int, primary key (id), foreign key (id) references t1 (id) on delete cascade ) engine=innodb;
alter table t1 partition by hash(id) partitions 5;
insert into t1 values (1),(2),(3);
insert into t2 values (1),(2),(3);
alter table t1 remove partitioning;
insert into t2 values (1),(2),(3);
alter table t1 partition by hash(id) partitions 5;
delete from t1;
select * from t2;
mysql 5.1.22-rc (root) [test]> create table t1 (id int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql 5.1.22-rc (root) [test]> create table t2 (id int, primary key (id), foreign key (id) references t1 (id) on delete cascade ) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql 5.1.22-rc (root) [test]> alter table t1 partition by hash(id) partitions 5;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql 5.1.22-rc (root) [test]> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 5.1.22-rc (root) [test]> insert into t2 values (1),(2),(3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE)
mysql 5.1.22-rc (root) [test]> alter table t1 remove partitioning;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 5.1.22-rc (root) [test]> insert into t2 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 5.1.22-rc (root) [test]> alter table t1 partition by hash(id) partitions 5;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 5.1.22-rc (root) [test]> delete from t1;
Query OK, 3 rows affected (0.00 sec)
mysql 5.1.22-rc (root) [test]> select * from t2;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
Suggested fix:
Since partitioning and foreign keys quite clearly do not "play well" together, it should not be possible to partition a table that is referenced by any foreign key.
Permitting this leads to surprising results when inserting data and serious data integrity issues when updating rows in the parent table.
Possibly related to bugs #17143 and #14353.
