---
note: 金額・日時・列挙の型選定と、不整合をアプリでなくDBの制約で弾く設計
created: 2026-08-14T10:00:00+09:00
---

# 第5回｜データ型と制約設計

> データ型と制約は、アプリのコードを1行も書かずに不正なデータを弾くための、DB側の防波堤である。

## この回のねらい

前回までで正規化された表の形は手に入った。この回では、その表の各列にどの型を割り当て、
どんな制約を張れば「間違ったデータがそもそも入らない」テーブルになるかを扱う。
アプリのバリデーションで頑張るのをやめて、DBの型と制約に不整合の検出を肩代わりさせる発想に切り替える回である。

## 到達目標

- integer/bigint/numericの違いを説明し、なぜ金額にnumericを使うべきかをfloatとの比較で説明できる
- text/varchar(n)の違いを説明し、文字数上限をスキーマの型で強制すべきか判断できる
- timestamptz/date/intervalを使い分けられ、なぜtimestamp（タイムゾーンなし）を避けるべきか説明できる
- 列挙的な値の表現方法として、ENUM型・CHECK制約・参照テーブルのトレードオフを比較し選べる
- NOT NULL/UNIQUE/CHECK/主キー/外部キー/生成列を組み合わせて、不正な状態を挿入できないテーブルを設計できる
- 外部キーのON DELETE RESTRICT/CASCADE/SET NULLの挙動の違いを、実験を通じて説明できる

## 前提と準備

第1回で構築したPostShopスキーマ（customers/categories/products/product_prices/orders/order_items/events）
にデータが投入済みであることを前提とする。本回で新しく必要な拡張機能はない。PostgreSQL 16系を前提に書く。

psqlで接続し（`psql -d postshop`）、まず`order_items`に現在どんな制約が付いているかを確認しておく。

```sql
\d order_items
```

```text
                Table "public.order_items"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 order_id   | bigint        |           | not null |
 product_id | bigint        |           | not null |
 qty        | integer       |           | not null |
 unit_price | numeric(10,2) |           | not null |
Indexes:
    "order_items_pkey" PRIMARY KEY, btree (order_id, product_id)
Check constraints:
    "order_items_qty_check" CHECK (qty > 0)
    "order_items_unit_price_check" CHECK (unit_price >= 0)
Foreign-key constraints:
    "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id)
    "order_items_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(id)
```

すでにCHECKとFOREIGN KEYが付いている。この回では「なぜこう作られているか」を理解し、
まだ制約が足りていない`orders.status`に自分で制約を追加する。

## この回で新しく出てくる用語

型と制約の選択肢を比べる回なので、比較対象の名前を先に揃えておく。
回をまたいで使う語は[用語集](glossary.md)にもまとめている。

| 用語 | 一行での説明 |
|---|---|
| DDL（Data Definition Language） | テーブルや制約など、データの入れ物の定義を変更するSQLの総称（`CREATE`/`ALTER`/`DROP`） |
| ORM（Object-Relational Mapping） | オブジェクトとテーブルの行を対応づけ、SQLを書かずにDBを操作させるライブラリの総称 |
| `interval` | 「3日」「1年」のような期間・長さを表す型 |
| ENUM型 | 取りうる値の集合そのものを型として定義するPostgreSQL固有の型 |
| `jsonb` | JSONを二進表現で格納し、演算子と索引で検索できる型 |
| GIN索引 | 1つの値の中に複数の要素を含むもの（配列・`jsonb`・全文）を検索するための索引 |
| 参照アクション | 参照先の行が消えた・変わったときに、参照する側をどうするかの指定 |
| 生成列 | 他の列から自動的に計算され、直接書き込めない列 |
| ドメイン型 | 既存の型にCHECK制約をまとめて名前を付け、再利用できるようにしたもの |

## 本編

### 数値型 — なぜ金額に float を使わないか

PostgreSQLの数値型は大きく2系統に分かれる。整数系（smallint/integer/bigint）と、
浮動小数点系（real/double precision、通称float4/float8）、そして任意精度の10進数型（numeric）である。

