【SQL】ALTER TABLE完全ガイド｜列追加・削除・型変更・RENAME・制約操作・Online DDL・RDBMS別構文まで解説

-- 変更前の employees テーブル
CREATE TABLE employees (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(200),
    salary     INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- テーブル構造の確認（MySQL）
DESCRIBE employees;
-- または
SHOW CREATE TABLE employees\G

-- テーブル構造の確認（PostgreSQL）
\d employees

-- テーブル構造の確認（SQL Server）
EXEC sp_help 'employees';

-- 列を追加（末尾に追加）
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- NOT NULL + DEFAULT を指定して追加
ALTER TABLE employees ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- 複数列を一括追加（MySQL / Oracle）
ALTER TABLE employees
    ADD COLUMN dept_id INT,
    ADD COLUMN resigned_at DATE;

-- PostgreSQL / SQL Server: 複数列を一括追加
ALTER TABLE employees
    ADD COLUMN dept_id INT,
    ADD COLUMN resigned_at DATE;  -- 同じ構文で OK

-- NOT NULL 列を既存テーブルに追加する場合
-- → 既存行に NULL が入れられないため DEFAULT 値が必須
ALTER TABLE employees
    ADD COLUMN version INT NOT NULL DEFAULT 1;
-- 既存行はすべて version = 1 になる

-- 先頭に追加
ALTER TABLE employees ADD COLUMN code VARCHAR(10) FIRST;

-- 特定の列の後ろに追加
ALTER TABLE employees ADD COLUMN nickname VARCHAR(100) AFTER name;

-- ※ FIRST / AFTER は MySQL 独自構文
-- PostgreSQL・SQL Server では位置指定不可（末尾にしか追加できない）
-- → 並び順を変えたい場合は ALTER TABLE ... テーブル再作成が必要

-- 列を削除
ALTER TABLE employees DROP COLUMN phone;

-- 複数列を一括削除（MySQL）
ALTER TABLE employees
    DROP COLUMN dept_id,
    DROP COLUMN resigned_at;

-- 存在チェック付きで削除（MySQL 8.0 / PostgreSQL 9.0 以降）
-- MySQL 8.0 以降
ALTER TABLE employees DROP COLUMN IF EXISTS phone;

-- PostgreSQL
ALTER TABLE employees DROP COLUMN IF EXISTS phone;

-- ===== 削除前の確認 =====
-- MySQL: 参照しているインデックス・制約の確認
SHOW CREATE TABLE employees\G
-- → KEY や CONSTRAINT で phone を参照していると DROP COLUMN がエラーになる

-- 外部キーが参照している列は先に外部キーを削除してから
ALTER TABLE employees DROP FOREIGN KEY fk_dept;  -- 外部キー削除
ALTER TABLE employees DROP COLUMN dept_id;         -- その後に列削除

-- VARCHAR(100) → VARCHAR(200) に拡張
ALTER TABLE employees MODIFY COLUMN name VARCHAR(200) NOT NULL;

-- INT → BIGINT に変更（より大きな整数が必要になった場合）
ALTER TABLE employees MODIFY COLUMN salary BIGINT;

-- 型変換に注意: データが入っている場合、互換性のない型変換はエラー
-- NG: VARCHAR(5) に「123456」が入っている列を VARCHAR(3) に縮小 → エラー
-- NG: '2024-01-15' が入っている VARCHAR 列を INT に変換 → エラー
ALTER TABLE employees MODIFY COLUMN code INT;  -- 文字が入っていると失敗

-- 安全な型変換のパターン
-- 1. 新しい列を追加
ALTER TABLE employees ADD COLUMN salary_new DECIMAL(12,2);
-- 2. データを変換してコピー
UPDATE employees SET salary_new = CAST(salary AS DECIMAL(12,2));
-- 3. 古い列を削除
ALTER TABLE employees DROP COLUMN salary;
-- 4. 列名を変更
ALTER TABLE employees RENAME COLUMN salary_new TO salary;

-- VARCHAR(100) → VARCHAR(200) に拡張
ALTER TABLE employees ALTER COLUMN name TYPE VARCHAR(200);

-- PostgreSQL は一部の型変換に USING 句が必要
-- TEXT → INTEGER に変換（キャスト式を指定）
ALTER TABLE employees ALTER COLUMN salary TYPE BIGINT USING salary::BIGINT;

-- VARCHAR → DATE に変換（フォーマットが合っていれば）
ALTER TABLE employees ALTER COLUMN resigned_at TYPE DATE
    USING resigned_at::DATE;

-- NOT NULL 制約の追加・削除
ALTER TABLE employees ALTER COLUMN email SET NOT NULL;
ALTER TABLE employees ALTER COLUMN email DROP NOT NULL;

-- DEFAULT の追加・削除
ALTER TABLE employees ALTER COLUMN status SET DEFAULT 'active';
ALTER TABLE employees ALTER COLUMN status DROP DEFAULT;

-- VARCHAR(100) → VARCHAR(200) に拡張
ALTER TABLE employees ALTER COLUMN name VARCHAR(200) NOT NULL;

-- NOT NULL 制約の追加（型も一緒に指定が必要）
ALTER TABLE employees ALTER COLUMN email VARCHAR(200) NOT NULL;

-- NOT NULL 制約の削除（NULL 許容に変更）
ALTER TABLE employees ALTER COLUMN email VARCHAR(200) NULL;

-- DEFAULT 制約の追加（制約名付きで追加）
ALTER TABLE employees
ADD CONSTRAINT df_employees_status DEFAULT 'active' FOR status;

-- DEFAULT 制約の削除（制約名を指定）
ALTER TABLE employees DROP CONSTRAINT df_employees_status;

-- DEFAULT 制約名を確認する
SELECT name FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('employees')
  AND parent_column_id = COLUMNPROPERTY(
      OBJECT_ID('employees'), 'status', 'ColumnId');

-- ===== MySQL 8.0+ =====
ALTER TABLE employees RENAME COLUMN email TO email_address;

-- MySQL 5.7 以前: CHANGE（型の再指定が必要）
ALTER TABLE employees CHANGE email email_address VARCHAR(200);
-- 構文: CHANGE 旧列名 新列名 データ型 [オプション]

-- ===== PostgreSQL =====
ALTER TABLE employees RENAME COLUMN email TO email_address;

-- ===== SQL Server =====
EXEC sp_rename 'employees.email', 'email_address', 'COLUMN';

-- ===== RENAME COLUMN の注意点 =====
-- ビュー・ストアドプロシージャ・外部キー制約が旧列名を参照している場合は
-- それらも一緒に変更が必要
-- 依存関係の確認（PostgreSQL）
SELECT dependent_ns.nspname, dependent_view.relname
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_class AS source_table ON pg_depend.refobjid = source_table.oid
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
WHERE source_table.relname = 'employees';

-- ===== MySQL =====
RENAME TABLE employees TO staff;
-- 複数テーブルを一括変更（アトミック操作）
RENAME TABLE employees TO staff, departments TO depts;

-- または
ALTER TABLE employees RENAME TO staff;

-- ===== PostgreSQL =====
ALTER TABLE employees RENAME TO staff;

-- ===== SQL Server =====
EXEC sp_rename 'employees', 'staff';
-- または（スキーマ付き）
EXEC sp_rename 'dbo.employees', 'staff';

-- ===== テーブル名変更の注意点 =====
-- ・ビュー・外部キー・ストアドプロシージャが旧テーブル名を参照していれば変更が必要
-- ・アプリケーションコードの SQL も変更が必要
-- ・MySQL の RENAME TABLE はアトミックに実行されロールバックも可能

-- UNIQUE 制約の追加（列単位）
ALTER TABLE employees ADD CONSTRAINT uq_email UNIQUE (email);

-- UNIQUE インデックスとして追加（MySQL では等価）
CREATE UNIQUE INDEX uq_email ON employees (email);

-- UNIQUE 制約の削除
-- MySQL
ALTER TABLE employees DROP INDEX uq_email;
-- PostgreSQL / SQL Server
ALTER TABLE employees DROP CONSTRAINT uq_email;

-- 複合 UNIQUE（email + dept_id の組み合わせが一意）
ALTER TABLE employees ADD CONSTRAINT uq_email_dept UNIQUE (email, dept_id);

-- CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(100));

