【SQL】プライマリキーの追加と削除方法｜ALTER TABLE・複合キー・AUTO_INCREMENT・RDBMS別の違い

【SQL】プライマリキーの追加と削除方法｜ALTER TABLE・複合キー・AUTO_INCREMENT・RDBMS別の違い SQL
2024.04.23

プライマリキー（PRIMARY KEY）は、テーブル内の各行を一意に識別するための制約です。データの重複やNULLを防ぎ、テーブル設計の土台となります。

この記事では、プライマリキーの追加・削除・変更方法から、複合キー、AUTO_INCREMENTとの組み合わせ、RDBMS別の違いまで、実務で必要な知識を網羅的に解説します。

この記事で学べること

  • CREATE TABLEでプライマリキーを設定する方法
  • ALTER TABLEで既存テーブルにプライマリキーを追加する方法
  • プライマリキーを削除する方法（RDBMS別）
  • 複合プライマリキー（複数列）の設定方法
  • AUTO_INCREMENT / IDENTITY / SERIALとの組み合わせ
  • プライマリキーの変更（削除→再作成）
  • MySQL・PostgreSQL・SQL Server・Oracleの構文の違い
  • よくあるエラーと対処法
スポンサーリンク

サンプルテーブル

以下のテーブルを例に解説します。

id name email
1 田中太郎 tanaka@example.com
2 鈴木花子 suzuki@example.com
3 佐藤一郎 sato@example.com

CREATE TABLEでプライマリキーを設定する

列定義に直接指定する方法

列レベルで指定
CREATE TABLE users (
  id   INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

テーブル制約として指定する方法

テーブルレベルで指定（制約名付き）
CREATE TABLE users (
  id    INT,
  name  VARCHAR(50),
  email VARCHAR(100),
  CONSTRAINT pk_users PRIMARY KEY (id)
);

ポイント：制約名（pk_users）を付けておくと、後から削除・変更する際に指定しやすくなります。命名規則はpk_テーブル名が一般的です。

ALTER TABLEでプライマリキーを追加する

既存のテーブルにプライマリキーを追加するには、ALTER TABLE文を使います。

プライマリキーの追加
-- 制約名を指定して追加
ALTER TABLE users
ADD CONSTRAINT pk_users PRIMARY KEY (id);

-- 制約名を省略（RDBMS が自動命名）
ALTER TABLE users
ADD PRIMARY KEY (id);

注意：プライマリキーを追加する列にNULLや重複データが含まれているとエラーになります。事前にデータを確認・修正してください。

追加前のデータ確認

NULLと重複の確認
-- NULLの確認
SELECT COUNT(*) FROM users WHERE id IS NULL;

-- 重複の確認
SELECT id, COUNT(*) AS cnt
FROM   users
GROUP BY id
HAVING COUNT(*) > 1;

プライマリキーを削除する

プライマリキーの削除方法はRDBMSごとに構文が異なります。

RDBMS別の削除構文
-- MySQL（制約名不要）
ALTER TABLE users
DROP PRIMARY KEY;

-- PostgreSQL / Oracle / SQL Server（制約名で指定）
ALTER TABLE users
DROP CONSTRAINT pk_users;
RDBMS 削除構文 制約名の確認方法
MySQL DROP PRIMARY KEY SHOW CREATE TABLE テーブル名;
PostgreSQL DROP CONSTRAINT 制約名 \d テーブル名
SQL Server DROP CONSTRAINT 制約名 sp_help テーブル名
Oracle DROP CONSTRAINT 制約名 / DROP PRIMARY KEY SELECT * FROM user_constraints;

注意：MySQLでAUTO_INCREMENTが設定されている列のプライマリキーを削除するには、先にAUTO_INCREMENT属性を外す必要があります。

複合プライマリキー（複数列）

複数の列を組み合わせてプライマリキーにすることを複合プライマリキー（Composite Primary Key）と呼びます。中間テーブルや履歴テーブルでよく使われます。

複合プライマリキーの例
-- 多対多の中間テーブル
CREATE TABLE user_roles (
  user_id INT,
  role_id INT,
  PRIMARY KEY (user_id, role_id)
);
ALTER TABLEで複合キーを追加
ALTER TABLE user_roles
ADD CONSTRAINT pk_user_roles PRIMARY KEY (user_id, role_id);

ポイント：複合プライマリキーでは、列の組み合わせが一意であればよく、個々の列にはNULLでない限り重複が許されます。例えば(user_id=1, role_id=1)と(user_id=1, role_id=2)は共存可能です。

AUTO_INCREMENT / IDENTITY / SERIALとの組み合わせ

プライマリキーには連番を自動生成する仕組みと組み合わせるのが一般的です。

RDBMS別の自動連番
-- MySQL
CREATE TABLE users (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

-- PostgreSQL
CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(50)
);

-- SQL Server
CREATE TABLE users (
  id   INT IDENTITY(1,1) PRIMARY KEY,
  name NVARCHAR(50)
);

-- Oracle（12c以降）
CREATE TABLE users (
  id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR2(50)
);

プライマリキーを変更する

プライマリキーを直接変更するSQL構文はありません。削除 → 再作成の2ステップで行います。

プライマリキーの変更（MySQL）
-- 1. 既存のプライマリキーを削除
ALTER TABLE users
DROP PRIMARY KEY;

-- 2. 新しいプライマリキーを追加
ALTER TABLE users
ADD PRIMARY KEY (email);
プライマリキーの変更（PostgreSQL / SQL Server / Oracle）
-- 1. 既存のプライマリキーを削除
ALTER TABLE users
DROP CONSTRAINT pk_users;

-- 2. 新しいプライマリキーを追加
ALTER TABLE users
ADD CONSTRAINT pk_users PRIMARY KEY (email);

注意：プライマリキーが外部キー（FOREIGN KEY）で参照されている場合、先に参照しているテーブルの外部キー制約を削除する必要があります。

よくあるエラーと対処法

エラー 原因 対処法
Duplicate entry for key 'PRIMARY' 対象列に重複データがある 重複を削除してから再実行
Column cannot be null 対象列にNULLがある NULLを適切な値に更新
Multiple primary key defined 既にプライマリキーが存在 既存のキーを削除してから追加
Cannot drop index needed in a foreign key 外部キーで参照されている 外部キー制約を先に削除
Incorrect table definition; there can be only one auto column AUTO_INCREMENTをPK以外に設定 AUTO_INCREMENTはPK列にのみ設定

まとめ

操作 SQL
テーブル作成時に設定 CREATE TABLE t (id INT PRIMARY KEY);
既存テーブルに追加 ALTER TABLE t ADD PRIMARY KEY (id);
削除（MySQL） ALTER TABLE t DROP PRIMARY KEY;
削除（その他） ALTER TABLE t DROP CONSTRAINT 制約名;
複合キー PRIMARY KEY (col1, col2)
変更 削除 → 再作成の2ステップ

プライマリキーはテーブル設計の要です。CREATE TABLE時に設定するのが基本ですが、ALTER TABLEで後から追加・変更することも可能です。RDBMSごとに削除構文が異なる点に注意しましょう。

関連記事