| 型 | サイズ | 範囲・特徴 |
|---|---|---|
| smallint | 2byte | -32,768 〜 32,767 |
| integer | 4byte | -2,147,483,648 〜 2,147,483,647 |
| bigint | 8byte | -9,223,372,036,854,775,808 〜 9,223,372,036,854,775,807 |
| real / double precision | 4byte / 8byte | IEEE754の二進浮動小数点。近似値。高速だが誤差を持つ |
| numeric(p, s) | 可変長 | 10進数で正確に格納。pは全体の桁数、sは小数点以下の桁数 |

float4/float8は2進数で数値を表現する。ところが0.1や0.2のような10進小数の多くは2進数では
有限桁で表現できず循環小数になる。そのため格納した時点ですでに近似値であり、演算を重ねるほど
誤差が蓄積する（環境やライブラリを問わずIEEE754二進浮動小数点である限り起きる現象である）。
実際に確かめる。

```sql
SELECT 0.1::float8 + 0.2::float8;
```

```text
      ?column?
---------------------
 0.30000000000000004
(1 row)
```

0.3にならない。numericで同じ式を計算すると誤差は出ない。

```sql
SELECT 0.1::numeric + 0.2::numeric;
```

```text
 ?column?
----------
      0.3
(1 row)
```

numericは10進数の桁をそのまま保持するため、この種の誤差が起きない（誤差が蓄積していく様子は
ハンズオン課題1でさらに確かめる）。これが共通スキーマで`products.price`・`product_prices.price`・`order_items.unit_price`
がすべて`numeric(10,2)`である理由である。`numeric(10,2)`は全体で10桁、小数点以下2桁を意味し、
最大`99999999.99`まで格納できる。金額・数量に関わる列は、丸め誤差を許容できる測定値でない限り
floatを使わない。PostgreSQLには`money`型も存在するが、`lc_monetary`ロケール設定に表示・入力が
依存し扱いが不安定なため、明示的な`numeric(p, s)`を使う方が安全である。

整数はidや個数のように誤差のない値に使う。`bigint`は`GENERATED BY DEFAULT AS IDENTITY`の主キーで
将来的に21億行を超える可能性がある表（`orders`や`events`）に使う。`order_items.qty`のように
現実的に上限が低い値は`integer`で十分である。

### 文字列型 — text と varchar(n)

PostgreSQLの`text`型は長さ無制限の可変長文字列である。`varchar(n)`は`text`とストレージ上ほぼ同じだが、
挿入・更新時にn文字を超えるとエラーになる長さチェックが追加される。`char(n)`は固定長で末尾を
空白で埋める型で、通常のアプリケーション開発では使う理由がほとんどない。

```sql
CREATE TEMP TABLE varchar_demo (name varchar(5));
INSERT INTO varchar_demo VALUES ('123456');
```

```text
ERROR:  value too long for type character varying(5)
```

このエラー自体は意図通りだが、問題は「その5文字という上限がどこから来たか」である。
共通スキーマでは`customers.email`・`products.name`・`categories.name`をすべて`text`にしている。
文字数の上限が本当にDBの型で強制すべき技術的制約（外部システムの列幅に合わせる、など）でない限り、
`text`にしておいて必要ならCHECK制約で緩やかに検証する方が変更に強い。

```sql
ALTER TABLE products
  ADD CONSTRAINT products_name_length_check CHECK (length(name) <= 200);
```

こうしておけば、上限を変えたくなったときにDDLで列の型を変えるのではなく、CHECK制約を
`DROP CONSTRAINT` / `ADD CONSTRAINT`し直すだけで済む。`varchar(n)`のnに「商品名は50文字のはず」
のような業務上の思い込みを固定してしまうと、後で想定外の長さのデータに遭遇するたびに
スキーマ変更が必要になる。

### 日時型 — timestamptz・date・interval

