MySQL Bugs: #14353: Partitions: foreign key can't reference a partitioned table

Bug #14353	Partitions: foreign key can't reference a partitioned table
Submitted:	26 Oct 2005 20:13	Modified:	6 Dec 2007 14:47
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.2-alpha-debug	OS:	Linux (SUSE 10.0)
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
If I have a primary-key table that contains partitions, I
can't have a foreign-key table that references it.

How to repeat:
mysql> create table t1 (s1 int, primary key (s1)) engine=innodb partition by list (s1) (partition p1 values in (1), partition p2 values in (2));
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (s1 int, foreign key (s1) references t1 (s1)) engine=innodb;
ERROR 1005 (HY000): Can't create table './db88/t2.frm' (errno: 150)

This is a known limitation of partitioned tables in 5.1

So partitioned tables cannot be neither having foreign keys or being referenced by
foreign keys in other tables. I'll much sure that proper error messages exists in all
those cases and that the manual contains sufficient information about this fact.
I'll do this as part of this bug and put bug #17143 as duplicate

My earlier comment was:
"This is not a documented limitation, as far as I can tell."

If that is still true, then the bug is in 5.1,
not in a later version.

So I have changed status from "to be fixed later" back to "verified".

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html currently states:

     *Foreign keys*.   Partitioned tables do not support foreign keys. This 
     includes partitioned tables employing the InnoDB storage engine.

I have amended this to read as follows:

     *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.
            
     The scope of these restrictions includes partitioned tables employing the 
     InnoDB storage engine.

Though there were reports that the fix for this bug would resolve bug #14353, no such resolution appears to have been effected.

mysql 5.1.22-rc (root) [test]> create table txx3 (s1 int) engine=innodb partition by list (s1) (partition p1 values in (1));
Query OK, 0 rows affected (0.05 sec)

mysql 5.1.22-rc (root) [test]> alter table txx3 add constraint foreign key (s1) references txx1 (s1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.1.22-rc (root) [test]> show create table txx3\G
*************************** 1. row ***************************
       Table: txx3
Create Table: CREATE TABLE `txx3` (
  `s1` int(11) DEFAULT NULL,
  KEY `s1` (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY LIST (s1) (PARTITION p1 VALUES IN (1) ENGINE = InnoDB) */
1 row in set (0.02 sec)

The ALTER TABLE to add the FK constraint to txx3 appears to succeed, but the FK constraint is *not* reflected in a subsequent SHOW CREATE TABLE.

See bug #32948, which is an example of another FK/Partitioning problem.

I opened a separate bug, bug #33027, with regards to the uninformative error message generated in this case. Whether it's a documented limitation or not, it would be helpful if the error message actually explained the problem. Feel free to mark the new bug as a duplicate of this bug if you intend to fix this problem here now that this bug (#14353) has been reopened.

Changing category since the documentation issue seems to be fixed.

ALTER TABLE tp ADD CONSTRAINT FOREIGN KEY ...
is silently ignored for any partitioned table (tp), just like it would for a MyISAM table. (also, see Bug#22707)
As mentioned above, the request for changing error message is in Bug#33027.
Closing this bug again.