MySQLパーティショニングは外部キーと一緒に使えない＆おまけの外部キー削除方法

パーティショニングした時に制約に引っかかったのでメモ。

あるテーブルで5000万レコードぐらいのデータを流し込む必要があったので、テーブルを作成する時にパーティションで分割しようと考えた。

例

CREATE TABLE `hoge` (
  `key` int(10) unsigned NOT NULL,
  `key2` varchar(10) NOT NULL,
  `test1` varchar(50) DEFAULT NULL,
  `test2` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`key`),
  KEY `hoge_FK1` (`key2`)
);

CREATE TABLE `piyo` (
  `key2` varchar(10) NOT NULL,
  `test4` varchar(50) DEFAULT NULL,
  `test5` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`key2`),
  KEY `hoge_FK1` (`key2`),
  CONSTRAINT `hoge_FK1` FOREIGN KEY (`key2`) REFERENCES `hoge` (`key2`) ON DELETE CASCADE ON UPDATE CASCADE
)
  PARTITION BY HASH(`key2`) PARTITIONS 500;

下のpiyoを流したときに以下のエラーが。
ERROR 1506 (HY000): Foreign key clause is not yet supported in conjunction with partitioning

どうやら制約で外部キーが使えない模様。。

設計上外部キーを消すわけにはいかなかったので諦めることに・・。しょんぼり。

おまけ

これを行っている時に、テーブルを作成後に外部キーを削除する方法を見つけたのでメモ。

１．SHOW CREATE TABLEで外部キーの確認。

mysql> SHOW CREATE TABLE piyo;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| piyo  | CREATE TABLE `piyo` (
  `key2` varchar(10) NOT NULL,
  `test4` varchar(50) DEFAULT NULL,
  `test5` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`key2`),
  KEY `hoge_FK1` (`key2`),
  CONSTRAINT `hoge_FK1` FOREIGN KEY (`key2`) REFERENCES `hoge` (`key2`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

２．外部キーの名前が「hoge_FK1」と分かったのでこれを削除するには以下のようにする。

ALTER TABLE piyo DROP FOREIGN KEY hoge_FK1;

３．外部キーを再度付与したい場合は以下のようにする。

ALTER TABLE piyo ADD CONSTRAINT hoge_FK1 FOREIGN KEY (key2) REFERENCES hoge(key2) ON DELETE CASCADE ON UPDATE CASCADE;