制約の追加・変更とテーブル再定義

本記事で使うデータ — legacy_user

運用中のテーブルは、最初の設計では足りなかった制約を後から足したくなることがよくあります。

「列にデフォルト値を付けたい」「メールアドレスを重複禁止（UNIQUE）にしたい」「必須項目をNOT NULLにしたい」などです。

本記事では、列の追加とデフォルト値の後付け、そして既存テーブルへ制約を足すためのテーブル再構築の手順を扱います。

演習に入る前に、legacy_userテーブルの列定義とデータのサンプルを確認しておきます。

① PRAGMA table_info(legacy_user);で列名・型・主キー・NOT NULL・デフォルト値を確認してください。

② SELECT * FROM legacy_user;で全行のデータをプレビューしてください。

ALTER TABLE ADD COLUMN — 列とデフォルト値を後付けする

既存テーブルに新しい列を足すにはALTER TABLE 表 ADD COLUMN 列名 型 制約を使います。

DEFAULT 値を付けると、その列の値を省略したときに使われる既定値が決まり、ALTER実行時に既存の行にもデフォルト値が埋まります。

ADD COLUMNにはDEFAULT付きのNOT NULLも付けられます（既存行はデフォルト値で埋まるのでNOT NULL違反になりません）。

一方、ADD COLUMNでUNIQUEや、デフォルト値の無いNOT NULLを直接付けることはできません。

これらは「テーブルの作り直し」が必要です。

-- DEFAULT 付きで列を足す例 (確認用の捨てテーブルで挙動を見る)
DROP TABLE IF EXISTS alter_demo;
CREATE TABLE alter_demo(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO alter_demo VALUES (1,'Alice'),(2,'Bob');

-- created_on をデフォルト値付きで追加 -> 既存行にも値が入る
ALTER TABLE alter_demo ADD COLUMN created_on TEXT DEFAULT '2024-01-01';

-- NOT NULL も DEFAULT 付きなら追加できる
ALTER TABLE alter_demo ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM alter_demo ORDER BY id;

「ユーザーテーブルに状態を表すstatus列を足したい。既存ユーザーは全員active扱いにしたい」という要件を想定します。（正しく実行できれば解説が表示されます）

① legacy_userテーブルに、statusという名前のTEXT型の列を、デフォルト値'active'付きで追加してください。

② 続けてSELECT * FROM legacy_user;を実行し、既存の 2 行のstatus列にデフォルト値が埋まっていることを確認してください。

制約の後付けはテーブル再構築で行う

MySQL ではALTER TABLE legacy_user ADD CONSTRAINT uq_email UNIQUE (email);のように、既存テーブルへ制約を直接足す書き方があります。

本講座のコンソールでは、既存テーブルにUNIQUEやNOT NULLを後付けするときは「望む制約を持った新しいテーブルを作り、既存データを移し替え、古いテーブルを置き換える」テーブル再構築という手順を使います。

MySQL / PostgreSQL でも、列の型変更などツールによってはこの再構築方式が内部で行われます。

手順は下の図のとおり、外部キーチェックを一時停止してから新表 CREATE → 全行コピー → 旧表 DROP → 新表 RENAME → チェック復帰、の順に進めます。

MySQL の ADD CONSTRAINT / AUTO_INCREMENT・カラムコメントについて

下に示すALTER TABLE ... ADD CONSTRAINTやAUTO_INCREMENT、カラムコメント（COMMENT '...'）はMySQL の書き方です。

本講座のブラウザコンソールでは再現できないため、この 2 つのcodeブロックは読むだけ（実行はしません）。

本講座のコンソールでは、制約の後付けは下のセクションのテーブル再構築で行い、自動採番はINTEGER PRIMARY KEYで実現します。

-- これは MySQL の書き方 (本講座のコンソールでは実行しません・読むだけ)

-- MySQL: 既存テーブルへ UNIQUE 制約を直接後付けできる
ALTER TABLE legacy_user ADD CONSTRAINT uq_email UNIQUE (email);

-- MySQL: NOT NULL への変更も MODIFY で行える
ALTER TABLE legacy_user MODIFY email VARCHAR(255) NOT NULL;

-- MySQL: 自動採番はカラム属性 AUTO_INCREMENT、説明はカラムコメント
CREATE TABLE member (
  member_id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE COMMENT 'login email'
);

-- 本講座のコンソールでは AUTO_INCREMENT 相当を INTEGER PRIMARY KEY で実現する
CREATE TABLE member (
  member_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- テーブル再構築の骨格 (捨てテーブル rebuild_demo で挙動を見る)
DROP TABLE IF EXISTS rebuild_demo;
CREATE TABLE rebuild_demo(id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO rebuild_demo VALUES (1,'a@example.com'),(2,'b@example.com');

-- ① 外部キーチェックを一時停止
PRAGMA foreign_keys=OFF;

-- ② 望む制約 (email を NOT NULL UNIQUE) を持つ新表
CREATE TABLE rebuild_demo_new(
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- ③ 既存データを移し替え
INSERT INTO rebuild_demo_new(id, email)
SELECT id, email FROM rebuild_demo;

-- ④ 旧表を外し、⑤ 新表を元の名前に
DROP TABLE rebuild_demo;
ALTER TABLE rebuild_demo_new RENAME TO rebuild_demo;

-- ⑥ 外部キーチェックを戻す
PRAGMA foreign_keys=ON;

-- 制約が効いているか確認 (重複 email はエラーになる)
SELECT sql FROM sqlite_master WHERE name='rebuild_demo';

「legacy_userのemail列を、必須かつ重複禁止（NOT NULLかつUNIQUE）に変更したい」という要件を想定します。本記事のメインとなるテーブル再構築の手順を、上の図のとおり順に実行します。

① 外部キーチェックを一時停止してください（PRAGMA foreign_keys=OFF;）。

② legacy_user_newという名前で、id（INTEGER 主キー）、name（TEXT）、email（TEXT・NOT NULL・UNIQUE）を持つ新しいテーブルをCREATEしてください。

③ INSERT INTO legacy_user_new(...) SELECT ... FROM legacy_user;で既存の全行を新テーブルへコピーしてください。

④ 古いlegacy_userをDROPし、legacy_user_newをlegacy_userにRENAMEしてください。

⑤ 外部キーチェックをONに戻してください。

⑥ 最後にSELECT sql FROM sqlite_master WHERE name='legacy_user';を実行し、新しいスキーマに UNIQUE / NOT NULL が入っていることを確認してください。

実践 2 と同じ再構築を捨てテーブルで再現し、新スキーマのUNIQUE制約が実際に重複登録を止めることを体感します。本演習は単独で動くよう、専用テーブルemail_checkを作って完結させます。

① email_checkをDROP TABLE IF EXISTSしてから、id（INTEGER 主キー）とemail（TEXT・NOT NULL・UNIQUE）を持つ形でCREATEしてください。

② email_checkに(1, 'a@example.com')を 1 件INSERTしてください。

③ 続けて、すでに存在する'a@example.com'をemailに持つ別の行(2, 'a@example.com')をINSERTしてください。UNIQUE制約に違反するため、この 2 件目のINSERTはエラーになるのが正解です。