`timestamptz`は内部的にUTCの瞬間（絶対時刻）として格納され、表示・入力時にセッションの
`TimeZone`設定に従って変換される。`timestamp`（タイムゾーンなし）は「壁時計の値」をそのまま
保持するだけで、それがどのタイムゾーンの時刻なのかという情報を持たない。同じ文字列
`'2026-08-10 09:00:00'`が、東京の9時なのかUTCの9時なのか、書いた人と読む人の間で
暗黙の合意に頼ることになる。実際に確かめる。

```sql
SET TIME ZONE 'Asia/Tokyo';
SELECT '2026-08-10 09:00:00+09'::timestamptz;
-- 2026-08-10 09:00:00+09  (1 row)

SET TIME ZONE 'UTC';
SELECT '2026-08-10 09:00:00+09'::timestamptz;
-- 2026-08-10 00:00:00+00  (1 row)
```

同じ瞬間を指しているが、セッションのタイムゾーンに応じて表示だけが変わる。これが
`timestamptz`が「絶対時刻」を扱える理由である。共通スキーマの`created_at`・`ordered_at`・
`occurred_at`・`valid_from`・`valid_to`がすべて`timestamptz`なのはこのためで、`timestamp`は
避けるべき対比としてのみ登場させる。

`date`は時刻を持たない暦日（生年月日、締め日など）に使う。`interval`は期間・長さを表す型で、
日時の加減算で自然に出てくる。

```sql
SELECT ordered_at, ordered_at + interval '3 days' AS estimated_delivery
FROM orders
ORDER BY id
LIMIT 1;
```

```text
       ordered_at       |   estimated_delivery
-------------------------+-------------------------
 2026-01-05 10:12:03+09 | 2026-01-08 10:12:03+09
(1 row)
```

### 真偽値と列挙 — boolean、そして ENUM vs CHECK vs 参照テーブル

`boolean`は`true`/`false`の2値ではなく、NULLを許すなら`unknown`を含めた3値論理になる。
「フラグ列のつもりがNULLも入ってしまい、`WHERE flag = true`が意図せず行を落とす」事故を
避けるため、真に2値であるべき列は`NOT NULL DEFAULT false`のように定義する。

もう少し値の種類が多い「列挙」を表現する方法は3つある。PostgreSQL固有のENUM型、CHECK制約、
そして値を別テーブルに切り出す参照テーブル方式である。共通スキーマの`orders.status`
（`pending`/`paid`/`shipped`/`delivered`/`cancelled`/`refunded`）を例に比較する。

```sql
-- 案1: ENUM型（このスキーマには採用しない。比較のための例）
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'delivered', 'cancelled', 'refunded');

-- 案2: CHECK制約
ALTER TABLE orders ADD CONSTRAINT orders_status_check
  CHECK (status IN ('pending','paid','shipped','delivered','cancelled','refunded'));

-- 案3: 参照テーブル+外部キー(設計イメージ。実際にこの環境のordersへは適用しない)
CREATE TABLE order_statuses (
  code text PRIMARY KEY, display_name text NOT NULL, sort_order int NOT NULL
);
INSERT INTO order_statuses (code, display_name, sort_order) VALUES
  ('pending', '受付済み', 1), ('paid', '支払い済み', 2), ('shipped', '発送済み', 3);
  -- 以下 delivered/cancelled/refunded も同様に投入する
-- orders.status を FOREIGN KEY REFERENCES order_statuses(code) にする
```

| 観点 | ENUM型 | CHECK制約 | 参照テーブル＋FK |
|---|---|---|---|
| 値の追加容易性 | `ALTER TYPE ... ADD VALUE`が必要（DDLでロールバックしづらい場面がある） | `DROP CONSTRAINT`＋`ADD CONSTRAINT`（DDL、短時間だがテーブルロック） | `INSERT`一発。DDL不要でアプリからも追加できる |
| 参照整合性 | 型システムで未知の値を拒否するが、値集合そのものは1箇所（型定義）にしかない | SQL文字列としてハードコードされる。複数テーブルで使うと重複しやすい | FKで保証。他テーブルからも同じ値集合をJOINで参照できる |
| 移植性 | PostgreSQL固有機能。他RDBMSへの移植で書き換えが要る | 標準SQL。移植性が高い | 標準SQL。移植性が高い |
| メタデータ付与 | 値そのものしか持てない（表示名・並び順などは持てない） | 持てない | 列を増やせば表示名・並び順・廃止フラグなどを自然に持てる |

