PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：差分理解 － Day5 制約

SQL PostgreSQL
スポンサーリンク

Day5 後半のゴール

「“アプリでチェックするから大丈夫”をやめて、DBに仕事をさせる感覚を身につける」

後半では、CHECK・UNIQUE・外部キーを「実際のミニアプリ」を想定しながら使ってみます。
単なる文法ではなく、「こう書いておくと、どんなバグをDBが止めてくれるのか」という視点で見ていきます。

ここでのゴールはこうです。
CHECK制約で“仕様の抜け漏れ”を防ぐ具体的なイメージを持てる。
UNIQUE制約で“レースコンディションによる重複”を防ぐ意味を理解できる。
外部キーの厳密さが、データの「孤児レコード」や「壊れた参照」を防ぐことを、ストーリーとして説明できる。

CHECK制約を“仕様の最後の砦”として使う

例題：会員ランクと割引率を持つテーブル

まずは、会員ランクと割引率を持つテーブルを考えます。

会員ランクは basic, silver, gold の3種類。
割引率は 0〜50％ の間。

これを、PostgreSQLで素直に書くとこうなります。

CREATE TABLE memberships (
  id           SERIAL PRIMARY KEY,
  name         TEXT    NOT NULL,
  rank         TEXT    NOT NULL CHECK (rank IN ('basic', 'silver', 'gold')),
  discount_pct INTEGER NOT NULL CHECK (discount_pct >= 0 AND discount_pct <= 50)
);
SQL

ここで、CHECK制約が2つ効いています。

rank は basic, silver, gold のどれかでなければならない。
discount_pct は 0〜50 の間でなければならない。

アプリ側でバリデーションを書いていたとしても、
もしどこかでバグって、こういうINSERTが飛んできたらどうなるでしょう。

INSERT INTO memberships (name, rank, discount_pct)
VALUES ('テスト', 'platinum', 80);
SQL

アプリがチェックし忘れていても、PostgreSQLはこう返します。
「CHECK制約に違反しているから、この行は受け付けない」。

これが「仕様の最後の砦」としてのCHECK制約です。

CHECK制約が“ない世界”との違いを意識する

もしCHECK制約がなかったら、このおかしなデータは普通に入ってしまいます。
後から「なんで割引率80％の会員がいるんだ？」と頭を抱えることになります。

CHECK制約を入れておくと、「おかしなデータはそもそもDBに入らない」ので、
後からデータを疑う必要が減ります。

ここでの深掘りポイントは、「仕様として決まっているルールは、可能な限りCHECKに落とし込む」という姿勢です。
「アプリでチェックしてるから大丈夫」ではなく、「アプリがミスってもDBが止める」状態を作るのが、堅い設計です。

UNIQUE制約で“同時アクセスの罠”を避ける

例題：メールアドレスの重複登録

次に、UNIQUE制約を「同時アクセス」の観点から見てみます。

ユーザーテーブルをこう定義したとします。

CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);
SQL

ここで、「メールアドレスが既に登録されていないかチェックしてからINSERTする」という、よくあるアプリ側の処理を考えます。

擬似コードで書くと、こうです。

1. SELECT * FROM users WHERE email = 'a@example.com';
2. 結果が0件なら、INSERT INTO users (email) VALUES ('a@example.com');

一見正しそうですが、ここに「同時アクセス」が入るとどうなるか。

ユーザーAとユーザーBが、ほぼ同時に同じメールアドレスで登録しようとしたとします。

ユーザーAが1のSELECTを実行 → まだ0件。
ユーザーBも1のSELECTを実行 → まだ0件。
ユーザーAがINSERTを実行 → 成功。
ユーザーBもINSERTを実行 → ここでどうなるか？

UNIQUE制約がなければ、両方INSERTが通ってしまい、
同じメールアドレスのユーザーが2人できてしまいます。

UNIQUE制約があると、ユーザーBのINSERTはエラーになります。
DBが「このemailは既に存在する」と言ってくれるわけです。

アプリ側のチェックは“親切”、UNIQUEは“絶対”

ここでの大事な考え方はこうです。

アプリ側の「事前チェック」は、ユーザーに分かりやすいエラーメッセージを出すため。
UNIQUE制約は、「どんなに同時アクセスが来ても、最終的に重複を許さないため」。

つまり、UNIQUE制約は「絶対に破られないルール」です。
アプリ側のチェックは「できるだけ早く気づくための親切」です。

