【SQL文】PRIMARY KEYを複数設定したのですが反映されません。

Question

おこっていること

MAMPでmysqlとPHPを使用したアプリケーションを開発しているときにデータベースをphpmyadminからinsertなどしていた時に起こったことです。

データベース構造

カラム名	備考
id	NOT NULL AUTOINREMENT PRIMARYKEY
name	NOT NULL
mail	NOT NULL PRIMARY KEY
pass	NOT NULL
graduate_year	NOT NULL
role	NOT NULL
message

要はプライマリーキーを複数カラムに指定したいのですが、どうやらそれを行うには主キー制約というものが必要なようです。（間違っていたら指摘していただけると幸いです。）
そこで、 以下のようにsql文を発行してみました。

CREATE TABLE users( id int AUTO_INCREMENT NOT Null, mail varchar(100) NOT NULL,name varchar(30) NOT Null,pass varchar(100) NOT Null, graduate_year int NOT Null, role varchar(20) NOT Null, message varchar(100) NOT Null,PRIMARY KEY(id,mail))

作成後、インサート文で挿入してみると、mailの部分を重複させて登録させているのにも関わらず、成功してしまい同じメールアドレスが複数存在する状態になってしまっています。
これはもしかして、PRIMARY KEYを複数指定するとその指定したもの同士で重複をチェックするから、（＝毎回IDが違うから重複とみなされない）のでしょうか。
もしそうなのであれば、idとmailを別にPRIMARY KEYとして設定したいのですがどうすればいいのでしょうか。
教えていただけると幸いです。

したいこと

複数テーブルにPRIMARY KEYを設定したい。

Comments

[m.ts10806]

>複数テーブルに

「カラム」では？

[Y.H.]

> idとmailを別にPRIMARY KEYとして設定したい
これの目的は何でしょうか？
単純に、「idは重複させたくないし、mailも重複させたくない」なら、
mailにUNIQUE制約付ければよいのでは。

Answer 1

idとmailを別にPRIMARY KEYとして設定したい

PRIMARY KEYは一つしか設定できません。
一意性が別に必要な場合はUNIQUE制約にします。
UNIQUE制約は複数指定できます。
19.6.1 パーティショニングキー、主キー、および一意キー

CREATE TABLE users( 
  id              int AUTO_INCREMENT  NOT Null
, mail            varchar(100)        NOT NULL
, name            varchar(30)         NOT Null
, pass            varchar(100)        NOT Null
, graduate_year   int                 NOT Null
, role            varchar(20)         NOT Null
, message         varchar(100)        NOT Null
, PRIMARY KEY(id)
, UNIQUE  KEY(mail)
);

Comments

[jjs]

ありがとうございます。
ちなみに、ユニーク属性とプライマリーキーの違いって複数選択できるか否かという違いでしょうか。

[sazi]

プライマリーキーはデータアクセスの際の基本として扱われるのが、根本的な違いです。
結合条件などはプライマリーキーを使用する方が、無駄なアクセスを避けられます。

[jjs]

教えてくださりありがとうございます!

Answer 2

mailの部分を重複させて登録させているのにも関わらず

idがオートインクリメントなのでmail同じでもidが毎回インクリメントされて複合キーにより別のデータになります。
「mailが絶対に重複させない」仕様なのでしたらidを廃して（カラムも不要）
mailのみにPK貼りましょう。

Comments

[jjs]

idを設定している理由として、投稿データをDBに紐づける際に使用しようと考えていました。
この場合、どのようにすればよいでしょうか。

[m.ts10806]

mailが一意となるのですからmailを投稿データに紐づければいいのでは。
「mailが変更できる仕様だから変更されないカラムが欲しい」なら、mailそのままで更新時に関連データ全部更新するか、キーはidだけで登録前にバリデーションの一環でmailの重複チェックを行うかになると思います。
複合キーはオートインクリメント設置してる場合には向かないと思います。

[退会済みユーザー]

mailを含めた主キーの設定でなく、
idのみの主キー、mailは単独でユニークキーにするんじゃないの？

[m.ts10806]

あぁ。。。そっちかもしれませんね。
もうちょっと要件具体化したほうが良さそうですけど（質問に書いてないだけな気がしますが）