質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

phpMyAdmin

phpMyAdminはオープンソースで、PHPで書かれたウェブベースのMySQL管理ツールのことです。

Q&A

解決済

2回答

1094閲覧

MySQL 複数カラムに対するUNIQUEインデックス

Take_it

総合スコア357

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

phpMyAdmin

phpMyAdminはオープンソースで、PHPで書かれたウェブベースのMySQL管理ツールのことです。

0グッド

0クリップ

投稿2019/02/21 14:20

編集2019/02/21 14:25

複数カラムに対するUNIQUEインデックスが、特定のレコードに対して機能しません。

PHPで作成したプログラムで扱うために、phpMyAdminを使用して作成したテーブルがあります。
エクスポート(SQL)した内容は次の通りです。

MySQL

1CREATE TABLE `グループ` ( 2 `group_id` bigint(20) UNSIGNED NOT NULL, 3 `num` int(11) NOT NULL COMMENT '並び順', 4 `name` char(20) NOT NULL COMMENT '名称', 5 `region` int(11) NOT NULL COMMENT '拠点', 6 `del` int(11) DEFAULT NULL 7) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL

1ALTER TABLE `グループ` 2 MODIFY `group_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

MySQL

1INSERT INTO `グループ` (`group_id`, `num`, `name`, `region`, `del`) VALUES 2(1, 1, ' 班1', 1, NULL), 3(2, 2, '班2', 1, NULL), 4(3, 3, '班3', 1, NULL), 5(4, 4, '班4', 2, NULL);

group_idはデータレコードを一意に特定するために付与しています。
numは表示順を入れ替えることを想定して付与しています。必ず整数値です。
(上記の例で班3を班2の上に表示したい場合、班3のレコードのnumを2に、班2のレコードのnumを3に入れ替える)
regionには必ず整数値を入力します。
delはデフォルトでNULLです。該当レコードを使用しなくなった場合にフラグを立ててSELECT対象から除外するためのカラムです。

このテーブルに対し、

MySQL

1ALTER TABLE `グループ` 2 ADD PRIMARY KEY (`group_id`), 3 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側で本来は解決できるはずですよね。。

解決策・何か試すべき方法その他、助言をいただければ幸いです。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

(1, 1, ' 班1', 1, NULL)

ってデータで1を2に変えても班の前に空白があるから他とは被りませんね~。

投稿2019/02/21 14:38

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Take_it

2019/02/21 14:46

!!!!!!!! お恥ずかしい限りです。 空白を除去したところ、意図した挙動になりました。 データの登録・編集共に、前後の余分な空白を除去する処理を咬ませるようにします。。 ありがとうございました!
guest

0

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文で載せては?

投稿2019/02/21 14:33

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問