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

[26 Oct 2005 20:13] Peter Gulutzan
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)
[21 Mar 2006 17:21] Mikael Ronström
This is a known limitation of partitioned tables in 5.1
[13 Apr 2006 13:44] Mikael Ronström
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
[11 Oct 2007 13:03] Peter Gulutzan
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".
[17 Oct 2007 8:09] Jon Stephens
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.
[4 Dec 2007 1:25] Kolbe Kegel
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.
[4 Dec 2007 1:26] Kolbe Kegel
See bug #32948, which is an example of another FK/Partitioning problem.
[6 Dec 2007 1:13] Kolbe Kegel
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.
[6 Dec 2007 13:27] Stefan Hinz
Changing category since the documentation issue seems to be fixed.
[6 Dec 2007 14:47] Mattias Jonsson
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.