DDLでつまずくのは「文法」ではなく「方言差」

CREATE TABLE の構文そのものは単純です。それでも書くたびに調べ直すことになるのは、同じ意味のカラムをMySQL・PostgreSQL・SQLiteで書き分ける必要があるからです。「MySQLで動いていたDDLをPostgreSQLに持っていったら AUTO_INCREMENT で構文エラー」「ローカルのSQLiteでは通っていたCHECK制約が本番で効いていなかった」——DDLのトラブルはほぼこの方言差に集約されます。

この記事は、3つのデータベースを横断した型・制約の早見表を中心にしたリファレンスです。設計そのものの話（正規化やインデックス戦略）ではなく、「この設計をどう書き下すか」に絞っています。

CREATE TABLE の骨格

まず、3つのデータベースで共通して通る最小構成を確認します。

CREATE TABLE users (
    id          INTEGER PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    name        VARCHAR(100),
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

カラム定義は カラム名 → 型 → 制約 の順で並べます。制約にはカラム制約（上のように型の後ろに書く）とテーブル制約（カラム定義をすべて書いた後にまとめて書く）の2種類があり、複合主キーや複合ユニークはテーブル制約でしか書けません。

CREATE TABLE order_items (
    order_id   INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity   INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id),                      -- 複合主キー
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
);

型の早見表

用途ごとに、3データベースで実際に指定すべき型を並べます。

用途MySQLPostgreSQLSQLite
整数（一般）INTINTEGERINTEGER
整数（大）BIGINTBIGINTINTEGER
金額・厳密な小数DECIMAL(10,2)NUMERIC(10,2)NUMERIC
短い文字列VARCHAR(255)VARCHAR(255) または TEXTTEXT
長文TEXTTEXTTEXT
真偽値BOOLEAN（実体は TINYINT(1)）BOOLEANINTEGER（0/1）
日付のみDATEDATETEXT
日時DATETIMETIMESTAMPTZTEXT
JSONJSONJSONBTEXT
UUIDCHAR(36) または BINARY(16)UUIDTEXT

型選択で押さえておきたい点は3つあります。

SQLiteには型がほぼ無い。SQLiteは値ごとに型を持つ動的型付けで、カラムの型宣言は「型親和性（type affinity）」というヒントにしかなりません。BOOLEAN や DATETIME と書いてもエラーにはなりませんが、内部的には数値かテキストとして保存されます。SQLite向けのDDLで方言変換が必要になるのは主にこの点です。

PostgreSQLの VARCHAR(n) に性能上の利点はない。PostgreSQLでは TEXT と VARCHAR の実装が同じで、長さ制限は単なるCHECK制約に近い扱いです。MySQLと違って「短いから速い」ということはないため、明確な業務上の上限がなければ TEXT で構いません。

金額に FLOAT / DOUBLE を使わない。浮動小数点数は10進の小数を正確に表現できないため、合計金額がずれます。DECIMAL / NUMERIC を使ってください。

自動採番は3方言で完全に別物

DDLの方言差が最も大きいのがサロゲートキーの自動採番です。

データベース書き方
MySQLid INT NOT NULL AUTO_INCREMENT PRIMARY KEY
PostgreSQL（推奨）id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
PostgreSQL（旧来）id SERIAL PRIMARY KEY
SQLiteid INTEGER PRIMARY KEY

PostgreSQLは SERIAL より IDENTITY が推奨です。SERIAL は「INTEGER 型＋裏でシーケンスを作成」という擬似型で、PostgreSQL 10以降はSQL標準の GENERATED ... AS IDENTITY が使えます。IDENTITYはシーケンスの所有関係がテーブルに正しく紐づくため、テーブル削除時の後始末やパーミッション周りで扱いやすくなっています。

SQLiteの AUTOINCREMENT は基本的に不要です。INTEGER PRIMARY KEY と書いた時点でそのカラムは内部のrowidの別名になり、省略時は自動採番されます。AUTOINCREMENT キーワードを付けると「削除済みのIDを二度と再利用しない」保証が加わりますが、sqlite_sequence テーブルへの書き込みが増えるぶん遅くなります。公式ドキュメントでも、本当に必要な場合を除いて避けるよう案内されています。なお AUTOINCREMENT は INTEGER PRIMARY KEY 以外に付けると構文エラーです。

制約の早見表と挙動の違い

制約意味方言上の注意
PRIMARY KEY一意かつNULL不可暗黙にNOT NULL。1テーブルに1つ
NOT NULLNULL禁止差はほぼ無い
UNIQUE値の重複禁止NULLは重複扱いされない（複数行がNULLを持てる）
DEFAULT省略時の値MySQLの TEXT/JSON はデフォルト式に括弧が必要（8.0.13以降）
CHECK条件を満たす値のみ許可MySQLは8.0.16より前だと構文が通るだけで無視される
FOREIGN KEY参照整合性SQLiteは既定で無効。InnoDB以外のMySQLエンジンでは無視

とくに事故になりやすいのが下の2つです。

MySQLのCHECK制約: MySQL 8.0.16より前のバージョンは CHECK 句を構文としては受け付けるものの、制約として一切適用しませんでした。「DDLに書いてあるから効いているはず」と思い込んだまま不正な値が入り続ける、という発見の遅れやすい不具合になります。古い環境に載せる可能性があるなら、アプリケーション側のバリデーションを正としてください。

SQLiteの外部キー: SQLiteは後方互換性のため、外部キー制約の適用が既定で無効です。しかも設定は接続ごとなので、接続を張るたびに次を実行する必要があります。

PRAGMA foreign_keys = ON;

これを忘れると、REFERENCES を書いてあるのに存在しない親IDを持つ行が普通に入ります。ローカルSQLite・本番PostgreSQLという構成だと、ローカルでだけ整合性が壊れるという厄介な出方をします。

