- AI ⚙️
- データベース
生成AIでDB設計|テーブル設計とER図を作らせるプロンプト集
生成AIにDB設計を依頼するときは、一度に全部を作らせず、エンティティ抽出・ER図・テーブル定義・正規化レビュー・DDLの5段階に分けて依頼します。 段階ごとに人が確認して次へ進むと、手戻りの範囲が1段階に収まります。
この記事では、段階ごとにそのまま貼れるプロンプトのテンプレートと、架空の図書貸出システムを題材にした成果物の完成形(MermaidのER図、テーブル定義表、DDL)を載せます。完成形はこの記事のために作成し、DDLをPostgreSQLとMySQLの両方で実行して確かめたものです。SQLの問い合わせを書かせる方法、正規形そのものの解説、ER図の記号の読み方は範囲から外し、設計を依頼する手順に絞ります。
確認環境:Ubuntu 24.04 / PostgreSQL 16.15 / MySQL 8.0.46 / Mermaid 12.1.0(@mermaid-js/mermaid-cli 12.0.0 とChromiumでER図の描画を確認)。確認日 2026-10-06。プロンプトは自作のテンプレートで、モデルの出力例は載せていません。
この記事の結論
- 依頼は5段階に分ける:エンティティ抽出 → ER図 → テーブル定義 → 正規化レビュー → DDL。
- 主キーの方針、削除の方式、履歴の持ち方は人が先に決めてプロンプトに書く。
- DDLは対象のDB製品と版を指定して書かせ、受け取ったら空のDBで実行して確かめる。
- 既存のDDLがある場合は、作らせるのではなくレビューを依頼するプロンプトを使う。
5段階の早見表
| 段階 | 依頼すること | 渡す情報 | 受け取る成果物 | 人が確認すること |
|---|---|---|---|---|
| 1. エンティティ抽出 | 業務の文章から管理対象を洗い出す | 業務の説明、利用者、主な操作 | エンティティ一覧と質問リスト | 抜けている対象、用語の食い違い |
| 2. ER図 | エンティティどうしの関連と多重度を決める | 確定したエンティティ一覧 | MermaidのER図 | 1対多・多対多の向き、中間テーブル |
| 3. テーブル定義 | 列・型・制約を表にする | ER図、設計方針(主キー・削除・履歴) | テーブル定義表 | 桁数、NULLの可否、一意にする列 |
| 4. 正規化レビュー | 繰り返し・重複・導出できる列を指摘させる | テーブル定義表 | 指摘の一覧と修正案 | 採用する指摘と、あえて残す重複 |
| 5. DDL | CREATE TABLE文を書かせる | 確定したテーブル定義、DB製品と版 | 実行できるDDL | 空のDBで実行、制約に反する行で確認 |
本記事の内容は、ご自由にお使いください。
ご利用の際は、出典として本ページへのリンクを記載いただけますようお願いします。
(記載例)出典:株式会社RJC「生成AIでDB設計|テーブル設計とER図を作らせるプロンプト集」
ゼロから作らせる場合と、既存DDLをレビューさせる場合の違い
| 観点 | ゼロから作らせる | 既存DDLをレビューさせる |
|---|---|---|
| 渡すもの | 業務の説明と設計方針 | 現在のDDL、困っていること、変更できる範囲 |
| 依頼の形 | 段階ごとに成果物を作る | 指摘を重要度つきで挙げる |
| 向く場面 | 新しいシステム、試作 | 動いているDBの見直し、引き継ぎ |
| 気を付ける点 | 業務の前提が足りないと一般的な形になる | 「全部作り直す案」が出やすいので、変更範囲を書く |
| 確認の方法 | 空のDBでDDLを実行 | 移行手順とデータへの影響を別に確認 |
AIに任せてよい範囲と、人が決める項目
| 項目 | 決める人 | 理由 |
|---|---|---|
| 主キーの方針(連番、UUID、業務コード) | 人 | 他システムとの連携、採番の規則、並び順に関わる |
| 削除の方式(物理削除、論理削除、退避テーブル) | 人 | 業務で「消したデータを後で見るか」によって決まる |
| 履歴の持ち方(上書き、履歴テーブル、有効期間) | 人 | 過去の時点の状態を再現する必要があるかで決まる |
| 命名規則(単数・複数、接頭辞、略語) | 人が決めて渡す | 既存のテーブルとそろえる必要がある |
| エンティティの洗い出しの下書き | AI(人が確認) | 文章からの抽出は速く、抜けの質問も出せる |
| 列の候補と型の下書き | AI(人が確認) | 一般的な項目は網羅しやすい。桁数は業務で確かめる |
| 正規化の指摘 | AI(人が採否を決める) | 規則に沿った点検は得意。残す重複の判断は業務次第 |
| DDLの清書、DB製品ごとの書き分け | AI(実行して確認) | 機械的な変換。実行すれば正否が分かる |
どちらを使うかの判断基準
| 条件 | 選ぶ進め方 |
|---|---|
| テーブルがまだ無い | 5段階で作らせる |
| DDLがあり、データも入っている | レビューを依頼する(後半のプロンプト) |
| 業務の説明がまだ固まっていない | 段階1だけを行い、質問リストに答えてから進む |
| テーブルが3つ以下の小さな試作 | 段階1〜3をまとめて依頼し、段階4・5は分ける |
| 別のDB製品へ移したい | 段階5だけを、移行先の製品と版を指定して依頼 |
最初に渡す共通の前提
5段階を通して使う前提です。会話の最初に1度貼ります。{ } の部分を書き換えてください。
あなたはデータベース設計の担当者です。これから段階を分けて設計を進めます。
各段階で、指定した成果物だけを出力してください。次の段階には、私が「次へ」と言うまで進まないでください。
# 対象システム
{図書貸出システム。社内の図書コーナーにある本を、社員が借りて返す}
# 利用者と主な操作
{利用者:社員(借りる・返す・予約する)、管理担当(本の登録、貸出状況の確認)}
# 設計方針(こちらで決めたこと。変えないでください)
- 主キー:{各テーブルに連番の代理キーを置く。業務で使う番号は別の列にして一意制約を付ける}
- 削除:{物理削除はしない。本は status 列、利用者は is_active 列で扱う}
- 履歴:{貸出は1回ごとに1行を残す。返却は行を消さず、返却日を入れる}
- 命名:{テーブルは英語の複数形、列はスネークケース、日付は _on、日時は _at で終える}
- DB製品:{PostgreSQL 16}
# 進め方
- 分からない点は推測で埋めず、質問として挙げてください。
- 指定していない機能(権限管理、通知など)は追加しないでください。
段階1:エンティティ抽出のプロンプト
段階1です。上の対象システムから、エンティティ(管理する対象)を洗い出してください。
# 出力形式
1. エンティティ一覧(表):エンティティ名(日本語)/英語のテーブル名の案/1行の説明/代表的な項目を3〜5個
2. 判断に迷った点(箇条書き):「1冊ごとに管理するか、書名ごとに管理するか」のように、設計が分かれる点
3. 私への質問(番号つき、5個まで)
# 条件
- 画面や帳票ではなく、保存するデータの単位で挙げてください。
- 同じ内容の本が複数冊ある場合を考慮してください。
- この段階では列の型や制約は書かないでください。
図書貸出システムで確定させたエンティティは次の6つです。
| エンティティ | テーブル名 | 説明 |
|---|---|---|
| 分類 | categories | 本の分類(技術書、読み物など) |
| 書誌 | books | 書名・著者などの、本の内容についての情報 |
| 蔵書 | book_copies | 棚にある1冊ごとの本。同じ書誌に複数ありうる |
| 利用者 | members | 本を借りる社員 |
| 貸出 | loans | 1冊を1人に貸した1回分の記録 |
| 予約 | reservations | 書誌に対する順番待ち |
書誌と蔵書を分けるかどうかが、この題材で設計が分かれる点です。同じ本が2冊あるときに「どちらの1冊を貸したか」を記録するため、分けています。
段階2:ER図のプロンプト
段階2です。確定したエンティティ一覧をもとに、ER図を Mermaid の erDiagram で書いてください。
# 確定したエンティティ
{段階1で確定した一覧を貼る}
# 出力形式
1. Mermaid のコードブロック1つ(関連の線を先に、各エンティティの項目を後に書く)
2. 関連ごとの説明(表):親/子/多重度/そう判断した理由
# 条件
- 多対多の関連は中間テーブルに分解してください。
- 各エンティティには主キー(PK)、外部キー(FK)、一意にする項目(UK)を付けてください。
- 型は大まかでかまいません(int、varchar、date など)。桁数は次の段階で決めます。
- 関連のラベルは日本語の動詞で書いてください。
完成形のER図です。
erDiagram
categories ||--o{ books : "分類する"
books ||--|{ book_copies : "所蔵する"
books ||--o{ reservations : "予約される"
members ||--o{ loans : "借りる"
members ||--o{ reservations : "予約する"
book_copies ||--o{ loans : "貸し出される"
categories {
int category_id PK
varchar name UK "分類名"
}
books {
bigint book_id PK
char isbn UK "13桁"
varchar title "書名"
varchar author "著者"
int category_id FK
date published_on "出版日"
}
book_copies {
bigint copy_id PK
bigint book_id FK
varchar copy_code UK "管理ラベル"
varchar status "available / on_loan / retired"
date acquired_on "受入日"
}
members {
bigint member_id PK
varchar member_code UK "利用者番号"
varchar name "氏名"
varchar email UK
boolean is_active "利用できるか"
date joined_on "登録日"
}
loans {
bigint loan_id PK
bigint copy_id FK
bigint member_id FK
date loaned_on "貸出日"
date due_on "返却期限"
date returned_on "返却日(未返却はNULL)"
}
reservations {
bigint reservation_id PK
bigint book_id FK
bigint member_id FK
timestamp reserved_at "予約日時"
varchar status "waiting / ready / cancelled / done"
}
段階3:テーブル定義のプロンプト
段階3です。ER図をもとに、テーブル定義を表にしてください。
# 出力形式(テーブルごとに1つの表)
列名/型(桁数つき)/NULL可否/キー(PK・FK・UK)/既定値/制約/説明
# 条件
- 型は {PostgreSQL 16} の型名で書いてください。
- 設計方針の主キー・削除・履歴に従ってください。
- 状態を表す列は、取りうる値をすべて列挙し、CHECK制約にしてください。
- 日付どうしの前後関係など、行の中で成り立つべき条件があれば制約の欄に書いてください。
- 「同時に1件だけ」のような、複数行にまたがる条件があれば、表の下に別に書いてください。
- 桁数の根拠が業務で決まるものは、根拠を「要確認」として挙げてください。
完成形のテーブル定義です(型はPostgreSQLの表記)。
| テーブル | 列名 | 型 | NULL | キー | 既定値・制約 | 説明 |
|---|---|---|---|---|---|---|
| categories | category_id | integer | 不可 | PK | 自動採番 | 分類ID |
| categories | name | varchar(50) | 不可 | UK | 分類名 | |
| books | book_id | bigint | 不可 | PK | 自動採番 | 書誌ID |
| books | isbn | char(13) | 可 | UK | ISBN(無い本はNULL) | |
| books | title | varchar(200) | 不可 | 書名 | ||
| books | author | varchar(100) | 不可 | 著者 | ||
| books | category_id | integer | 不可 | FK | categories を参照 | 分類 |
| books | published_on | date | 可 | 出版日 | ||
| books | created_at | timestamptz | 不可 | 現在日時 | 登録日時 | |
| book_copies | copy_id | bigint | 不可 | PK | 自動採番 | 蔵書ID |
| book_copies | book_id | bigint | 不可 | FK | books を参照 | 書誌 |
| book_copies | copy_code | varchar(20) | 不可 | UK | 本に貼る管理ラベル | |
| book_copies | status | varchar(10) | 不可 | 既定値 available。available / on_loan / retired のいずれか | 状態 | |
| book_copies | acquired_on | date | 不可 | 受入日 | ||
| members | member_id | bigint | 不可 | PK | 自動採番 | 利用者ID |
| members | member_code | varchar(10) | 不可 | UK | 利用者番号 | |
| members | name | varchar(100) | 不可 | 氏名 | ||
| members | varchar(254) | 不可 | UK | メールアドレス | ||
| members | is_active | boolean | 不可 | 既定値 true | 利用できるか | |
| members | joined_on | date | 不可 | 登録日 | ||
| loans | loan_id | bigint | 不可 | PK | 自動採番 | 貸出ID |
| loans | copy_id | bigint | 不可 | FK | book_copies を参照 | 貸した1冊 |
| loans | member_id | bigint | 不可 | FK | members を参照 | 借りた人 |
| loans | loaned_on | date | 不可 | 貸出日 | ||
| loans | due_on | date | 不可 | 貸出日以降 | 返却期限 | |
| loans | returned_on | date | 可 | NULLまたは貸出日以降 | 返却日(返却前はNULL) | |
| loans | created_at | timestamptz | 不可 | 現在日時 | 登録日時 | |
| reservations | reservation_id | bigint | 不可 | PK | 自動採番 | 予約ID |
| reservations | book_id | bigint | 不可 | FK | books を参照 | 予約した書誌 |
| reservations | member_id | bigint | 不可 | FK | members を参照 | 予約した人 |
| reservations | reserved_at | timestamptz | 不可 | 現在日時 | 予約日時 | |
| reservations | status | varchar(10) | 不可 | 既定値 waiting。waiting / ready / cancelled / done のいずれか | 状態 |
複数行にまたがる条件:同じ蔵書について、返却前の貸出は同時に1件だけ。列の制約では書けないため、DDLの段階で一意インデックスとして表します。
段階4:正規化レビューのプロンプト
段階4です。次のテーブル定義を、第1〜第3正規形の観点でレビューしてください。作り直しではなく、指摘だけを出してください。
# テーブル定義
{段階3の表を貼る}
# 出力形式(表)
番号/対象のテーブルと列/該当する観点/何が起きうるか(更新・追加・削除のときの具体例)/修正案/重要度(高・中・低)
# 観点
- 1つの列に複数の値が入っていないか(カンマ区切り、連番つきの列名)
- 複合キーの一部だけで決まる列がないか
- 主キー以外の列から決まる列がないか(名称の重複保持など)
- 他の列から計算できる値を保存していないか
- 同じ意味の列が複数のテーブルにないか
# 条件
- 指摘が無い観点は「該当なし」と書いてください。
- あえて重複を持つ設計(その時点の値を残す目的など)と思われる箇所は、指摘ではなく「確認」として分けてください。
- 設計方針(主キー・削除・履歴)は変えないでください。
この題材で人が判断した点は2つあります。
| 確認された点 | 判断 | 理由 |
|---|---|---|
| book_copies.status の「on_loan」は、loans に返却前の行があるかどうかから導出できる | 残す | 棚の一覧を出すたびに loans を調べずに済む。貸出と返却の処理で同時に更新する決まりにする |
| loans に書名を持たせるか | 持たせない | 書名は book_copies → books とたどれば得られ、書名を直したときに食い違いが起きない |
段階5:DDLのプロンプト
段階5です。確定したテーブル定義から、{PostgreSQL 16} で実行できる DDL を書いてください。
# テーブル定義
{段階3・4で確定した表を貼る}
# 条件
- CREATE TABLE は、参照される側のテーブルから順に並べてください。
- 主キーは自動採番にしてください(PostgreSQL は GENERATED ALWAYS AS IDENTITY、MySQL は AUTO_INCREMENT)。
- 外部キー、一意制約、CHECK制約を省かないでください。
- 「同じ蔵書について返却前の貸出は1件だけ」を、DBの制約またはインデックスで表してください。
- 外部キーの列には、必要に応じてインデックスを付けてください。
- DROP文、サンプルデータ、権限の設定は含めないでください。
- 製品に依存する書き方を使った箇所は、DDLの後に一覧で挙げてください。
完成形のDDL(PostgreSQL 16)
-- PostgreSQL 16 用
CREATE TABLE categories (
category_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name varchar(50) NOT NULL UNIQUE
);
CREATE TABLE books (
book_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
isbn char(13) UNIQUE,
title varchar(200) NOT NULL,
author varchar(100) NOT NULL,
category_id integer NOT NULL REFERENCES categories (category_id),
published_on date,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE book_copies (
copy_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
book_id bigint NOT NULL REFERENCES books (book_id),
copy_code varchar(20) NOT NULL UNIQUE,
status varchar(10) NOT NULL DEFAULT 'available'
CHECK (status IN ('available', 'on_loan', 'retired')),
acquired_on date NOT NULL
);
CREATE TABLE members (
member_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
member_code varchar(10) NOT NULL UNIQUE,
name varchar(100) NOT NULL,
email varchar(254) NOT NULL UNIQUE,
is_active boolean NOT NULL DEFAULT true,
joined_on date NOT NULL
);
CREATE TABLE loans (
loan_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
copy_id bigint NOT NULL REFERENCES book_copies (copy_id),
member_id bigint NOT NULL REFERENCES members (member_id),
loaned_on date NOT NULL,
due_on date NOT NULL,
returned_on date,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT loans_due_check CHECK (due_on >= loaned_on),
CONSTRAINT loans_returned_check CHECK (returned_on IS NULL OR returned_on >= loaned_on)
);
-- 同じ1冊について「返却前の貸出」は1件だけ(部分一意インデックス)
CREATE UNIQUE INDEX loans_open_copy_key ON loans (copy_id) WHERE returned_on IS NULL;
CREATE INDEX loans_member_idx ON loans (member_id);
CREATE TABLE reservations (
reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
book_id bigint NOT NULL REFERENCES books (book_id),
member_id bigint NOT NULL REFERENCES members (member_id),
reserved_at timestamptz NOT NULL DEFAULT now(),
status varchar(10) NOT NULL DEFAULT 'waiting'
CHECK (status IN ('waiting', 'ready', 'cancelled', 'done'))
);
CREATE INDEX reservations_book_idx ON reservations (book_id, reserved_at);
完成形のDDL(MySQL 8.0)
-- MySQL 8.0 用
CREATE TABLE categories (
category_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE books (
book_id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
isbn char(13) UNIQUE,
title varchar(200) NOT NULL,
author varchar(100) NOT NULL,
category_id int NOT NULL,
published_on date,
created_at datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
CONSTRAINT books_category_fk FOREIGN KEY (category_id) REFERENCES categories (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE book_copies (
copy_id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
book_id bigint NOT NULL,
copy_code varchar(20) NOT NULL UNIQUE,
status varchar(10) NOT NULL DEFAULT 'available',
acquired_on date NOT NULL,
CONSTRAINT book_copies_book_fk FOREIGN KEY (book_id) REFERENCES books (book_id),
CONSTRAINT book_copies_status_check CHECK (status IN ('available', 'on_loan', 'retired'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE members (
member_id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
member_code varchar(10) NOT NULL UNIQUE,
name varchar(100) NOT NULL,
email varchar(254) NOT NULL UNIQUE,
is_active boolean NOT NULL DEFAULT true,
joined_on date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE loans (
loan_id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
copy_id bigint NOT NULL,
member_id bigint NOT NULL,
loaned_on date NOT NULL,
due_on date NOT NULL,
returned_on date,
created_at datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
CONSTRAINT loans_copy_fk FOREIGN KEY (copy_id) REFERENCES book_copies (copy_id),
CONSTRAINT loans_member_fk FOREIGN KEY (member_id) REFERENCES members (member_id),
CONSTRAINT loans_due_check CHECK (due_on >= loaned_on),
CONSTRAINT loans_returned_check CHECK (returned_on IS NULL OR returned_on >= loaned_on),
-- 同じ1冊について「返却前の貸出」は1件だけ(式インデックス。返却済みの行は NULL になり重複可)
UNIQUE KEY loans_open_copy_key ((CASE WHEN returned_on IS NULL THEN copy_id END))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE reservations (
reservation_id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
book_id bigint NOT NULL,
member_id bigint NOT NULL,
reserved_at datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
status varchar(10) NOT NULL DEFAULT 'waiting',
CONSTRAINT reservations_book_fk FOREIGN KEY (book_id) REFERENCES books (book_id),
CONSTRAINT reservations_member_fk FOREIGN KEY (member_id) REFERENCES members (member_id),
CONSTRAINT reservations_status_check CHECK (status IN ('waiting', 'ready', 'cancelled', 'done')),
KEY reservations_book_idx (book_id, reserved_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
どちらも空のデータベースで実行し、エラー無く作成できました。
制約が働くことを確かめる
DDLを受け取ったら、制約に反する行をわざと入れて、止まることを確かめます。 次のSQLを両方のDBで実行しました。
INSERT INTO categories (name) VALUES ('技術書'), ('読み物');
INSERT INTO books (isbn, title, author, category_id, published_on) VALUES ('9780000000001', 'はじめてのSQL', '山田 太郎', 1, '2024-04-01');
INSERT INTO book_copies (book_id, copy_code, acquired_on) VALUES (1, 'C-0001', '2024-05-01'), (1, 'C-0002', '2024-05-01');
INSERT INTO members (member_code, name, email, joined_on) VALUES ('M0001', '佐藤 花子', 'hanako@example.com', '2025-04-01'), ('M0002', '鈴木 次郎', 'jiro@example.com', '2025-04-01');
INSERT INTO loans (copy_id, member_id, loaned_on, due_on) VALUES (1, 1, '2026-10-01', '2026-10-15');
-- (1) 同じ1冊を返却前にもう一度貸し出す
INSERT INTO loans (copy_id, member_id, loaned_on, due_on) VALUES (1, 2, '2026-10-02', '2026-10-16');
-- (2) 返却期限が貸出日より前
INSERT INTO loans (copy_id, member_id, loaned_on, due_on) VALUES (2, 2, '2026-10-02', '2026-10-01');
-- (3) 決めていない状態の値
UPDATE book_copies SET status = 'lost' WHERE copy_id = 2;
-- (4) 存在しない利用者
INSERT INTO loans (copy_id, member_id, loaned_on, due_on) VALUES (2, 99, '2026-10-02', '2026-10-16');
-- (5) 返却してから、同じ1冊をもう一度貸し出す
UPDATE loans SET returned_on = '2026-10-05' WHERE loan_id = 1;
INSERT INTO loans (copy_id, member_id, loaned_on, due_on) VALUES (1, 2, '2026-10-06', '2026-10-20');
SELECT loan_id, copy_id, member_id, loaned_on, due_on, returned_on FROM loans ORDER BY loan_id;
PostgreSQL 16 の結果((1)〜(4) のエラーと、最後のSELECT):
ERROR: duplicate key value violates unique constraint "loans_open_copy_key"
DETAIL: Key (copy_id)=(1) already exists.
ERROR: new row for relation "loans" violates check constraint "loans_due_check"
DETAIL: Failing row contains (3, 2, 2, 2026-10-02, 2026-10-01, null, 2026-10-05 17:10:39.002708+00).
ERROR: new row for relation "book_copies" violates check constraint "book_copies_status_check"
DETAIL: Failing row contains (2, 1, C-0002, lost, 2024-05-01).
ERROR: insert or update on table "loans" violates foreign key constraint "loans_member_id_fkey"
DETAIL: Key (member_id)=(99) is not present in table "members".
UPDATE 1
INSERT 0 1
loan_id | copy_id | member_id | loaned_on | due_on | returned_on
---------+---------+-----------+------------+------------+-------------
1 | 1 | 1 | 2026-10-01 | 2026-10-15 | 2026-10-05
5 | 1 | 2 | 2026-10-06 | 2026-10-20 |
(2 rows)
MySQL 8.0 の結果(データベース名は appdb に置き換えています):
ERROR 1062 (23000) at line 7: Duplicate entry '1' for key 'loans.loans_open_copy_key'
ERROR 3819 (HY000) at line 9: Check constraint 'loans_due_check' is violated.
ERROR 3819 (HY000) at line 11: Check constraint 'book_copies_status_check' is violated.
ERROR 1452 (23000) at line 13: Cannot add or update a child row: a foreign key constraint fails (`appdb`.`loans`, CONSTRAINT `loans_member_fk` FOREIGN KEY (`member_id`) REFERENCES `members` (`member_id`))
+---------+---------+-----------+------------+------------+-------------+
| loan_id | copy_id | member_id | loaned_on | due_on | returned_on |
+---------+---------+-----------+------------+------------+-------------+
| 1 | 1 | 1 | 2026-10-01 | 2026-10-15 | 2026-10-05 |
| 4 | 1 | 2 | 2026-10-06 | 2026-10-20 | NULL |
+---------+---------+-----------+------------+------------+-------------+
4つの制約がどちらのDBでも働き、返却後の再貸出((5))は通りました。
PostgreSQLとMySQLの型・書き方の違い早見表
今回のDDLで書き分けた箇所です。右端は、相手側の書き方をそのまま流したときに出たエラーです。
| 用途 | PostgreSQL 16 | MySQL 8.0 | 相手側の書き方を流した結果 |
|---|---|---|---|
| 自動採番の主キー | bigint GENERATED ALWAYS AS IDENTITY |
bigint NOT NULL AUTO_INCREMENT |
MySQL:ERROR 1064(near 'IDENTITY PRIMARY KEY)')/PostgreSQL:syntax error at or near "AUTO_INCREMENT" |
| 日時 | timestamptz |
datetime(6) |
MySQL:ERROR 1064(near 'timestamptz)')/PostgreSQL:type "datetime" does not exist |
| 現在日時の既定値 | DEFAULT now() |
DEFAULT CURRENT_TIMESTAMP(6) |
— |
| 真偽値 | boolean |
boolean と書ける(定義を表示すると tinyint(1)) |
どちらも通る |
| 外部キー | 列の定義に REFERENCES を書ける |
CONSTRAINT ... FOREIGN KEY をテーブルの制約として書く |
MySQLで列に REFERENCES を書くとエラー無く通るが、外部キーは作られない(次の節) |
| 条件つきの一意 | CREATE UNIQUE INDEX ... WHERE 条件(部分インデックス) |
UNIQUE KEY 名前 ((CASE WHEN 条件 THEN 列 END))(式インデックス) |
MySQL:ERROR 1064(near 'WHERE r IS NULL')/PostgreSQL:テーブル制約の UNIQUE ((式)) は syntax error at or near "(" |
| CHECK制約 | 使える | 使える(違反は ERROR 3819) | どちらも通る |
| 文字コード・エンジン | 指定不要(データベース側の設定) | ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 を付けた |
— |
varchar(n)、char(n)、date、bigint |
同じ表記 | 同じ表記 | どちらも通る |
PostgreSQLの部分インデックスは、公式ドキュメントで「条件を満たす行の中だけで一意性を保証し、満たさない行は制約しない」使い方として説明されています。
確かめて分かったこと
- MySQLは、列の定義に書いた REFERENCES をエラー無く受け付けるが、外部キーを作らない。
CREATE TABLE c (id int PRIMARY KEY, p_id int NOT NULL REFERENCES p (id))を実行すると成功し、親に無い値 999 の行も入りました。information_schema.REFERENTIAL_CONSTRAINTS で数えると外部キーは0件でした。PostgreSQL向けのDDLをMySQLへ流すときは、外部キーをテーブルの制約に書き直します。 - 「返却前の貸出は1件だけ」は、製品ごとに書き方が違う。 PostgreSQLは部分インデックス、MySQL 8.0は CASE式を使った式インデックスで表せました。返却済みの行は式の値がNULLになり、何件でも入ります。
- エラーになった INSERT でも採番は進む。 最後に入った行の loan_id は、PostgreSQLで5、MySQLで4でした。連番が飛ぶことを前提に、業務で見せる番号は別の列にします。
- MySQLの boolean は tinyint(1) として保存される。
SHOW CREATE TABLE membersではis_active tinyint(1) NOT NULL DEFAULT '1'と表示されました。 - MermaidのER図は、項目の説明に日本語を書いても描画できた。 説明は二重引用符で囲みます。関連のラベルも日本語で描画されました。
- PostgreSQLの外部キー名は自動で付く。 列に書いた REFERENCES の制約名は
loans_member_id_fkeyになりました。MySQL側で付けた名前(loans_member_fk)とそろえたい場合は、CONSTRAINT で名前を指定します。
既存のDDLをレビューさせるプロンプト
次のDDLをレビューしてください。作り直しの案ではなく、指摘の一覧を出してください。
# DB製品と版
{MySQL 8.0}
# 現在のDDL
{SHOW CREATE TABLE や pg_dump --schema-only の結果を貼る}
# 困っていること・背景
{例:同じ本が同時に2人へ貸し出された記録が見つかった}
# 変更できる範囲
{例:列やインデックスの追加は可。既存の列名の変更とテーブルの分割は不可}
# 出力形式(表)
番号/対象(テーブル・列・制約)/指摘の内容/起きうること/修正のためのSQL案/既存データへの影響/重要度(高・中・低)
# 観点
- 主キー、外部キー、一意制約、NOT NULL の不足
- 型と桁数(日時の型、真偽値、文字数)
- 状態の列が取りうる値の制約
- 複数行にまたがる条件(同時に1件だけ、期間の重なり)
- 正規化(重複して持つ列、導出できる列)
- 命名のゆれ
# 条件
- 変更できる範囲の外になる指摘は、「範囲外」として分けて挙げてください。
- 修正のSQLは、既存データが制約に反している場合の確認用SELECTを先に付けてください。
- 推測した点は「推測」と明記してください。
受け取った設計の確認表
| 確認項目 | 確認の方法 |
|---|---|
| 設計方針(主キー・削除・履歴)が守られている | テーブル定義表を方針と1つずつ照らす |
| 頼んでいないテーブルや列が増えていない | エンティティ一覧と件数を比べる |
| DDLが指定した製品で実行できる | 空のDBで実行する |
| 外部キーが実際に作られている | \d テーブル名(PostgreSQL)、SHOW CREATE TABLE(MySQL)で見る |
| 制約が働く | 制約に反する行を入れて、エラーになることを見る |
| 複数行にまたがる条件が表されている | 同じ1冊の二重貸出を試す |
| 桁数に業務の根拠がある | 「要確認」と挙がった列を担当者に確かめる |
よくある質問
Q. 生成AIにDB設計を全部任せてもよいですか? A. 下書きと点検は任せ、方針の決定と最終確認は人が行います。主キーの方針、削除の方式、履歴の持ち方は業務で決まるため、先に人が決めてプロンプトに書きます。
Q. ER図とテーブル定義はどちらを先に作らせますか? A. ER図が先です。エンティティと関連が固まってから列を決めると、列の置き場所の手戻りが少なくなります。
Q. 正規化のレビューで出た指摘は全部直すべきですか? A. いいえ、採否は人が決めます。この記事の book_copies.status のように、導出できる値をあえて持つ設計もあるため、指摘と「確認」を分けて出させ、理由を残して判断します。
Q. PostgreSQL用のDDLをMySQL用に変換できないときは? A. 製品と版を指定して、段階5のプロンプトでDDLを書き直させます。自動採番、日時の型、外部キーの位置、条件つきの一意の4か所が書き分けの対象です。変換後は必ず実行して、外部キーが作られたことを確かめます。
Q. ER図をMermaidで出力させる利点は? A. テキストなので差分を見られ、次の段階のプロンプトにそのまま貼れる点です。この記事のER図は Mermaid 12.1.0 で図として描画できることを確かめています。
まとめ
- DB設計の依頼はエンティティ抽出・ER図・テーブル定義・正規化レビュー・DDLの5段階に分ける。
- 主キー・削除・履歴・命名の方針は人が決め、最初の前提として渡す。
- 各段階で出力形式と「やらないこと」を指定し、分からない点は質問として挙げさせる。
- 正規化レビューは指摘と確認を分けて出させ、採否を人が決める。
- DDLは製品と版を指定し、空のDBで実行して、制約に反する行で確かめる。
- PostgreSQLとMySQLでは自動採番・日時・外部キーの位置・条件つきの一意を書き分ける。
参考・出典
- PostgreSQL 16 Documentation「11.8. Partial Indexes」https://www.postgresql.org/docs/16/indexes-partial.html
本記事の内容は、ご自由にお使いください。
ご利用の際は、出典として本ページへのリンクを記載いただけますようお願いします。
(記載例)出典:株式会社RJC「生成AIでDB設計|テーブル設計とER図を作らせるプロンプト集」
株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。
WE ARE HIRING
RJCで一緒に開発しながら、
成長を楽しみませんか?
RJCは、Web・モバイル・AIを活用した開発プロジェクトで、テックリードやPM・PMOも活躍するシステム開発会社です。会社を知る、待遇を確かめる、話を聞いてみる。気になるところ見てみてください!
- 127日年間休日
- 12時間平均残業時間
- 毎日ガチャ遊びココロも大切にする福利厚生。アマギフなどの賞品ラインナップ!
ほかにも、チケットレストラン、書籍読み放題、2年ごとの慰労報奨(休暇 or 金一封)、11期連続の黒字決算。