タイトル: DDL
SEOタイトル: SQL DDL (CREATE/ALTER/DROP) 完全ガイド
| この記事の要点 |
|
DDL とは
DDL (Data Definition Language: データ定義言語) は、データベースのスキーマ構造（テーブル・インデックス・ビュー等）を定義・変更・削除する SQL コマンドの総称です。データ自体を操作する DML や、権限を操作する DCL とは区別されます。
| 区分 | 正式名称 | 主なコマンド | 役割 |
|---|---|---|---|
| DDL | Data Definition Language | CREATE / ALTER / DROP / TRUNCATE / RENAME | スキーマ定義 |
| DML | Data Manipulation Language | SELECT / INSERT / UPDATE / DELETE | データ操作 |
| DCL | Data Control Language | GRANT / REVOKE | 権限制御 |
| TCL | Transaction Control Language | COMMIT / ROLLBACK / SAVEPOINT | トランザクション |
主要 DDL コマンド一覧
| コマンド | 機能 | 例 |
|---|---|---|
CREATE | オブジェクト作成 | CREATE TABLE users (...) |
ALTER | オブジェクト変更 | ALTER TABLE users ADD email VARCHAR(255) |
DROP | オブジェクト削除 | DROP TABLE users |
TRUNCATE | 全行削除（高速・ロールバック不可） | TRUNCATE TABLE logs |
RENAME | 名前変更 | RENAME TABLE old TO new |
COMMENT | コメント付与（PG/Oracle） | COMMENT ON TABLE users IS 'ユーザー' |
CREATE TABLE: テーブル定義の基本
CREATE TABLE users (
id BIGINT NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
name VARCHAR(100) NOT NULL DEFAULT '',
age INT CHECK (age >= 0 AND age <= 150),
status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_users_email (email),
KEY idx_users_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;カラム定義の主な属性
| 属性 | 意味 | 例 |
|---|---|---|
NOT NULL | NULL を禁止 | name VARCHAR(100) NOT NULL |
DEFAULT 値 | 既定値 | status INT DEFAULT 0 |
CHECK | 値の制約 | age INT CHECK (age >= 0) |
UNIQUE | 一意制約 | email VARCHAR(255) UNIQUE |
PRIMARY KEY | 主キー | id BIGINT PRIMARY KEY |
FOREIGN KEY | 外部キー | FOREIGN KEY (user_id) REFERENCES users(id) |
AUTO_INCREMENT / SERIAL | 自動採番 | id BIGINT AUTO_INCREMENT |
FOREIGN KEY (外部キー)
CREATE TABLE posts (
id BIGINT NOT NULL AUTO_INCREMENT,
user_id BIGINT NOT NULL,
title VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_posts_user
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE -- 親削除時に子も削除
ON UPDATE CASCADE -- 親更新時に子も更新
) ENGINE=InnoDB;
-- ON DELETE / ON UPDATE のオプション
-- CASCADE : 親に連動
-- SET NULL : NULL に
-- RESTRICT : 子があれば親削除を拒否（デフォルト）
-- NO ACTION : RESTRICT と同等
-- SET DEFAULT : DEFAULT 値にALTER TABLE: スキーマ変更
-- カラム追加
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email;
-- カラム削除
ALTER TABLE users DROP COLUMN phone;
-- カラム型変更
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;
-- カラム名変更
ALTER TABLE users CHANGE COLUMN name full_name VARCHAR(200) NOT NULL;
-- インデックス追加
ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE users ADD UNIQUE KEY uq_email (email);
-- インデックス削除
ALTER TABLE users DROP INDEX idx_email;
-- 外部キー追加
ALTER TABLE posts
ADD CONSTRAINT fk_posts_user
FOREIGN KEY (user_id) REFERENCES users(id);
-- 外部キー削除
ALTER TABLE posts DROP FOREIGN KEY fk_posts_user;インデックス・ビュー・プロシージャ
-- INDEX
CREATE INDEX idx_users_status ON users(status, created_at);
DROP INDEX idx_users_status ON users;
-- VIEW
CREATE VIEW active_users AS
SELECT id, email, name FROM users WHERE status = 'active';
CREATE OR REPLACE VIEW v_user_post_count AS
SELECT u.id, u.name, COUNT(p.id) AS post_count
FROM users u LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name;
DROP VIEW active_users;
-- STORED PROCEDURE (MySQL)
DELIMITER //
CREATE PROCEDURE add_user(IN p_email VARCHAR(255), IN p_name VARCHAR(100))
BEGIN
INSERT INTO users (email, name) VALUES (p_email, p_name);
END //
DELIMITER ;
CALL add_user('foo@example.com', 'Foo');
-- TRIGGER
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
SET NEW.updated_at = NOW();
-- FUNCTION
CREATE FUNCTION user_count() RETURNS INT
DETERMINISTIC
BEGIN
DECLARE c INT;
SELECT COUNT(*) INTO c FROM users;
RETURN c;
END;DROP と TRUNCATE の違い
| 項目 | DROP TABLE | TRUNCATE TABLE | DELETE FROM |
|---|---|---|---|
| 区分 | DDL | DDL | DML |
| 対象 | テーブル自体 | 全行 | 条件指定可 |
| 速度 | 速い | 速い | 遅い（行ごとに削除） |
| ROLLBACK | 不可（多くの DB） | 不可（多くの DB） | 可 |
| AUTO_INCREMENT | テーブル消滅 | リセット | 維持 |
| トリガー | 消滅 | 発火しない | 発火する |
トランザクション中の DDL の挙動
多くの DB で DDL は暗黙的に自動コミットされます。これは「途中まで実行した DDL を ROLLBACK で取り消せない」ことを意味します。
-- MySQL / Oracle: DDL は暗黙コミット
BEGIN;
INSERT INTO users (email) VALUES ('a@b.c'); -- DML
ALTER TABLE users ADD COLUMN age INT; -- DDL ← この時点で INSERT が暗黙 COMMIT
ROLLBACK; -- ALTER は取り消せない / INSERT も既に COMMIT 済
-- PostgreSQL: DDL もトランザクション内に
BEGIN;
INSERT INTO users (email) VALUES ('a@b.c');
ALTER TABLE users ADD COLUMN age INT;
ROLLBACK; -- INSERT も ALTER も取り消される ★Online DDL (MySQL 8 / PostgreSQL)
巨大テーブルの ALTER はテーブルロックを取って数時間〜数日かかることがあります。MySQL 8 / PostgreSQL はサービス無停止で ALTERできるアルゴリズムを提供します。
-- MySQL 8 の Online DDL
ALTER TABLE users
ADD COLUMN email VARCHAR(255),
ALGORITHM=INPLACE, -- INSTANT / INPLACE / COPY
LOCK=NONE; -- NONE / SHARED / EXCLUSIVE
-- ALGORITHM の意味
-- INSTANT : メタデータのみ更新（最速、対応操作限定）
-- INPLACE : テーブル再構築するが行ロック取らない
-- COPY : 旧形式（テーブル全体ロック、長時間）
-- PostgreSQL: CONCURRENTLY でインデックス追加（行ロックなし）
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- 巨大テーブルは pt-online-schema-change (Percona) や gh-ost (GitHub) で
-- ダウンタイム無しに ALTER 推奨FAQ
Q: DROP TABLE と DROP TABLE IF EXISTS の違いは？
A: IF EXISTS 付けると、テーブルが無くてもエラーにならず警告のみ。マイグレーションスクリプトで安全に使えます。
Q: TRUNCATE は本当に高速？
A: はい。テーブルファイルを再作成する実装が多く、行数によらず一定時間です。ただし外部キー参照されていると失敗することがあります。
Q: DDL の途中で失敗したらどうなる？
A: MySQL では中途半端な状態が残ることがあります。PostgreSQL ならトランザクション内 DDL なので自動 ROLLBACK されます。