この講座では`orders.status`にENUM型を使わない立場を取る。値の追加・廃止が発生しやすい業務列を
PostgreSQL固有の型に縛ると、将来の変更コストと移植性の両方を犠牲にする。小規模で値集合が
ほぼ固定なら**CHECK制約**、値にメタデータを持たせたい・他テーブルからも参照したいなら
**参照テーブル**を選ぶ。この回のハンズオンでは、まずシンプルなCHECK制約を追加する。

### jsonb の使いどころと乱用

`jsonb`は二進表現で格納されるJSON型で、専用の演算子とGINインデックスによる検索が可能である。
演算子は `->`（キーを引いて`jsonb`で返す）、`->>`（キーを引いて文字列で返す）、`@>`（右のJSONを
含むか）、`?`（そのキーを持つか）の4つを押さえておけばよい。GINインデックスは「1つの値の中に
複数の要素が入っているもの」を検索するための索引で、詳しくは[第7回](07-storage-indexes-collation.md)で扱う。使いどころは、行ごとに持つ属性の種類が本当にバラバラで、事前に列を
固定できない場合である。たとえば商品のカテゴリ別属性（書籍ならISBN・ページ数、家電なら
消費電力・色）を、カテゴリの数だけNULL列を並べる代わりにjsonbへ逃がす。

```sql
ALTER TABLE products ADD COLUMN attributes jsonb NOT NULL DEFAULT '{}'::jsonb;

UPDATE products SET attributes = '{"isbn": "978-4-000000-00-0", "pages": 320}'::jsonb WHERE id = 1;
UPDATE products SET attributes = '{"wattage": 1200, "color": "white"}'::jsonb WHERE id = 2;

CREATE INDEX idx_products_attributes ON products USING gin (attributes);

SELECT id, name, attributes -> 'wattage' AS wattage FROM products WHERE attributes ? 'wattage';
-- id |   name    | wattage
-- ---+-----------+---------
--  2 | 商品サンプル2 | 1200
-- (1 row)
```

乱用は、本来リレーショナルにモデリングすべき情報までjsonbに逃がすことで起きる。たとえば
`order_items`を正規化せず、`orders`に`items jsonb`列を1本足して
`[{"product_id": 1, "qty": 2, "unit_price": 1000}, ...]`のような配列で持たせるアンチパターンを
考える（本教材のスキーマには存在しない）。この形では、JSON内の`product_id`が実在の商品を
指している保証（外部キー相当）がなく、
`qty > 0`のようなCHECKも効かず、`SUM(qty * unit_price)`のような集計は`jsonb_to_recordset`で
一度展開しないと書けない。第4回で`order_items`を正規化した理由がそのまま、jsonbを乱用しては
いけない理由になる。頻繁に検索・集計・JOINする属性は列やテーブルに戻し、jsonbは本当に
可変で構造を事前に決められない属性だけに限定する。

### 制約 — NOT NULL・UNIQUE・CHECK・主キー・外部キー

制約は「このテーブルにはこの形の行しか存在してはならない」という宣言であり、
アプリのコードを経由しなくてもPostgreSQL自身が守る。

- **NOT NULL**: 値の欠落を禁止する。共通スキーマでは`customers.email`や`orders.status`など
  ほぼ全列がNOT NULLである。NULLを許すのは「未確定」に本当に意味がある列（`product_prices.valid_to`
  はNULLで「現在も有効」を表す）だけにする。
- **UNIQUE**: 一意性を強制する。`customers.email UNIQUE`のように単一列でも、複数列の組み合わせでも
  張れる。NULLはUNIQUE制約上「互いに異なる」扱いになるため、複数のNULLは共存できる
  （PostgreSQL 15以降は`UNIQUE NULLS NOT DISTINCT`でNULLも重複禁止にできる）。
