複合主キーに後からAUTO_INCREMENTをつけたい

Question

以下のようなテーブルがあります

CREATE TABLE body.group_artist_relations
(
    group_id                    INT NOT NULL,
    artist_id                   INT NOT NULL,
    pk_group_artist_relations   INT NOT NULL,
    CONSTRAINT PRIMARY KEY (group_id, artist_id)
);

** pk_group_artist_relations にAUTO_INCREMENT**をつけたいと思い以下のようなSQLを書いたのですが

ALTER TABLE group_artist_relations modify pk_group_artist_relations  INT( 4 ) AUTO_INCREMENT;

incorrect table definition; there can be only one auto column and it must be defined as a key
と言われてしまいました
何が違うのでしょうか?

Answer 1

pk_group_artist_relationsカラムにインデックスが張られていないためです。

以下のリンクの「カラムのデータ型および属性」という項目に、以下のように記載があります。
https://dev.mysql.com/doc/refman/5.6/ja/create-table.html

注記
テーブルごとに存在できる AUTO_INCREMENT カラムは 1 つだけです。このカラムはインデックス付きである必要があり、DEFAULT 値を割り当てることはできません。

そのため、pk_group_artist_relationsカラムにインデックス（オートインクリメントカラムなので、ユニークインデックスが相応しいでしょう）を張ってやれば、
うまく行きます。

mysql> CREATE TABLE group_artist_relations
    -> (
    ->     group_id                    INT NOT NULL,
    ->     artist_id                   INT NOT NULL,
    ->     pk_group_artist_relations   INT NOT NULL,
    ->     CONSTRAINT PRIMARY KEY (group_id, artist_id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE group_artist_relations modify pk_group_artist_relations  INT( 4 ) UNIQUE AUTO_INCREMENT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE group_artist_relations\G
*************************** 1. row ***************************
       Table: group_artist_relations
Create Table: CREATE TABLE `group_artist_relations` (
  `group_id` int(11) NOT NULL,
  `artist_id` int(11) NOT NULL,
  `pk_group_artist_relations` int(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`group_id`,`artist_id`),
  UNIQUE KEY `pk_group_artist_relations` (`pk_group_artist_relations`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932
1 row in set (0.00 sec)

ところで、pk_group_artist_relationsカラムの用途は何でしょうか？
すでにプライマリキーが存在するので、オートインクリメントカラムは
｢そもそも必要なの？｣
という疑問を感じます。

Comments

[退会済みユーザー]

UNIQUEをつけたらできました

Answer 2

あとから追加する以前に、複合キーの第二キー以降にAUTO_INCREMENT属性はつけられないようです。

たとえばつぎのようにすると、複合キーのひとつにAUTO_INCREMENT属性を付与させること自体は可能です。

CREATE TABLE body.group_artist_relations
(
    group_id                    INT NOT NULL,
    artist_id                   INT NOT NULL AUTO_INCREMENT,
    pk_group_artist_relations   INT NOT NULL,
    CONSTRAINT PRIMARY KEY (artist_id, group_id) -- (group_id, artist_id) ではなく
);

http://www.dbonline.jp/mysql/table/index7.html
しかしながら、テーブル１つに対して連番が付される仕様がAUTO_INCREMENT属性ですので（上記ページご参照）、仮に (group_id, artist_id) の順で主キーを定義できたとしても、group_idの値と関係なくartist_idは+1ずつされていくでしょう。

[group_id, artist_id]
100, 1
120, 2
100, 3
100, 4
115, 5

group_id の値各々において連番を付与したいのでしたら

[group_id, artist_id]
100, 1
120, 1
100, 2
100, 3
115, 1

解決方法として、INSERTトリガーで定義する方法があります。
http://www.dbonline.jp/mysql/trigger/index1.html

CREATE TRIGGER trigger1 BEFORE INSERT ON body.group_artist_relations
 FOR EACH ROW BEGIN
  DECLARE newid INT;
  SELECT IFNULL(MAX(artist_id) + 1, 初期値) INTO @newid FROM group_artist_relations WHERE group_id = new.group_id;
  SET new.artist_id = @newid;
 END

関連行がDELETEされたときにつじつまが合わなくなる可能性がありますので、関連テーブルには参照制約（FOREIGN KEY .. REFERENCES...）を指定しておくことをおすすめします。

Answer 3

これでは？↓

MySQL フィールドにAUTO_INCREMENT属性を追加する
http://blog.doli.jp/blog/2012/post509/