質問: 複数カラムに対するUNIQUEインデックスが、特定のレコードに対して機能しません。 PHPで作成したプログラムで扱うために、phpMyAdminを使用して作成したテーブルがあります。 エクスポート(SQL)した内容は次の通りです。 ``` CREATE TABLE `グループ` ( `group_id` bigint(20) UNSIGNED NOT NULL, `num` int(11) NOT NULL COMMENT '並び順', `name` char(20) NOT NULL COMMENT '名称', `region` int(11) NOT NULL COMMENT '拠点', `del` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ``` ``` ALTER TABLE `グループ` MODIFY `group_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5; ``` ``` INSERT INTO `グループ` (`group_id`, `num`, `name`, `region`, `del`) VALUES (1, 1, ' 班1', 1, NULL), (2, 2, '班2', 1, NULL), (3, 3, '班3', 1, NULL), (4, 4, '班4', 2, NULL); ``` group_idはデータレコードを一意に特定するために付与しています。 numは表示順を入れ替えることを想定して付与しています。必ず整数値です。 (上記の例で班3を班2の上に表示したい場合、班3のレコードのnumを2に、班2のレコードのnumを3に入れ替える) regionには必ず整数値を入力します。 delはデフォルトでNULLです。該当レコードを使用しなくなった場合にフラグを立ててSELECT対象から除外するためのカラムです。 このテーブルに対し、 ``` ALTER TABLE `グループ` ADD PRIMARY KEY (`group_id`), ADD UNIQUE KEY `TEST` (`name`,`region`) USING BTREE; ``` を設定し、同一regionでの同一nameを重複として受け付けないようにします。 これで、班3を班2に書き換えようとすると、 ``` SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '班2-1' for key 'TEST' ``` と返されますが、班4を班3に書き換えることはできます。 一見、狙い通りかと思うのですが、group_id=1のデータのみは、他のレコードとの重複を無視して好きなように編集ができてしまいます。 (1, 1, ' 班1', 1, NULL), (2, 2, '班2', 1, NULL), (3, 3, '班3', 1, NULL), (4, 4, '班4', 2, NULL); 上記の状態から、班1を班2や3に書き換えることも、班2や3を班1に書き換えることもできてしまうのです。 (班4を班1に書き換えることはregionが異なるのでOKですが、regionとnameがどちらも同じなのは困るのです) また、新たにレコードを追加する場合も、name=班1、region=1が追加できてしまいます。 しかし、一度追加して、 (1, 1, ' 班1', 1, NULL), (2, 2, '班2', 1, NULL), (3, 3, '班3', 1, NULL), (4, 4, '班4', 2, NULL); (5, 5, ' 班1', 1, NULL), この状態になってから更に name=班1、region=1 を追加しようとすると撥ねられます。 このことから、UNIQUEかどうかの検査対象からgroup_id=1のレコードが漏れていると思われます。 編集前にPHPでデータを読み出して検査するなんていう強引な手法も考えましたが・・・そんなことしなくても、MySQL側で本来は解決できるはずですよね。。 解決策・何か試すべき方法その他、助言をいただければ幸いです。
回答: > > ADD UNIQUE KEY `TEST` (`name`,`region`) USING BTREE; > > > ですから、 > > (2, 2, '班2', 1, NULL), > > > をDELETEするなり、UPDATE で他の UNIQUE KEY に変更しないで、 >これで、班3を班2に書き換えようとすると、 でUPDATE するからUNIQUE制約でエラーになるだけです。 >上記の状態から、班1を班2や3に書き換えることも、班2や3を班1に書き換えることもできてしまうのです。 現象を再現できるように、質問にきちんと実行したUPDATE文で載せては?