- **CHECK**: 行単位の任意の真偽式を強制する。`order_items.qty > 0`のような単一列条件のほか、
  複数列にまたがる条件も書ける。たとえば第6回で扱う有効期間モデルでは
  `CHECK (valid_to IS NULL OR valid_to > valid_from)`のような表現が出てくる。
- **主キー（PRIMARY KEY）**: NOT NULL＋UNIQUEの組み合わせで、テーブルにつき1つだけ定義できる。
  `order_items`のように複数列の複合主キー（`order_id, product_id`）も張れる。
- **外部キー（FOREIGN KEY）**: 参照先に存在する値しか持てないことを強制する。
  `order_items.product_id REFERENCES products(id)`は、存在しない商品IDを明細に持たせない。

外部キーには「参照先の行が消えたとき、参照している側をどうするか」を決める**参照アクション**
（`ON DELETE` / `ON UPDATE`）がある。

| 参照アクション | 親削除時の挙動 |
|---|---|
| `NO ACTION`（デフォルト） | 子行が存在すれば削除を拒否する。文の実行完了まで、または`DEFERRABLE`なら遅延してチェックする |
| `RESTRICT` | 子行が存在すれば削除を拒否する。`NO ACTION`と異なり即座にチェックされ、遅延不可 |
| `CASCADE` | 親の削除に連動して子行も削除する |
| `SET NULL` | 子行の外部キー列をNULLにする（列がNOT NULLなら失敗する） |
| `SET DEFAULT` | 子行の外部キー列をデフォルト値にする |

`ON UPDATE`も同じ選択肢を持ち、参照先の主キー値そのものが更新されたときの挙動を決める。
このスキーマは主キーをすべて`GENERATED ... AS IDENTITY`のサロゲートキーにしているため、
主キー値が更新される場面はほぼないが、自然キー（コードや型番など）を主キーにする設計では
`ON UPDATE CASCADE`が意味を持つ。

RESTRICT/CASCADE/SET NULLで実際に挙動がどう変わるかは、次のハンズオンで手を動かして確認する。

### 生成列とドメイン型

**生成列**は、他の列から自動的に計算される列で、直接書き込むことができない。
値を常に他の列と整合させたいとき（明細行の小計など）に使う。PostgreSQL 16では
`GENERATED ALWAYS AS (...) STORED`のみサポートされ、計算結果はディスクに保存される。

```sql
ALTER TABLE order_items
  ADD COLUMN line_total numeric(12,2) GENERATED ALWAYS AS (qty * unit_price) STORED;

SELECT order_id, product_id, qty, unit_price, line_total FROM order_items
ORDER BY order_id, product_id LIMIT 2;
-- order_id | product_id | qty | unit_price | line_total
-- ---------+------------+-----+------------+-----------
--        1 |          3 |   2 |    1500.00 |    3000.00
--        1 |          7 |   1 |     980.00 |     980.00

UPDATE order_items SET line_total = 999 WHERE order_id = 1 AND product_id = 3;
-- ERROR:  column "line_total" can only be updated to DEFAULT
-- DETAIL:  Column "line_total" is a generated column.
```

`line_total`は直接更新できない。`qty`や`unit_price`が更新されれば自動的に再計算されるため、
「明細金額のキャッシュがずれる」というバグの入る余地がなくなる。

**ドメイン型**は、既存の型にCHECK制約を1つにまとめて名前を付け、再利用できるようにしたものである。

```sql
CREATE DOMAIN jpy_amount AS numeric(12,2) CHECK (VALUE >= 0);

CREATE TEMP TABLE domain_demo (id int, amount jpy_amount);
INSERT INTO domain_demo VALUES (1, -100);
-- ERROR:  value for domain jpy_amount violates check constraint "jpy_amount_check"
```

