[MySQL]主キーを複数に与えたい(複合主キーを作りたい)

Question

やりたいこと

MariaDBでテーブル作成を作成しており、

create ~ (
number int aut_increment,
name varchar(20),
~
);

この二つ(number, name)を主キーにしたいのですが、

create ~ (
number int auto_increment,
name varchar(20),
~,
primary key(number, name)
);

このように書いたところ、Incorrect table definition; there can be only one auto column and it must be defined as a keyと怒られてしまいました。
いろいろなサイトを参考にしてみたのですが、上記の方法以外記載されていなかったので、わかるかたがいらっしゃいましたら教えていただきたいです。

Comments

[maisumakun]

どのような構文エラーが出たのでしょうか。

[maisumakun]

あと、「AUTO_INCREMENTな列を一部に含んだ複合主キー」が必要な場面がわからないのですが、どのような理由でそうしたいのでしょうか。

[sazi]

それは、nameで重複があるから一つに纏めたいのか、同じnumberでも異なるnameを管理したいのか、どっちですか？

[tokyo2020]

構文エラーについてはこちらの凡ミスでしたので、修正いたしました。
出ているエラーについては、
「Incorrect table definition; there can be only one auto column and it must be defined as a key」
auto_incrementにはpkをつけないといけないというエラーでした。
number, nameにpkを設定したところ
「Multiple primary key defined」
と出てしまいます

[xebme]

auto_increment項目はすべて異なる値になるので、サロゲートキー（代理キー）つまり単独で主キーになります。理論的には、主キーに従属する項目を加えて（水増しした）複合主キーをつくっても主キーの役割を果たします。
しかし、MariaDBは、auto_increment項目に水増しした主キーを許していない。なぜ、そうするのでしょうか。意味上の主キーは何かを追記してくださると答えやすくなるでしょう。

Answer 1

端的にいえば
auto_incrementしたカラムと別のカラムを複合でprimay keyに設定するのは
デメリットしか無いと思います。
auto_incrementは本来ユニークを確保するための処理です
それが他のカラムと複合で処理するとユニークではない前提になります。

どういう意図での試みかわかりかねますが、やめたほうがよいと思います

Comments

[tokyo2020]

回答ありがとうございます。
おっしゃる通りですね。もう一度テーブル設計者に確認とってみます。

皆さんもご協力いただきありがとうございました。

Answer 2

既にPRIMARY KEYがある場合は
ALTER TABLE テーブル名 DROP PRIMARY KEY;
でDROP。

ALTER TABLE テーブル名 ADD PRIMARY KEY (カラム名1, カラム名2);
で追加します。

Comments

[tokyo2020]

回答ありがとうございます。
一度auto_incremantだけにpkをつけ、
alterでdropを試みたのですが
「 Incorrect table definition; there can be only one auto column and it must be defined as a key」
auto_incrementのpkが外せなくて困っています。

[yoorwm]

ちょっと記憶が曖昧ですが、ALTER TABLEの構文は複数指定が可能なので
ALTER TABLE テーブル名
DROP PRIMARY KEY,
ADD PRIMARY KEY(カラム1, カラム2);
な感じで通りますか？
（以前に同様な事があった時、コレで解決したか、それともtmp_idという感じでプライマリキーと同じ型のカラムを作って、作り直したか・・・チョット覚えてないですが、まずは試してみてどうでしょう？）

[Orlofsky]

すみません。MySQLの実行環境がないので動作確認はしていません。
auto_increment のカラムだけでPRIMARY(一意)なのに、複数のカラムでPRIMARY KEYにしたい、って実際はどんなデータが入るのですか？
PRIMARY KEY以外にUNIQUE制約を設定することはできますが、普通あまりそういう使い方はしません。 https://www.dbonline.jp/mysql/table/index9.html

[tokyo2020]

yoorwmさん
回答ありがとうございます。
alterでauto_incrementのカラムはdropできませんでした。。。