-- 外部キーの追加
ALTER TABLE employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
ON DELETE SET NULL    -- 親が削除されたら NULL にする
ON UPDATE CASCADE;    -- 親が更新されたら連動して更新

-- 外部キーの削除
-- MySQL
ALTER TABLE employees DROP FOREIGN KEY fk_dept;
-- PostgreSQL / SQL Server
ALTER TABLE employees DROP CONSTRAINT fk_dept;

-- 外部キー名の確認（MySQL）
SELECT CONSTRAINT_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'employees'
  AND CONSTRAINT_TYPE = 'FOREIGN KEY';

-- 外部キーの一時的な無効化（MySQL）
SET FOREIGN_KEY_CHECKS = 0;
-- バルクインサートなど実行
SET FOREIGN_KEY_CHECKS = 1;

-- 外部キーの一時的な無効化（PostgreSQL）
ALTER TABLE employees DISABLE TRIGGER ALL;  -- 外部キーを含むトリガー無効化
ALTER TABLE employees ENABLE TRIGGER ALL;

-- CHECK 制約の追加（salary が 0 以上）
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 0);

-- CHECK 制約（status の値制限）
ALTER TABLE employees
ADD CONSTRAINT chk_status CHECK (status IN ('active', 'inactive', 'resigned'));

-- CHECK 制約の削除
ALTER TABLE employees DROP CONSTRAINT chk_salary;

-- MySQL 8.0 以前は CHECK を構文として受け付けるが無視する
-- MySQL 8.0.16 以降で CHECK 制約が有効になった