金額に関するルール（0以上、桁数）を複数テーブルで使い回したいなら、列ごとにCHECKを
書く代わりにドメイン型1つに集約できる。ただしドメイン型はPostgreSQL固有の機能で、
一部のORM・マイグレーションツールとの相性が悪いこと、制約を後から変更すると
既存の全データを再検証する必要があることから、この講座の共通スキーマでは採用していない。
選択肢として知っておき、チーム・ツールの事情に応じて使う。

### 制約はアプリではなくDBで守る

Webアプリのバリデーションだけでデータの整合性を守ろうとすると、そのバリデーションを
通らない書き込み経路——管理画面からの手直し、バッチ処理、別サービスからの直接INSERT、
移行スクリプト、将来チェックを書き忘れる開発者——がすべて素通りしてしまう。
NOT NULL/UNIQUE/CHECK/主キー/外部キーはどの経路から書き込んでもPostgreSQL自身が検証するため、
抜け道がない。アプリ側のバリデーションは「早い段階で分かりやすいエラーメッセージを返す」
というUX上の役割に限定し、データの正しさそのものはDBの制約に保証させる。この考え方は
第9回のトランザクションでも土台になる。

## ハンズオン

### 課題1: floatの丸め誤差を再現してnumericに直す

やること: `float8`で金額計算をしたときに起きる誤差を確認し、`numeric`なら起きないことを確認する。

**想定解答**

```sql
SELECT 0.1::float8 + 0.2::float8   AS float_result;    -- 0.30000000000000004 (1 row)
SELECT 0.1::numeric + 0.2::numeric AS numeric_result;  -- 0.3                 (1 row)
```

さらに、明細10件ぶんの単価0.1円を合計するイメージで再現する。

```sql
CREATE TEMP TABLE float_demo (amount float8);
INSERT INTO float_demo SELECT 0.1 FROM generate_series(1, 10);
SELECT sum(amount) FROM float_demo;                     -- 0.9999999999999999 (1 row)

CREATE TEMP TABLE numeric_demo (amount numeric(10,2));
INSERT INTO numeric_demo SELECT 0.1 FROM generate_series(1, 10);
SELECT sum(amount) FROM numeric_demo;                   -- 1.00 (1 row)
```

なぜこれで解けるか: float8は2進数で10進小数を近似するため、0.1のような値は格納した瞬間から
すでに誤差を含んでいる。加算のたびに誤差が持ち越され蓄積する。numericは10進の桁をそのまま
保持するため、宣言した精度・スケールの範囲内では誤差が生じない。共通スキーマの金額列が
すべて`numeric(10,2)`なのはこの実験結果そのものが根拠になる。

### 課題2: CHECK制約で不正な注文データを弾く

やること: `order_items`にすでにあるCHECK制約が不正な行を拒否することを確認したうえで、
`orders.status`にCHECK制約を追加し、列挙外の値のINSERTが弾かれることを確認する。

**想定解答**

まず既存の制約を確認する（id=1の商品・注文は第1回の投入データに存在する）。

```sql
INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES (1, 1, -1, 1000.00);
```

```text
ERROR:  new row for relation "order_items" violates check constraint "order_items_qty_check"
DETAIL:  Failing row contains (1, 1, -1, 1000.00, null).
```

```sql
INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES (1, 1, 1, -500.00);
```

```text
ERROR:  new row for relation "order_items" violates check constraint "order_items_unit_price_check"
DETAIL:  Failing row contains (1, 1, 1, -500.00, null).
```

次に`orders.status`にCHECK制約を追加する。

```sql
ALTER TABLE orders
  ADD CONSTRAINT orders_status_check
  CHECK (status IN ('pending','paid','shipped','delivered','cancelled','refunded'));
```

不正な値でのINSERTは拒否され、正しい値なら通る。

```sql
INSERT INTO orders (customer_id, status, ordered_at) VALUES (1, 'in_transit', now());
-- ERROR:  new row for relation "orders" violates check constraint "orders_status_check"
-- DETAIL:  Failing row contains (..., 1, in_transit, ...).

INSERT INTO orders (customer_id, status, ordered_at) VALUES (1, 'pending', now());
-- INSERT 0 1
```

