2026.10.06
- 設計
テーブル定義書の書き方とテンプレート DDLから自動生成するSQL
テーブル定義書とは、データベースの各テーブルについて、列の論理名・物理名・型・桁・NULLの可否・既定値・主キー・外部キー・索引を一覧にした設計書です。 1テーブルを1枚（1シート）にまとめ、全体を見渡す「テーブル一覧」を先頭に付けるのが基本の形です。
この記事では、定義書に書く項目の一覧と貼って使えるテンプレートを記入例つきで載せ、さらに CREATE TABLE（DDL）で作成済みのデータベースから定義書の表を取り出すSQL を PostgreSQL と MySQL の両方で、実行結果つきで示します。取り出した結果を CSV と Markdown の表に書き出す Python も付けました。CREATE TABLE の文法やER図の描き方には触れず、定義書の体裁とデータベースからの逆生成に絞ります。
確認環境：Ubuntu 24.04 / PostgreSQL 16.15 / MySQL 8.0.46 / Python 3.11.15（標準ライブラリのみ）
テーブル定義書の早見表
| 項目 | 書く内容 | 記入例 | DBから取れるか |
|---|---|---|---|
| テーブル論理名 | 日本語の名前 | 予約 | ○（コメントに入れておく） |
| テーブル物理名 | 実際のテーブル名 | reservations | ○ |
| No | 列の並び順 | 1 | ○ |
| 論理名 | 列の日本語名 | 利用開始日 | ○（コメントに入れておく） |
| 物理名 | 実際の列名 | start_date | ○ |
| 型 | データ型 | DATE、VARCHAR | ○ |
| 桁 | 長さ、または全体桁数と小数桁数 | 50、(10,2) | ○ |
| NULL | NULLを許すか | 不可／可 | ○ |
| 既定値 | 値を省いたときに入る値 | 1、CURRENT_TIMESTAMP、自動採番 | ○ |
| PK | 主キー。複合のときは順番 | PK、PK1・PK2 | ○ |
| FK | 参照先のテーブルと列 | items.item_code | ○ |
| 索引 | 索引名・種類・列の並び | reservations_item_period_idx（item_code, start_date, end_date） | ○ |
| 備考 | コード値の意味、CHECK制約、設計の意図 | 0:申請中 1:承認 9:取消 | △（CHECK制約は取れる。意図は人が書く） |
本記事の内容は、ご自由にお使いください。
ご利用の際は、出典として本ページへのリンクを記載いただけますようお願いします。
（記載例）出典：株式会社RJC「テーブル定義書の書き方とテンプレート DDLから自動生成するSQL」
テーブル定義書の構成と項目一覧
テーブル定義書は、次の4つの部分で構成すると読みやすくなります。
| 部分 | 内容 | 単位 |
|---|---|---|
| 1. テーブル一覧 | 全テーブルの論理名・物理名・概要 | データベースに1つ |
| 2. テーブル情報 | 論理名・物理名・概要・更新日 | テーブルごと |
| 3. 列一覧 | No・論理名・物理名・型・桁・NULL・既定値・PK・FK・備考 | テーブルごと |
| 4. 索引一覧・制約一覧 | 索引名・種類・列／制約名・定義 | テーブルごと |
列一覧の各項目は、次のきまりで書くと表記がそろいます。
| 項目 | 書き方のきまり |
|---|---|
| No | テーブルの列の並び順どおりに1から振る |
| 論理名 | 画面や帳票で使う呼び名に合わせる。同じ意味の列は全テーブルで同じ名前にする |
| 物理名 | 実際の列名をそのまま書く（大文字・小文字も合わせる） |
| 型 | 定義書の中で表記を統一する（VARCHAR と character varying を混ぜない） |
| 桁 | 文字列は長さ、数値は「全体桁数,小数桁数」。型に含めて VARCHAR(50) と書いてもよい |
| NULL | 「不可／可」または「NOT NULL／空欄」。○×は意味が逆に読まれやすいので使い方を凡例に書く |
| 既定値 | 固定値・関数・自動採番を区別して書く。無いときは空欄または「―」 |
| PK | 主キーの列に「PK」。複合主キーは PK1、PK2 と順番を書く |
| FK | 「参照先テーブル.列」の形で書く |
| 備考 | コード値の意味、単位、CHECK制約、ほかの列との関係 |
コピペ用テンプレート
テーブル一覧
| No | 論理名 | 物理名 | 概要 | 備考 |
|---|---|---|---|---|
| 1 | 部署 | departments | 利用者が所属する部署 | マスタ |
| 2 | 備品 | items | 予約の対象になる備品 | マスタ |
| 3 | 利用者 | members | 備品を予約する人 | マスタ |
| 4 | 予約 | reservations | 備品の予約1件を1行で持つ | トランザクション |
No 論理名 物理名 概要 備考
1 部署 departments 利用者が所属する部署 マスタ
2 備品 items 予約の対象になる備品 マスタ
3 利用者 members 備品を予約する人 マスタ
4 予約 reservations 備品の予約1件を1行で持つ トランザクション
テーブル定義（1テーブル分）の記入例：予約
架空の社内備品予約システムの「予約」テーブルの記入例です。
| 項目 | 内容 |
|---|---|
| テーブル論理名 | 予約 |
| テーブル物理名 | reservations |
| 概要 | 備品の予約1件を1行で持つ。同じ備品の予約は期間が重ならない |
| 更新日 | 2026-10-05 |
列一覧
| No | 論理名 | 物理名 | 型 | 桁 | NULL | 既定値 | PK | FK | 備考 |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 予約ID | reservation_id | BIGINT | ― | 不可 | 自動採番 | PK | ― | |
| 2 | 備品コード | item_code | CHAR | 6 | 不可 | ― | ― | items.item_code | |
| 3 | 利用者ID | member_id | BIGINT | ― | 不可 | ― | ― | members.member_id | |
| 4 | 利用開始日 | start_date | DATE | ― | 不可 | ― | ― | ― | |
| 5 | 利用終了日 | end_date | DATE | ― | 不可 | ― | ― | ― | 利用開始日以降（CHECK） |
| 6 | 数量 | qty | INTEGER | ― | 不可 | 1 | ― | ― | 1以上99以下（CHECK） |
| 7 | 状態 | status | SMALLINT | ― | 不可 | 0 | ― | ― | 0:申請中 1:承認 9:取消 |
| 8 | 登録日時 | created_at | TIMESTAMP | ― | 不可 | CURRENT_TIMESTAMP | ― | ― |
索引一覧
| No | 索引名 | 種類 | 列（並び順） | 用途 |
|---|---|---|---|---|
| 1 | reservations_pkey | 主キー | reservation_id | 予約1件の特定 |
| 2 | reservations_item_period_idx | 通常 | item_code, start_date, end_date | 備品ごとの期間の重なり検索 |
| 3 | reservations_member_idx | 通常 | member_id | 利用者ごとの予約一覧 |
制約一覧
| No | 制約名 | 種類 | 内容 |
|---|---|---|---|
| 1 | reservations_period_check | CHECK | start_date <= end_date |
| 2 | reservations_qty_check | CHECK | qty が 1 以上 99 以下 |
表計算ソフトに貼れるタブ区切りです（列一覧）。
No 論理名 物理名 型 桁 NULL 既定値 PK FK 備考
1 予約ID reservation_id BIGINT ― 不可 自動採番 PK ―
2 備品コード item_code CHAR 6 不可 ― ― items.item_code
3 利用者ID member_id BIGINT ― 不可 ― ― members.member_id
4 利用開始日 start_date DATE ― 不可 ― ― ―
5 利用終了日 end_date DATE ― 不可 ― ― ― 利用開始日以降（CHECK）
6 数量 qty INTEGER ― 不可 1 ― ― 1以上99以下（CHECK）
7 状態 status SMALLINT ― 不可 0 ― ― 0:申請中 1:承認 9:取消
8 登録日時 created_at TIMESTAMP ― 不可 CURRENT_TIMESTAMP ― ―
準備：題材の4テーブルを作る（PostgreSQL）
ここからは、作成済みのデータベースから定義書を取り出す方法です。論理名はテーブルと列のコメントに入れておく のがポイントで、こうしておくと定義書の「論理名」までSQLで取り出せます。
CREATE TABLE departments (
dept_code CHAR(3) PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE members (
member_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
dept_code CHAR(3) NOT NULL REFERENCES departments (dept_code),
member_name VARCHAR(40) NOT NULL,
email VARCHAR(254) NOT NULL UNIQUE,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE items (
item_code CHAR(6) PRIMARY KEY,
item_name VARCHAR(100) NOT NULL,
category VARCHAR(10) NOT NULL DEFAULT 'PC' CHECK (category IN ('PC', 'MONITOR', 'ROOM')),
unit_price NUMERIC(10,2),
note TEXT
);
CREATE TABLE reservations (
reservation_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
item_code CHAR(6) NOT NULL REFERENCES items (item_code),
member_id BIGINT NOT NULL REFERENCES members (member_id),
start_date DATE NOT NULL,
end_date DATE NOT NULL,
qty INTEGER NOT NULL DEFAULT 1 CHECK (qty BETWEEN 1 AND 99),
status SMALLINT NOT NULL DEFAULT 0,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT reservations_period_check CHECK (start_date <= end_date)
);
CREATE INDEX reservations_item_period_idx ON reservations (item_code, start_date, end_date);
CREATE INDEX reservations_member_idx ON reservations (member_id);
COMMENT ON TABLE departments IS '部署';
COMMENT ON COLUMN departments.dept_code IS '部署コード';
COMMENT ON COLUMN departments.dept_name IS '部署名';
COMMENT ON TABLE members IS '利用者';
COMMENT ON COLUMN members.member_id IS '利用者ID';
COMMENT ON COLUMN members.dept_code IS '部署コード';
COMMENT ON COLUMN members.member_name IS '氏名';
COMMENT ON COLUMN members.email IS 'メールアドレス';
COMMENT ON COLUMN members.is_active IS '有効フラグ';
COMMENT ON COLUMN members.created_at IS '登録日時';
COMMENT ON TABLE items IS '備品';
COMMENT ON COLUMN items.item_code IS '備品コード';
COMMENT ON COLUMN items.item_name IS '備品名';
COMMENT ON COLUMN items.category IS '区分';
COMMENT ON COLUMN items.unit_price IS '購入単価';
COMMENT ON COLUMN items.note IS '備考';
COMMENT ON TABLE reservations IS '予約';
COMMENT ON COLUMN reservations.reservation_id IS '予約ID';
COMMENT ON COLUMN reservations.item_code IS '備品コード';
COMMENT ON COLUMN reservations.member_id IS '利用者ID';
COMMENT ON COLUMN reservations.start_date IS '利用開始日';
COMMENT ON COLUMN reservations.end_date IS '利用終了日';
COMMENT ON COLUMN reservations.qty IS '数量';
COMMENT ON COLUMN reservations.status IS '状態（0:申請中 1:承認 9:取消）';
COMMENT ON COLUMN reservations.created_at IS '登録日時';
PostgreSQLで定義書の表を作るSQL
テーブル一覧を取り出す
SELECT c.relname AS "物理名",
obj_description(c.oid, 'pg_class') AS "論理名",
(SELECT count(*) FROM pg_attribute a
WHERE a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped) AS "列数"
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
ORDER BY c.relname;
物理名 | 論理名 | 列数
--------------+--------+------
departments | 部署 | 2
items | 備品 | 5
members | 利用者 | 6
reservations | 予約 | 8
(4 rows)
列一覧（論理名・型・NULL・既定値・PK・FK）を取り出す
システムカタログ（pg_catalog）の pg_attribute を軸に、コメント・既定値・主キー・外部キーを結合します。format_type 関数を使うと、character varying(50) のように桁まで含んだ型の表記が得られます。
SELECT c.relname AS "テーブル",
a.attnum AS "No",
col_description(c.oid, a.attnum) AS "論理名",
a.attname AS "物理名",
format_type(a.atttypid, a.atttypmod) AS "型",
CASE WHEN a.attnotnull THEN '不可' ELSE '可' END AS "NULL",
CASE WHEN a.attidentity <> '' THEN '自動採番'
ELSE pg_get_expr(d.adbin, d.adrelid) END AS "既定値",
CASE WHEN pk.conrelid IS NOT NULL THEN 'PK' END AS "PK",
(SELECT string_agg(rt.relname || '.' || ra.attname, ', ')
FROM pg_constraint fk
JOIN pg_class rt ON rt.oid = fk.confrelid
JOIN pg_attribute ra ON ra.attrelid = fk.confrelid
AND ra.attnum = fk.confkey[array_position(fk.conkey, a.attnum)]
WHERE fk.conrelid = c.oid AND fk.contype = 'f'
AND a.attnum = ANY (fk.conkey)) AS "FK"
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = a.attnum
LEFT JOIN pg_constraint pk ON pk.conrelid = c.oid AND pk.contype = 'p'
AND a.attnum = ANY (pk.conkey)
WHERE n.nspname = 'public' AND c.relkind = 'r'
ORDER BY c.relname, a.attnum;
テーブル | No | 論理名 | 物理名 | 型 | NULL | 既定値 | PK | FK
--------------+----+--------------------------------+----------------+-----------------------------+------+-------------------------+----+-----------------------
departments | 1 | 部署コード | dept_code | character(3) | 不可 | | PK |
departments | 2 | 部署名 | dept_name | character varying(50) | 不可 | | |
items | 1 | 備品コード | item_code | character(6) | 不可 | | PK |
items | 2 | 備品名 | item_name | character varying(100) | 不可 | | |
items | 3 | 区分 | category | character varying(10) | 不可 | 'PC'::character varying | |
items | 4 | 購入単価 | unit_price | numeric(10,2) | 可 | | |
items | 5 | 備考 | note | text | 可 | | |
members | 1 | 利用者ID | member_id | bigint | 不可 | 自動採番 | PK |
members | 2 | 部署コード | dept_code | character(3) | 不可 | | | departments.dept_code
members | 3 | 氏名 | member_name | character varying(40) | 不可 | | |
members | 4 | メールアドレス | email | character varying(254) | 不可 | | |
members | 5 | 有効フラグ | is_active | boolean | 不可 | true | |
members | 6 | 登録日時 | created_at | timestamp without time zone | 不可 | CURRENT_TIMESTAMP | |
reservations | 1 | 予約ID | reservation_id | bigint | 不可 | 自動採番 | PK |
reservations | 2 | 備品コード | item_code | character(6) | 不可 | | | items.item_code
reservations | 3 | 利用者ID | member_id | bigint | 不可 | | | members.member_id
reservations | 4 | 利用開始日 | start_date | date | 不可 | | |
reservations | 5 | 利用終了日 | end_date | date | 不可 | | |
reservations | 6 | 数量 | qty | integer | 不可 | 1 | |
reservations | 7 | 状態（0:申請中 1:承認 9:取消） | status | smallint | 不可 | 0 | |
reservations | 8 | 登録日時 | created_at | timestamp without time zone | 不可 | CURRENT_TIMESTAMP | |
(21 rows)
結果の読み方です。
| 列 | 取り出し元 | 補足 |
|---|---|---|
| 論理名 | col_description(テーブルのOID, 列番号) |
COMMENT ON COLUMN で入れた文字列 |
| 型 | format_type(atttypid, atttypmod) |
桁つきの表記になる |
| NULL | pg_attribute.attnotnull |
true なら NOT NULL |
| 既定値 | pg_attrdef を pg_get_expr で文字列にする |
IDENTITY 列は attidentity で判定して「自動採番」と表示 |
| PK | pg_constraint の contype = 'p' |
conkey に主キーの列番号が配列で入る |
| FK | pg_constraint の contype = 'f' |
confrelid が参照先テーブル、confkey が参照先の列番号 |
items の category の既定値が 'PC'::character varying と型つきで表示されているのは PostgreSQL の内部表現です。定義書に載せるときは 'PC' と読み替えます。
information_schema だけで取り出す
標準的なビューである information_schema.columns でも、型・桁・NULL・既定値は取れます。型名と桁が別の列に分かれている点と、コメント（論理名）は含まれない点が pg_catalog との違いです。
SELECT table_name, ordinal_position AS no, column_name, data_type,
character_maximum_length AS len, numeric_precision AS prec, numeric_scale AS scale,
is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'items'
ORDER BY ordinal_position;
table_name | no | column_name | data_type | len | prec | scale | is_nullable | column_default
------------+----+-------------+-------------------+-----+------+-------+-------------+-------------------------
items | 1 | item_code | character | 6 | | | NO |
items | 2 | item_name | character varying | 100 | | | NO |
items | 3 | category | character varying | 10 | | | NO | 'PC'::character varying
items | 4 | unit_price | numeric | | 10 | 2 | YES |
items | 5 | note | text | | | | YES |
(5 rows)
索引一覧とCHECK制約を取り出す
SELECT t.relname AS "テーブル",
i.relname AS "索引名",
CASE WHEN x.indisprimary THEN '主キー'
WHEN x.indisunique THEN '一意'
ELSE '通常' END AS "種類",
(SELECT string_agg(a.attname, ', ' ORDER BY k.ord)
FROM unnest(x.indkey) WITH ORDINALITY AS k(attnum, ord)
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = k.attnum) AS "列"
FROM pg_index x
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_class t ON t.oid = x.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'public'
ORDER BY t.relname, i.relname;
テーブル | 索引名 | 種類 | 列
--------------+------------------------------+--------+---------------------------------
departments | departments_pkey | 主キー | dept_code
items | items_pkey | 主キー | item_code
members | members_email_key | 一意 | email
members | members_pkey | 主キー | member_id
reservations | reservations_item_period_idx | 通常 | item_code, start_date, end_date
reservations | reservations_member_idx | 通常 | member_id
reservations | reservations_pkey | 主キー | reservation_id
(7 rows)
主キーと UNIQUE 制約には、テーブル名_pkey、テーブル名_列名_key という名前の索引が自動で作られていることがわかります。
SELECT t.relname AS "テーブル", con.conname AS "制約名", pg_get_constraintdef(con.oid) AS "定義"
FROM pg_constraint con
JOIN pg_class t ON t.oid = con.conrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'public' AND con.contype = 'c'
ORDER BY t.relname, con.conname;
テーブル | 制約名 | 定義
--------------+---------------------------+--------------------------------------------------------------------------------------------------------------------------------------
items | items_category_check | CHECK (((category)::text = ANY ((ARRAY['PC'::character varying, 'MONITOR'::character varying, 'ROOM'::character varying])::text[])))
reservations | reservations_period_check | CHECK ((start_date <= end_date))
reservations | reservations_qty_check | CHECK (((qty >= 1) AND (qty <= 99)))
(3 rows)
qty BETWEEN 1 AND 99 と書いた制約が (qty >= 1) AND (qty <= 99) に、IN (...) が = ANY (ARRAY[...]) に書き換わって保存されています。定義書の備考には、読みやすい元の書き方で載せるとよいでしょう。
MySQLで定義書の表を作るSQL
題材のテーブル（MySQL版）
MySQL では、列の定義の中に COMMENT '...' を書けます。
CREATE DATABASE t358 CHARACTER SET utf8mb4;
USE t358;
CREATE TABLE departments (
dept_code CHAR(3) NOT NULL COMMENT '部署コード',
dept_name VARCHAR(50) NOT NULL COMMENT '部署名',
PRIMARY KEY (dept_code)
) COMMENT = '部署';
CREATE TABLE members (
member_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '利用者ID',
dept_code CHAR(3) NOT NULL COMMENT '部署コード',
member_name VARCHAR(40) NOT NULL COMMENT '氏名',
email VARCHAR(254) NOT NULL COMMENT 'メールアドレス',
is_active BOOLEAN NOT NULL DEFAULT TRUE COMMENT '有効フラグ',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時',
PRIMARY KEY (member_id),
UNIQUE KEY members_email_key (email),
CONSTRAINT members_dept_fk FOREIGN KEY (dept_code) REFERENCES departments (dept_code)
) COMMENT = '利用者';
CREATE TABLE items (
item_code CHAR(6) NOT NULL COMMENT '備品コード',
item_name VARCHAR(100) NOT NULL COMMENT '備品名',
category VARCHAR(10) NOT NULL DEFAULT 'PC' COMMENT '区分',
unit_price DECIMAL(10,2) COMMENT '購入単価',
note TEXT COMMENT '備考',
PRIMARY KEY (item_code),
CONSTRAINT items_category_check CHECK (category IN ('PC', 'MONITOR', 'ROOM'))
) COMMENT = '備品';
CREATE TABLE reservations (
reservation_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '予約ID',
item_code CHAR(6) NOT NULL COMMENT '備品コード',
member_id BIGINT NOT NULL COMMENT '利用者ID',
start_date DATE NOT NULL COMMENT '利用開始日',
end_date DATE NOT NULL COMMENT '利用終了日',
qty INT NOT NULL DEFAULT 1 COMMENT '数量',
status SMALLINT NOT NULL DEFAULT 0 COMMENT '状態（0:申請中 1:承認 9:取消）',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時',
PRIMARY KEY (reservation_id),
KEY reservations_item_period_idx (item_code, start_date, end_date),
KEY reservations_member_idx (member_id),
CONSTRAINT reservations_item_fk FOREIGN KEY (item_code) REFERENCES items (item_code),
CONSTRAINT reservations_member_fk FOREIGN KEY (member_id) REFERENCES members (member_id),
CONSTRAINT reservations_qty_check CHECK (qty BETWEEN 1 AND 99),
CONSTRAINT reservations_period_check CHECK (start_date <= end_date)
) COMMENT = '予約';
テーブル一覧と列一覧を取り出す
SELECT TABLE_NAME AS `物理名`, TABLE_COMMENT AS `論理名`, ENGINE AS `エンジン`, TABLE_COLLATION AS `照合順序`
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 't358' AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;
+--------------+-----------+--------------+--------------------+
| 物理名 | 論理名 | エンジン | 照合順序 |
+--------------+-----------+--------------+--------------------+
| departments | 部署 | InnoDB | utf8mb4_0900_ai_ci |
| items | 備品 | InnoDB | utf8mb4_0900_ai_ci |
| members | 利用者 | InnoDB | utf8mb4_0900_ai_ci |
| reservations | 予約 | InnoDB | utf8mb4_0900_ai_ci |
+--------------+-----------+--------------+--------------------+
MySQL は information_schema.COLUMNS にコメント（COLUMN_COMMENT）と桁つきの型（COLUMN_TYPE）が入っているので、1つのビューでほぼそろいます。外部キーは KEY_COLUMN_USAGE から取ります。
SELECT c.TABLE_NAME AS `テーブル`,
c.ORDINAL_POSITION AS `No`,
c.COLUMN_COMMENT AS `論理名`,
c.COLUMN_NAME AS `物理名`,
c.COLUMN_TYPE AS `型`,
IF(c.IS_NULLABLE = 'NO', '不可', '可') AS `NULL`,
IF(c.EXTRA LIKE '%auto_increment%', '自動採番', IFNULL(c.COLUMN_DEFAULT, '')) AS `既定値`,
IF(c.COLUMN_KEY = 'PRI', 'PK', '') AS `PK`,
IFNULL((SELECT GROUP_CONCAT(CONCAT(k.REFERENCED_TABLE_NAME, '.', k.REFERENCED_COLUMN_NAME) SEPARATOR ', ')
FROM information_schema.KEY_COLUMN_USAGE k
WHERE k.TABLE_SCHEMA = c.TABLE_SCHEMA AND k.TABLE_NAME = c.TABLE_NAME
AND k.COLUMN_NAME = c.COLUMN_NAME
AND k.REFERENCED_TABLE_NAME IS NOT NULL), '') AS `FK`
FROM information_schema.COLUMNS c
WHERE c.TABLE_SCHEMA = 't358'
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION;
+--------------+----+-------------------------------------------+----------------+---------------+--------+-------------------+----+-----------------------+
| テーブル | No | 論理名 | 物理名 | 型 | NULL | 既定値 | PK | FK |
+--------------+----+-------------------------------------------+----------------+---------------+--------+-------------------+----+-----------------------+
| departments | 1 | 部署コード | dept_code | char(3) | 不可 | | PK | |
| departments | 2 | 部署名 | dept_name | varchar(50) | 不可 | | | |
| items | 1 | 備品コード | item_code | char(6) | 不可 | | PK | |
| items | 2 | 備品名 | item_name | varchar(100) | 不可 | | | |
| items | 3 | 区分 | category | varchar(10) | 不可 | PC | | |
| items | 4 | 購入単価 | unit_price | decimal(10,2) | 可 | | | |
| items | 5 | 備考 | note | text | 可 | | | |
| members | 1 | 利用者ID | member_id | bigint | 不可 | 自動採番 | PK | |
| members | 2 | 部署コード | dept_code | char(3) | 不可 | | | departments.dept_code |
| members | 3 | 氏名 | member_name | varchar(40) | 不可 | | | |
| members | 4 | メールアドレス | email | varchar(254) | 不可 | | | |
| members | 5 | 有効フラグ | is_active | tinyint(1) | 不可 | 1 | | |
| members | 6 | 登録日時 | created_at | datetime | 不可 | CURRENT_TIMESTAMP | | |
| reservations | 1 | 予約ID | reservation_id | bigint | 不可 | 自動採番 | PK | |
| reservations | 2 | 備品コード | item_code | char(6) | 不可 | | | items.item_code |
| reservations | 3 | 利用者ID | member_id | bigint | 不可 | | | members.member_id |
| reservations | 4 | 利用開始日 | start_date | date | 不可 | | | |
| reservations | 5 | 利用終了日 | end_date | date | 不可 | | | |
| reservations | 6 | 数量 | qty | int | 不可 | 1 | | |
| reservations | 7 | 状態（0:申請中 1:承認 9:取消） | status | smallint | 不可 | 0 | | |
| reservations | 8 | 登録日時 | created_at | datetime | 不可 | CURRENT_TIMESTAMP | | |
+--------------+----+-------------------------------------------+----------------+---------------+--------+-------------------+----+-----------------------+
BOOLEAN と書いた is_active が tinyint(1)、既定値の TRUE が 1 になっています。MySQL では BOOLEAN が TINYINT(1) の別名として扱われるためです。
索引一覧とCHECK制約を取り出す
SELECT TABLE_NAME AS `テーブル`,
INDEX_NAME AS `索引名`,
CASE WHEN INDEX_NAME = 'PRIMARY' THEN '主キー'
WHEN NON_UNIQUE = 0 THEN '一意' ELSE '通常' END AS `種類`,
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ', ') AS `列`
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 't358'
GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE
ORDER BY TABLE_NAME, INDEX_NAME;
+--------------+------------------------------+-----------+---------------------------------+
| テーブル | 索引名 | 種類 | 列 |
+--------------+------------------------------+-----------+---------------------------------+
| departments | PRIMARY | 主キー | dept_code |
| items | PRIMARY | 主キー | item_code |
| members | members_dept_fk | 通常 | dept_code |
| members | members_email_key | 一意 | email |
| members | PRIMARY | 主キー | member_id |
| reservations | PRIMARY | 主キー | reservation_id |
| reservations | reservations_item_period_idx | 通常 | item_code, start_date, end_date |
| reservations | reservations_member_idx | 通常 | member_id |
+--------------+------------------------------+-----------+---------------------------------+
members に members_dept_fk という索引があります。これは DDL には書いていませんが、MySQL（InnoDB）が外部キーの列に自動で作った索引 です。PostgreSQL の結果には外部キーの列の索引は無かったので、定義書の索引一覧は実際のデータベースから取り出して確かめると確実です。
SELECT t.TABLE_NAME AS `テーブル`, t.CONSTRAINT_NAME AS `制約名`, c.CHECK_CLAUSE AS `定義`
FROM information_schema.TABLE_CONSTRAINTS t
JOIN information_schema.CHECK_CONSTRAINTS c
ON c.CONSTRAINT_SCHEMA = t.CONSTRAINT_SCHEMA AND c.CONSTRAINT_NAME = t.CONSTRAINT_NAME
WHERE t.TABLE_SCHEMA = 't358' AND t.CONSTRAINT_TYPE = 'CHECK'
ORDER BY t.TABLE_NAME, t.CONSTRAINT_NAME;
+--------------+---------------------------+-----------------------------------------------------------------------+
| テーブル | 制約名 | 定義 |
+--------------+---------------------------+-----------------------------------------------------------------------+
| items | items_category_check | (`category` in (_utf8mb4\'PC\',_utf8mb4\'MONITOR\',_utf8mb4\'ROOM\')) |
| reservations | reservations_period_check | (`start_date` <= `end_date`) |
| reservations | reservations_qty_check | (`qty` between 1 and 99) |
+--------------+---------------------------+-----------------------------------------------------------------------+
CSVとMarkdownの表に書き出すPython
SQLの結果をファイルに保存し、Python でテーブルごとの Markdown の表と、表計算ソフトで開ける CSV に変換します。データベースへの接続ライブラリは使わず、各データベースのコマンドが出力したファイルを読む形にしているので、Python の標準ライブラリだけで動きます。
まず、列一覧のSQLの結果をファイルにします。接続先やユーザーの指定は環境に合わせてください。
# PostgreSQL：CSVで出力
psql -d t358 --csv -f pg_columns.sql > columns_pg.csv
# MySQL：タブ区切りで出力
mysql --batch --default-character-set=utf8mb4 t358 < my_columns.sql > columns_mysql.tsv
変換するスクリプトです。
import csv
import sys
from itertools import groupby
def read_rows(path):
delimiter = "\t" if path.endswith(".tsv") else ","
with open(path, encoding="utf-8", newline="") as f:
return list(csv.DictReader(f, delimiter=delimiter))
def cell(value):
return (value or "").replace("|", "\|").replace("\n", " ")
def to_markdown(rows, key="テーブル"):
lines = []
for table, group in groupby(rows, key=lambda r: r[key]):
group = list(group)
headers = [h for h in group[0] if h != key]
lines.append(f"### {table}")
lines.append("")
lines.append("| " + " | ".join(headers) + " |")
lines.append("|" + "---|" * len(headers))
for r in group:
lines.append("| " + " | ".join(cell(r[h]) for h in headers) + " |")
lines.append("")
return "\n".join(lines)
def to_csv(rows, path):
with open(path, "w", encoding="utf-8-sig", newline="") as f: # BOMつきで表計算ソフトが文字コードを判別しやすい
writer = csv.DictWriter(f, fieldnames=list(rows[0]))
writer.writeheader()
writer.writerows(rows)
if __name__ == "__main__":
src, out_md, out_csv = sys.argv[1], sys.argv[2], sys.argv[3]
rows = read_rows(src)
with open(out_md, "w", encoding="utf-8") as f:
f.write(to_markdown(rows))
to_csv(rows, out_csv)
tables = sorted({r["テーブル"] for r in rows})
print(f"{src}: {len(tables)}テーブル {len(rows)}列 -> {out_md}, {out_csv}")
python3 make_docs.py columns_pg.csv tables_pg.md tables_pg_excel.csv
python3 make_docs.py columns_mysql.tsv tables_mysql.md tables_mysql_excel.csv
columns_pg.csv: 4テーブル 21列 -> tables_pg.md, tables_pg_excel.csv
columns_mysql.tsv: 4テーブル 21列 -> tables_mysql.md, tables_mysql_excel.csv
PostgreSQL から作った tables_pg.md のうち、予約テーブルの部分です。
### reservations
| No | 論理名 | 物理名 | 型 | NULL | 既定値 | PK | FK |
|---|---|---|---|---|---|---|---|
| 1 | 予約ID | reservation_id | bigint | 不可 | 自動採番 | PK | |
| 2 | 備品コード | item_code | character(6) | 不可 | | | items.item_code |
| 3 | 利用者ID | member_id | bigint | 不可 | | | members.member_id |
| 4 | 利用開始日 | start_date | date | 不可 | | | |
| 5 | 利用終了日 | end_date | date | 不可 | | | |
| 6 | 数量 | qty | integer | 不可 | 1 | | |
| 7 | 状態（0:申請中 1:承認 9:取消） | status | smallint | 不可 | 0 | | |
| 8 | 登録日時 | created_at | timestamp without time zone | 不可 | CURRENT_TIMESTAMP | | |
MySQL から作った tables_mysql.md のうち、利用者テーブルの部分です。
### members
| No | 論理名 | 物理名 | 型 | NULL | 既定値 | PK | FK |
|---|---|---|---|---|---|---|---|
| 1 | 利用者ID | member_id | bigint | 不可 | 自動採番 | PK | |
| 2 | 部署コード | dept_code | char(3) | 不可 | | | departments.dept_code |
| 3 | 氏名 | member_name | varchar(40) | 不可 | | | |
| 4 | メールアドレス | email | varchar(254) | 不可 | | | |
| 5 | 有効フラグ | is_active | tinyint(1) | 不可 | 1 | | |
| 6 | 登録日時 | created_at | datetime | 不可 | CURRENT_TIMESTAMP | | |
CSV は BOM つきの UTF-8（utf-8-sig）で書き出しています。表計算ソフトで開いたときに文字コードが判別されやすくなります。
型の書き方のDB別の違い早見表
同じ DDL を PostgreSQL 16 と MySQL 8.0 の両方で実行し、それぞれが型をどう表示するかを確かめた結果です。定義書をデータベースから逆生成すると、この表の「表示」の列の書き方で出てきます。
| DDLに書いた型 | PostgreSQL 16 の表示 | MySQL 8.0 の表示 |
|---|---|---|
| INTEGER | integer | int |
| BIGINT | bigint | bigint |
| SMALLINT | smallint | smallint |
| NUMERIC(10,2) | numeric(10,2) | decimal(10,2) |
| CHAR(3) | character(3) | char(3) |
| VARCHAR(50) | character varying(50) | varchar(50) |
| TEXT | text | text |
| BOOLEAN | boolean | tinyint(1) |
| DATE | date | date |
| TIME | time without time zone | time |
| TIMESTAMP | timestamp without time zone | timestamp |
| REAL | real | double |
| DOUBLE PRECISION | double precision | double |
片方のデータベースだけで使える書き方も確かめました。
| 書き方 | PostgreSQL 16 | MySQL 8.0 |
|---|---|---|
| 桁を省いた VARCHAR | 作成できる（表示は character varying） | 構文エラー（ERROR 1064） |
| DATETIME | エラー（type "datetime" does not exist） | 作成できる。DATETIME(6) で小数秒6桁 |
| TIMESTAMPTZ | 作成できる（表示は timestamp with time zone） | ― |
| SERIAL | integer 型＋既定値 nextval('テーブル名_列名_seq'::regclass) になる |
― |
| JSONB、UUID、BYTEA | 作成できる | ― |
| INT(11) | ― | 作成できるが表示は int。警告1681（表示幅の指定は非推奨）が出る |
| TINYINT UNSIGNED、ENUM('PC','ROOM')、BLOB、LONGTEXT、JSON | ― | 作成できる |
| 自動採番 | GENERATED ALWAYS AS IDENTITY | AUTO_INCREMENT |
| 論理名（コメント） | COMMENT ON TABLE／COMMENT ON COLUMN 文 | 列・テーブルの定義に COMMENT を書く |
定義書の「型」は、採用するデータベースの表記にそろえるか、VARCHAR(50)・NUMERIC(10,2) のような短い表記に統一して凡例に書く と読みやすくなります。たとえば PostgreSQL の timestamp without time zone は、定義書では TIMESTAMP と書き、凡例に「タイムゾーンなし」と添える方法があります。
手書きと逆生成の使い分け
| 場面 | おすすめの進め方 |
|---|---|
| 設計の初期（テーブルがまだ無い） | テンプレートに手で書き、レビューする |
| DDL を作って開発用データベースに反映した後 | SQLで逆生成し、手書きの定義書と見比べる |
| 運用中のシステムの定義書を整える | 逆生成した表を土台にして、備考と概要を人が書き足す |
| 定義書を常に最新にしておきたい | 論理名をコメントで管理し、変更のたびに逆生成する |
逆生成で取り出せるのは「いまのデータベースがどうなっているか」です。なぜその型にしたのか、コード値が何を意味するのかといった設計の意図は、備考として人が書く 部分になります。コード値の意味をコメントに入れておけば（例：状態（0:申請中 1:承認 9:取消））、逆生成の結果にもそのまま出てきます。
テーブル定義書のチェックリスト
| No | 確認すること |
|---|---|
| 1 | テーブル一覧と各テーブルの定義の数が一致している |
| 2 | すべてのテーブルと列に論理名がある |
| 3 | 同じ意味の列は、テーブルが違っても論理名・物理名・型・桁が同じ |
| 4 | 型の表記が定義書全体で統一されている |
| 5 | NULL の凡例（不可／可）が書いてある |
| 6 | 既定値は固定値・関数・自動採番の区別がつく |
| 7 | 主キーがすべてのテーブルにあり、複合主キーは順番がわかる |
| 8 | 外部キーの参照先と型・桁が一致している |
| 9 | 索引一覧に、列の並び順と用途が書いてある |
| 10 | コード値の意味と CHECK 制約が備考にある |
| 11 | 実際のデータベースから逆生成した結果と食い違いがない |
よくある質問
Q. テーブル定義書とER図はどう使い分けますか？ A. ER図はテーブルどうしの関係を見渡すための図、テーブル定義書は1テーブルの列を細かく確かめるための表です。関係はER図、型・桁・NULL・既定値は定義書、と役割を分けて両方を用意します。
Q. 論理名をコメントに入れていないデータベースでも逆生成できますか？ A. できます。論理名の列が空になるだけで、物理名・型・NULL・既定値・PK・FK・索引は取り出せます。取り出した表に論理名を書き足し、あわせて COMMENT ON COLUMN（MySQL では列定義の COMMENT）で登録しておくと、次回からは論理名も取り出せます。
Q. 「桁」は型と分けて書くべきですか？ A. どちらでも構いませんが、定義書の中で統一します。逆生成の結果は VARCHAR(50) のように型と一体で出てくるので、型に含める書き方にすると手直しが少なくなります。桁を別の列にしたい場合は、PostgreSQL・MySQL とも information_schema.columns の character_maximum_length、numeric_precision、numeric_scale から取れます。
Q. 定義書とデータベースが食い違っていたら、どちらを正にしますか？ A. 動いているデータベースが現在の事実です。逆生成した結果を見て、意図した変更なら定義書を直し、意図していない差分ならデータベース側を直します。変更のたびに逆生成して見比べる手順にしておくと、差分が小さいうちに気づけます。
まとめ
- テーブル定義書は「テーブル一覧＋1テーブル1枚」 で、列一覧・索引一覧・制約一覧を持たせる
- 列一覧の項目は No・論理名・物理名・型・桁・NULL・既定値・PK・FK・備考 の10個が基本
- 論理名をコメントに入れておく と、定義書の表をSQLでそのまま取り出せる
- PostgreSQL は pg_catalog と format_type、MySQL は information_schema.COLUMNS が逆生成の中心になる
- 同じ DDL でも表示される型はデータベースごとに違う（BOOLEAN が MySQL では tinyint(1) など）ので、定義書の表記を決めて凡例に書く
- 外部キーの列の索引は MySQL では自動で作られ、PostgreSQL では作られない。索引一覧は実際のデータベースから取り出して確かめる
- 設計の意図とコード値の意味は人が書く。逆生成は土台づくりと食い違いの確認に使う
本記事の内容は、ご自由にお使いください。
ご利用の際は、出典として本ページへのリンクを記載いただけますようお願いします。
（記載例）出典：株式会社RJC「テーブル定義書の書き方とテンプレート DDLから自動生成するSQL」
株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。
WE ARE HIRING
RJCで一緒に開発しながら、
成長を楽しみませんか？
RJCは、Web・モバイル・AIを活用した開発プロジェクトで、テックリードやPM・PMOも活躍するシステム開発会社です。会社を知る、待遇を確かめる、話を聞いてみる。気になるところ見てみてください！
- 127日年間休日
- 12時間平均残業時間
- 毎日ガチャ遊びココロも大切にする福利厚生。アマギフなどの賞品ラインナップ！
ほかにも、チケットレストラン、書籍読み放題、2年ごとの慰労報奨（休暇 or 金一封）、11期連続の黒字決算。