PostgreSQLはUNIQUE制約をしっかり守ってくれるので、
「一意性は必ずDBに書く」という習慣を持つと、データの信頼性が一気に上がります。

外部キーの厳密性を“親子関係の物語”で理解する

例題：ユーザーと注文、孤児レコードを防ぐ

最後に、外部キーの厳密性を、ユーザーと注文の関係で見ていきます。

まず、こういうテーブルを作ります。

CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id      SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  amount  INTEGER NOT NULL
);
SQL

ここで、外部キー user_id REFERENCES users(id) が効いています。
この状態だと、次のようなことが起きます。

存在するユーザーに対する注文は、普通に登録できる。

INSERT INTO users (name) VALUES ('Taro');      -- id = 1
INSERT INTO orders (user_id, amount) VALUES (1, 1000); -- OK
SQL

存在しないユーザーに対する注文は、エラーになる。

INSERT INTO orders (user_id, amount) VALUES (9999, 1000); -- エラー
SQL

PostgreSQLは、「そんなユーザーいないから、その注文はおかしい」と言ってくれます。

これが「外部キーの厳密性」です。
SQLiteやMySQLでは、設定次第でこのチェックが甘くなったり、無効になっていたりすることがありますが、
PostgreSQLでは「書いた外部キーはちゃんと守られる」と考えてOKです。

親を消したときにどうするか：ON DELETEの話の入口

もう一歩だけ踏み込みます。
「ユーザーを削除したら、そのユーザーの注文はどうするか？」という問題です。

外部キーを張った状態で、こうするとどうなるでしょう。

DELETE FROM users WHERE id = 1;
SQL

デフォルトでは、PostgreSQLはこれを拒否します。
「orders に user_id=1 の行が残っているから、親を消したら整合性が崩れる」と判断するからです。

このときに使うのが、ON DELETE のオプションです。

例えば、「ユーザーが消えたら、そのユーザーの注文も一緒に消していい」という仕様なら、こう書きます。

CREATE TABLE orders (
  id      SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  amount  INTEGER NOT NULL
);
SQL

こうしておくと、DELETE FROM users WHERE id = 1; を実行したとき、
そのユーザーに紐づく orders の行も自動で消えます。

逆に、「ユーザーが消えることは基本的に許さない」という仕様なら、
ON DELETE を付けずに、デフォルトの「削除を拒否する」挙動のままでOKです。

ここでのポイントは、「外部キーは“存在チェック”だけでなく、“親子のライフサイクル”も設計に含める」ということです。
PostgreSQLはこのあたりの挙動が非常に一貫しているので、安心して外部キー前提の設計ができます。

制約を“テストの一部”として考える

「DB定義そのものが、仕様の自動テストになっている」

CHECK・UNIQUE・外部キーをしっかり書いたテーブル定義は、
それ自体が「仕様の自動テスト」のようなものです。

おかしな値をINSERTしようとすると、DBが即座にエラーを返す。
重複しちゃいけないものが重複しようとすると、DBが止める。
存在しない親を参照しようとすると、DBが拒否する。

これは、アプリのテストコードとは別のレイヤーで動く「常時オンのテスト」です。
しかも、どんなクライアントからアクセスされても、必ず効きます。

PostgreSQLは制約まわりがかなり真面目なので、
「仕様として決まっているルールは、できるだけ制約に落とし込む」
というスタイルがとても相性がいいです。

Day5 後半のまとめ

CHECK制約は、会員ランクや割引率のような「値の範囲・候補が仕様で決まっているもの」を CHECK (rank IN (...)) や CHECK (discount_pct BETWEEN 0 AND 50) のようにDBに書き下ろすことで、「アプリがバグってもおかしな値はそもそもDBに入らない」という“仕様の最後の砦”として機能する。
UNIQUE制約は、メールアドレスや (user_id, product_id) のような複合キーに対して「同時アクセスで事前チェックをすり抜けても、最終的にDBが重複を拒否する」保証を与え、アプリ側の重複チェックを“親切”、UNIQUEを“絶対”として役割分担することで、一意性を強固に守る。
外部キー制約は、user_id REFERENCES users(id) によって「存在しない親への参照」を厳密に禁止し、さらに ON DELETE CASCADE などで「親を消したとき子をどうするか」というライフサイクルまで含めて設計できるため、PostgreSQLでは「制約＝仕様＝常時オンの自動テスト」として積極的に活用するのが、Day5 後半で目指す考え方になる。

前へ
12
スポンサーリンク
タイトルとURLをコピーしました