なぜこれで解けるか: CHECK制約は行が実際にテーブルへ書き込まれる前に評価される。
アプリ側でどれだけ値を検証していても、この制約を追加した瞬間から、
どの経路からの書き込みでも列挙外の値は物理的に入らなくなる。

### 課題3: 外部キーのON DELETE RESTRICT/CASCADE/SET NULLの違いを試す

やること: 参照アクションの異なる3組の親子テーブルを作り、子行のある親を削除したときの
挙動の違いを実際に観察する。実験用テーブルなので最後にDROPする。

**想定解答**

3つの参照アクションぶんの親子テーブルをまとめて用意する。

```sql
CREATE TABLE demo_categories_restrict (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name text NOT NULL);
CREATE TABLE demo_products_restrict (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  category_id bigint REFERENCES demo_categories_restrict(id) ON DELETE RESTRICT, name text NOT NULL);

CREATE TABLE demo_categories_cascade (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name text NOT NULL);
CREATE TABLE demo_products_cascade (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  category_id bigint REFERENCES demo_categories_cascade(id) ON DELETE CASCADE, name text NOT NULL);

CREATE TABLE demo_categories_setnull (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name text NOT NULL);
CREATE TABLE demo_products_setnull (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  category_id bigint REFERENCES demo_categories_setnull(id) ON DELETE SET NULL, name text NOT NULL);

INSERT INTO demo_categories_restrict (name) VALUES ('文具');
INSERT INTO demo_products_restrict (category_id, name) VALUES (1, 'ノート'), (1, '鉛筆');
INSERT INTO demo_categories_cascade (name) VALUES ('文具');
INSERT INTO demo_products_cascade (category_id, name) VALUES (1, 'ノート'), (1, '鉛筆');
INSERT INTO demo_categories_setnull (name) VALUES ('文具');
INSERT INTO demo_products_setnull (category_id, name) VALUES (1, 'ノート'), (1, '鉛筆');
```

同じ操作（子行のある親を削除）を3パターンで試す。

```sql
DELETE FROM demo_categories_restrict WHERE id = 1;
-- ERROR:  update or delete on table "demo_categories_restrict" violates foreign key
--         constraint "demo_products_restrict_category_id_fkey" on table "demo_products_restrict"
-- DETAIL:  Key (id)=(1) is still referenced from table "demo_products_restrict".
```

```sql
DELETE FROM demo_categories_cascade WHERE id = 1;
SELECT * FROM demo_products_cascade;
```

```text
DELETE 1
 id | category_id | name
----+-------------+------
(0 rows)
```

親を消しただけなのに、子の`demo_products_cascade`も連動して消えている。

```sql
DELETE FROM demo_categories_setnull WHERE id = 1;
SELECT * FROM demo_products_setnull;
```

```text
DELETE 1
 id | category_id | name
----+-------------+------
  1 |        null | ノート
  2 |        null | 鉛筆
(2 rows)
```

子行は残るが、参照先を失った`category_id`がNULLになっている。最後に片付ける。

```sql
DROP TABLE demo_products_restrict, demo_categories_restrict,
           demo_products_cascade, demo_categories_cascade,
           demo_products_setnull, demo_categories_setnull;
```

なぜこれで解けるか: 3つとも「親にひもづく子行がある状態で親を消す」という同じ操作をしているが、
外部キーに指定した参照アクションだけが違う。RESTRICTは安全側に倒して削除自体を止め、
CASCADEは削除を子まで伝播させ、SET NULLは子を残しつつ関連だけを断つ。どれが正しいかは
業務要件次第で、`orders`のように削除してよいか慎重であるべき親には`RESTRICT`（デフォルトの
`NO ACTION`も同様に働く）、監査ログのように親が消えても記録自体は残したい子には`SET NULL`が
向く。

## よくあるつまずきと対処

