【保存版】MySQL/MariaDBテーブルの主キー設定：追加、変更、削除の全手順

2024-06-18

MySQL/MariaDBでテーブルに後から主キーを追加した場合、主キーはクラスタ化インデックスになるのか？

主キーを追加するタイミング

テーブル作成後: この場合も、多くの場合で主キーはクラスタ化インデックスになりますが、以下の例外が発生する可能性があります。
- 既存のインデックスと競合する場合: 既存のインデックスがテーブルのデータと同じ順序で並んでいる場合、新しい主キーはクラスタ化インデックスにならず、非クラスタ化インデックスになる可能性があります。
- innodb_file_per_table テーブルオプションが有効な場合: このオプションが有効な場合、テーブルは個々のファイルに保存され、主キーは必ずしもクラスタ化インデックスとはなりません。

確認方法

以下のコマンドで、テーブルのインデックス情報を確認できます。

SHOW CREATE TABLE table_name;

出力結果を確認し、PRIMARY KEY 行の USING キーワードを確認します。

USING BTREE：クラスタ化インデックス

まとめ

主キーは、多くの場合でクラスタ化インデックスになります。
例外も存在するため、詳細は SHOW CREATE TABLE コマンドで確認する必要があります。

この説明は、MySQL 8.0.28 と MariaDB 10.6.11 で検証しています。
複雑なテーブル構造の場合は、データベース管理者に相談することをお勧めします。

-- テーブル作成（主キーなし）
CREATE TABLE users (
  id INT AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- データ挿入
INSERT INTO users (name, email) VALUES ('Taro Yamada', '[email protected]'), ('Hanako Suzuki', '[email protected]');

-- 主キーの追加
ALTER TABLE users ADD PRIMARY KEY (id);

-- インデックス情報の確認
SHOW CREATE TABLE users;

users テーブルが主キーなしで作成されます。
2 件のデータがテーブルに挿入されます。
id 列に主キーが追加されます。
SHOW CREATE TABLE コマンドの結果で、PRIMARY KEY 行の USING キーワードが BTREE であることが確認できます。つまり、主キーはクラスタ化インデックスになっています。

補足

この例では、id 列が自動的にインクリメントされるようになっています。これは、主キーとして適切な列であることを意味します。
主キーを追加する前に、テーブルに重複する値がないことを確認する必要があります。
主キーを追加すると、テーブルのパフォーマンスが向上することがあります。

主キーを後から追加するその他の方法

ALTER TABLE ステートメントを使用する

これは、最も一般的で簡単な方法です。

ALTER TABLE table_name ADD PRIMARY KEY (column_name1, column_name2, ...);

table_name は、主キーを追加するテーブルの名前です。
column_name1、column_name2 は、主キーを構成する列の名前です。複数の列を指定して複合主キーを作成できます。

例:

ALTER TABLE users ADD PRIMARY KEY (id);

テーブルを作成する際に、CREATE TABLE ステートメントで主キーを指定する方法もあります。

CREATE TABLE table_name (
  column_name1 data_type PRIMARY KEY,
  column_name2 data_type,
  ...
);

column_name1 は、主キーとなる列の名前です。
data_type は、列のデータ型です。

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);