-- 複数の変更を 1 つの ALTER TABLE にまとめる
ALTER TABLE employees
    ADD COLUMN dept_id INT,
    ADD COLUMN resigned_at DATE,
    ADD COLUMN version INT NOT NULL DEFAULT 1,
    MODIFY COLUMN name VARCHAR(200) NOT NULL,
    DROP COLUMN phone,
    ADD CONSTRAINT uq_email UNIQUE (email),
    ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

-- ※ 複数回に分けると毎回テーブルが再構築される（大量データで時間がかかる）
-- 1回にまとめると再構築が1回で済む → 実行時間を大幅に短縮できる

-- PostgreSQL でも複数操作を一括実行可能
ALTER TABLE employees
    ADD COLUMN dept_id INT,
    ADD COLUMN resigned_at DATE,
    ALTER COLUMN name TYPE VARCHAR(200),
    ALTER COLUMN email SET NOT NULL,
    DROP COLUMN phone;

-- PostgreSQL では ALTER TABLE 全体がトランザクション内で実行される
-- 途中でエラーが起きればすべてロールバック

-- ALGORITHM=INSTANT: テーブル再構築なしで即座に完了（MySQL 8.0+、列追加に対応）
ALTER TABLE employees
    ADD COLUMN score INT DEFAULT 0,
    ALGORITHM=INSTANT;  -- テーブルロックなし・即座に完了

-- ALGORITHM=INPLACE: テーブルをコピーせず変更（多くの操作で利用可能）
ALTER TABLE employees
    ADD INDEX idx_dept (dept_id),
    ALGORITHM=INPLACE, LOCK=NONE;  -- 読み書き並行実行可能

-- ALGORITHM=COPY: テーブル全体をコピー（古い動作・ロック発生）
ALTER TABLE employees
    MODIFY COLUMN salary BIGINT,
    ALGORITHM=COPY;  -- 大テーブルでは時間がかかる

-- LOCK=DEFAULT / NONE / SHARED / EXCLUSIVE
-- LOCK=NONE: 読み書き両方を並行許可（不可能な操作はエラー）
-- LOCK=SHARED: 読み取りのみ並行（書き込みは待ち）
-- LOCK=EXCLUSIVE: 全操作を直列化

-- どのアルゴリズムが使われるか確認（試してみる）
-- ALGORITHM=INSTANT がサポートされなければエラーになる（実際の変更は起きない）
ALTER TABLE employees ADD COLUMN tmp INT, ALGORITHM=INSTANT;

-- 通常のインデックス作成（テーブルを短時間ロック）
CREATE INDEX idx_emp_email ON employees (email);

-- CONCURRENTLY: テーブルをロックせずにインデックス作成（時間はかかる）
CREATE INDEX CONCURRENTLY idx_emp_email ON employees (email);
-- → 読み書き並行実行中でもインデックスを作成できる
-- → ただし通常より時間がかかり、失敗すると INVALID インデックスが残る

-- INVALID インデックスの確認と削除
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'employees'
  AND indexname = 'idx_emp_email';

-- PostgreSQL 11 以降: DEFAULT 値付き列の追加が即座（テーブルスキャンなし）
ALTER TABLE employees ADD COLUMN score INT DEFAULT 0;
-- PG10 以前: 全行を更新するためテーブルスキャンが発生

-- pt-online-schema-change（Percona Toolkit）
-- テーブルをコピーしながら変更・元テーブルをアトミックにリネーム
-- → 数億行のテーブルでも無停止で ALTER TABLE 相当の操作が可能

-- 実行コマンド例（コマンドライン）
-- pt-online-schema-change \
--   --alter "ADD COLUMN score INT DEFAULT 0" \
--   --execute \
--   D=mydb,t=employees

-- gh-ost（GitHub 製）
-- バイナリログを利用したオンラインスキーマ変更ツール
-- より安全で進捗管理・一時停止・再開が可能

-- gh-ost 実行コマンド例
-- gh-ost \
--   --database="mydb" \
--   --table="employees" \
--   --alter="ADD COLUMN score INT DEFAULT 0" \
--   --execute

-- どちらも大きなテーブルで ALTER TABLE を安全に実行するための定番ツール

-- ① 現在の NULL 状況を確認
SELECT COUNT(*) FROM employees WHERE email IS NULL;

-- ② NULL 行を更新してから制約を追加
UPDATE employees SET email = 'unknown@example.com' WHERE email IS NULL;

-- ③ NOT NULL 制約を追加
-- MySQL
ALTER TABLE employees MODIFY COLUMN email VARCHAR(200) NOT NULL;
-- PostgreSQL
ALTER TABLE employees ALTER COLUMN email SET NOT NULL;

-- ④ DEFAULT も同時に設定しておくと今後の INSERT で NULL が入らなくなる
ALTER TABLE employees MODIFY COLUMN email VARCHAR(200) NOT NULL DEFAULT '';
-- または: ALTER TABLE employees MODIFY COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';