| つまずき | 症状 | 原因 | 対処 |
|---|---|---|---|
| varchar(n)のnに業務意味を持たせすぎる | 「氏名は20文字のはず」と`varchar(20)`にしたら、想定外に長い名前でエラーが頻発する | 変わりやすい業務ルールを型そのものに固定してしまった | `text`にしてCHECK制約で上限を表現する。上限の変更がDDLの型変更ではなく制約の張り替えで済む |
| アプリのバリデーションだけに頼りDB制約を省く | バッチ処理や別サービスからの書き込みで、あり得ないはずの値が紛れ込む | 検証ロジックがアプリ層にしかなく、その経路を通らない書き込みを弾けない | NOT NULL/CHECK/UNIQUE/外部キーをDB側にも必ず設定する。アプリの検証は早期にエラーを返すUX目的と割り切る |
| jsonbに何でも入れて後で検索・整合性に困る | 特定の属性での検索が遅い、必須のはずのキーが抜けている行がある、他テーブルとJOINできない | 本来リレーショナルにモデリングすべき情報までjsonbに逃がした | 頻繁に検索・集計・JOINする属性は列やテーブルに戻す。jsonbは本当に可変な属性だけに限定する |

## 到達度チェックリスト

- [ ] float8とnumericの違いを、実際に`0.1 + 0.2`を実行して説明できる
- [ ] 数量・金額・日時・真偽・列挙のそれぞれに適した型を、根拠つきで選べる
- [ ] text/varchar(n)のどちらを使うか、業務ルールの変わりやすさを踏まえて判断できる
- [ ] NOT NULL/UNIQUE/CHECK/主キー/外部キーを組み合わせて、不正な状態を挿入できないテーブルを設計できる
- [ ] ON DELETE RESTRICT/CASCADE/SET NULLの違いを、子行のある親を削除する実験を通じて説明できる
- [ ] ENUM型・CHECK制約・参照テーブルのトレードオフを説明し、状況に応じて選べる
- [ ] 生成列を使って、qty×unit_priceのような派生値を常に整合させられる
- [ ] 「制約はアプリではなくDBで守る」理由を、複数の書き込み経路の例で説明できる

## 宿題（次回までの自習）

1. `products`に追加した`attributes`jsonb列を使い、カテゴリの異なる商品2〜3件に別々のキー構成の
   属性を入れる。`attributes ? 'キー名'`で特定の属性を持つ商品を検索するSQLを書く。
2. `order_items`に追加した`line_total`生成列を使い、顧客ごとの合計注文金額を
   `SUM(line_total)`で求めるSQLを書く（`orders`と`JOIN`する）。
3. `orders.status`をCHECK制約ではなく参照テーブル（`order_statuses`）＋外部キー方式に
   置き換えるとしたら、どんなDDLになるか設計だけメモする（実装は不要）。

## 次回への接続

第6回では、この回で身につけた制約の道具立てを使って、有効期間モデル（`product_prices`の
`valid_from`/`valid_to`）や注文の状態遷移など、より実務寄りのモデリング課題を扱う。
CHECK制約だけでは表現しきれない「重なりの禁止」を`EXCLUDE USING gist`で守る例も登場する。
詳しくは[第6回](06-modeling.md)で扱う。

## 参考

- PostgreSQL 公式ドキュメント "Data Types" 章 (https://www.postgresql.org/docs/current/datatype.html)
- 同 "Numeric Types" (https://www.postgresql.org/docs/current/datatype-numeric.html)
- 同 "Date/Time Types" (https://www.postgresql.org/docs/current/datatype-datetime.html)
- 同 "Enumerated Types" (https://www.postgresql.org/docs/current/datatype-enum.html)
- 同 "JSON Types" (https://www.postgresql.org/docs/current/datatype-json.html)
- 同 "Constraints" 章 (https://www.postgresql.org/docs/current/ddl-constraints.html)
- 同 "Generated Columns" (https://www.postgresql.org/docs/current/ddl-generated-columns.html)
- 同 "CREATE DOMAIN" リファレンス (https://www.postgresql.org/docs/current/sql-createdomain.html)