SQL ALTER TABLEによるスキーマ変更

SQL ALTER TABLEによるスキーマ変更

ALTER TABLE 文は既存のテーブル構造を変更します。アプリケーション要件の変化に伴いデータベーススキーマを進化させるための主要なツールです。

カラムの追加

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20),
ADD COLUMN is_active BOOLEAN DEFAULT true NOT NULL;

DEFAULTが指定されない限り、新しいカラムは既存の行に対してNULL値で追加されます。

カラムの変更

-- PostgreSQL
ALTER TABLE products
ALTER COLUMN price TYPE DECIMAL(12, 2),
ALTER COLUMN description SET NOT NULL;

-- MySQL
ALTER TABLE products
MODIFY COLUMN price DECIMAL(12, 2),
MODIFY COLUMN description TEXT NOT NULL;

カラムの削除

ALTER TABLE users
DROP COLUMN IF EXISTS legacy_field;

名前の変更

-- カラム名の変更（PostgreSQL）
ALTER TABLE employees RENAME COLUMN dept TO department;

-- テーブル名の変更
ALTER TABLE user_accounts RENAME TO accounts;

制約の管理

-- 制約の追加
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 制約の削除
ALTER TABLE orders DROP CONSTRAINT fk_customer;

-- インデックスの追加
CREATE INDEX idx_orders_date ON orders(order_date);

ゼロダウンタイムマイグレーション

本番データベースのスキーマ変更は慎重な計画が必要です:

1. まずNULLを許可するカラムを追加し、データをバックフィルしてから、NOT NULL制約を追加する
2. 本番環境でカラム名を直接リネームしない。新しいカラムを追加し、データをコピーし、アプリケーションコードを更新してから、古いカラムを削除する
3. サポートされている場合はインデックスを並行して作成する（PostgreSQLの CREATE INDEX CONCURRENTLY）
4. 実行時間を見積もるために、本番データのコピーでマイグレーションをテストする
5. 長時間のロックを避けるため、データのバックフィルには小さなバッチを使用する

ロックに関する考慮事項

• DEFAULT付きのカラム追加はPostgreSQL 11以降およびMySQL 8.0以降では瞬時に完了する
• NOT NULL制約の追加にはすべての行のスキャンが必要
• MySQLではインデックスの作成は ALGORITHM=INPLACE を使用しない限りテーブルをロックする

ALTER TABLEはデータベースの進化に不可欠ですが、本番環境でのダウンタイムやデータ損失を避けるために慎重な計画が必要です。