外部キーの ON DELETE / ON UPDATE

参照先の行が削除・更新されたときの挙動は4種類です。

オプション挙動
RESTRICT / NO ACTION子行が残っていれば親の削除・更新を拒否（既定）
CASCADE親に合わせて子行も削除・更新する
SET NULL子側の外部キーカラムをNULLにする（カラムがNULL許可である必要がある）
SET DEFAULT子側をデフォルト値にする（MySQL/InnoDBは非対応）
-- ユーザー削除時に、その投稿も消す／コメントは残して投稿者不明にする
CREATE TABLE posts (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE comments (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users (id) ON DELETE SET NULL  -- NOT NULLにしてはいけない
);

CASCADE は便利ですが、消える範囲がDDLを読まないと分からないという副作用があります。監査ログや売上明細のように「親が消えても残したい」テーブルには使わないでください。

日時カラムの選び方

やりたいことMySQLPostgreSQL
作成日時を自動で入れるTIMESTAMP DEFAULT CURRENT_TIMESTAMPTIMESTAMPTZ DEFAULT NOW()
更新日時を自動で更新するTIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPトリガーが必要（構文レベルの機能は無い）

ON UPDATE CURRENT_TIMESTAMP はMySQL独自の機能です。PostgreSQLに移すときは BEFORE UPDATE トリガーで置き換えることになるため、DDLをそのままコピーすると updated_at が更新されないまま気づかない、という移行事故が起きます。

MySQLで日時型を選ぶときは、TIMESTAMP が1970〜2038年の範囲しか保持できずセッションのタイムゾーンで変換される点、DATETIME は範囲が広くタイムゾーン変換されない点を踏まえて選びます。PostgreSQLでは、タイムゾーンを持たない TIMESTAMP より TIMESTAMPTZ が基本的に無難です。

文字コードは utf8 ではなく utf8mb4

MySQLの utf8 は歴史的経緯から1文字あたり最大3バイトしか扱えない別物で、絵文字や一部の漢字を入れると Incorrect string value エラーになります。明示するなら utf8mb4 を指定してください。

CREATE TABLE posts (
    id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    body  TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

PostgreSQLとSQLiteはデータベース／ファイル単位でUTF-8を扱うため、テーブル定義側で意識する必要はありません。

設計をそのままDDLとER図にする

型と制約を決めたあと、3方言ぶんを手で書き分けるのは単純作業です。DDLビルダー はテーブルとカラムを画面上で組み立てるだけで、MySQL / PostgreSQL / SQLite それぞれの書式の CREATE TABLE 文と、Mermaid形式のER図を同時に生成します。APIレスポンスのJSONサンプルを貼り付けてカラム構成を自動推測させることもできるので、既存データからの逆算にも使えます。方言差の吸収はツール側が持っているため、この記事の早見表を毎回引き直す必要がなくなります。

すでにDDLがある場合は、SQL to ER図 変換 に貼り付けるとテーブル間の参照関係を図として確認できます。テーブルが揃ったら Visual SQL Builder でSELECT文を組み立てられます。JOINの種類ごとの結果の違いは SQL JOIN完全リファレンス、ER図記法そのものは Mermaid ER図記法リファレンス にまとめています。いずれのツールもブラウザ内で完結し、入力したスキーマ情報が外部に送信されることはありません。

まとめ

  • 型は「SQLiteは型親和性でしかない」「PostgreSQLの VARCHAR(n) に速度上の利点はない」「金額は DECIMAL」の3点を押さえる
  • 自動採番は AUTO_INCREMENT / GENERATED ALWAYS AS IDENTITY / INTEGER PRIMARY KEY と完全に別物。PostgreSQLの新規設計では SERIAL より IDENTITY
  • MySQL 8.0.16より前の CHECK は無視される。SQLiteの外部キーは接続ごとに PRAGMA foreign_keys = ON が要る
  • ON DELETE SET NULL を使うカラムを NOT NULL にはできない
  • ON UPDATE CURRENT_TIMESTAMP はMySQL専用。PostgreSQLではトリガーで代替する
  • MySQLの文字コードは utf8 ではなく utf8mb4

よくある質問

VARCHARとTEXTはどちらを使うべきですか？

データベースによります。PostgreSQLでは両者の実装がほぼ同じなので、業務上の明確な文字数上限がなければ TEXT で問題ありません。MySQLでは VARCHAR が行内に格納されるのに対し TEXT は別領域に格納されうるため、頻繁に検索・ソートする短い文字列は VARCHAR(n) が有利です。SQLiteはどちらを書いても内部の扱いは同じです。

SQLiteの AUTOINCREMENT は付けたほうがいいですか？

通常は不要です。INTEGER PRIMARY KEY と書けば省略時に自動採番されます。AUTOINCREMENT を付けると「削除済みIDを再利用しない」保証が加わる代わりに、管理用テーブルへの書き込みが増えて遅くなります。外部に公開するIDで採番済み値の再利用を絶対に避けたい、といった要件がある場合にだけ検討してください。

CREATE TABLE に書いたCHECK制約が効きません

MySQLを使っている場合、バージョンが8.0.16より前だと CHECK 句は構文として受け付けられるだけで適用されません。SELECT VERSION(); でバージョンを確認してください。SQLiteでCHECKは有効ですが、外部キー制約のほうは既定で無効なので、PRAGMA foreign_keys = ON; を接続ごとに実行する必要があります。

作ったDDLを貼り付けて確認したいのですが、データは送信されますか？

いいえ。DDLビルダー と SQL to ER図 変換 はどちらもブラウザ内で完結し、入力したテーブル定義やスキーマ情報がサーバーに送信されることはありません。