MySQL Bugs: #44273: Possible to create FK associations for partitioned tables

Bug #44273	Possible to create FK associations for partitioned tables
Submitted:	14 Apr 2009 18:12	Modified:	27 Jan 2012 8:06
Reporter:	Thorsten Kunz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.1.10 beta	OS:	Any
Assigned to:	Maksym Yehorov	CPU Architecture:	Any
Tags:	foreign key, partitions

Description:
MySQL Workbench allows to create FK constraints for tables with manual partitions. But MySQL does not support that and the execution of the resulting SQL script will fail. Here is the restriction description from the MySQL online manual chapter 18.5.: 

Foreign keys not supported. Partitioned tables do not support foreign keys. This means that:

   1. Definitions of tables employing user-defined partitioning may not contain foreign key references to other tables.

   2. No table definition may contain a foreign key reference to a partitioned table.

How to repeat:
- create two tables
- setup a FK relation between them
- manually put some kind of partition on each table
-> the SQL script generated from this model won't work

Suggested fix:
Make partitions and any kind of FK constraint mutual exclusive for tables

Many thanks for pointing this out.

Verified as described.

A validation should be added for this case

Added validation for the case.

This was fixed in 5.2.26, and here's the new changelog entry:

A validation module (a SE feature only) has been added. It sends
an alert if foreign keys are being used in or to a partitioned
table. Note: partitioned tables do not support foreign keys.