第2章 テーブル設計の基本 — 主キー・外部キー・リレーション

2.1 テーブル・行・列とデータ型

第1章で見たとおり、リレーショナルデータベースはすべてのデータをテーブル(表)で持ちます。行(レコード)が1件のデータ、列(カラム)がデータの項目です。Excelと決定的に違うのは、列ごとに「入れられるデータの種類(データ型)」をあらかじめ決めておくことです。Excelのセルには数値でも文字でも日付でも何でも入りますが、データベースでは「salary列は整数」と宣言したら、原則としてそこには整数しか入りません。この「入り口の厳しさ」が整合性の第一歩です。

SQLiteの主なデータ型は次の4つです。

NULL —「値がない」という特別な状態

どの型の列にも入りうる特別な状態が NULL(ヌル)です。NULLは「0」でも「空文字」でもなく、「値がまだない・分からない」という状態そのものを表します。たとえば「配属先が未定の新入社員」の部署IDは、0でも空文字でもなくNULLであるべきです。0なら「0番という部署」に見えてしまい、空文字は数値列にはそもそも入れられません。

2.2 主キー — 行を一意に特定する

テーブルには「この値を見れば行が1つに決まる」列が必要です。これを主キー(PRIMARY KEY)と呼びます。「社員番号3番の給与を更新して」「注文ID 1052をキャンセルして」——データベースへの依頼は、必ず主キーで対象を指定します。主キーがなければ、更新も削除も「どの行のことか」を確実に指せません。

なぜ「氏名」を主キーにしてはいけないのか

初学者が最初にやりがちな設計が「氏名を主キーにする」ことです。これは2つの理由で破綻します。

• 同姓同名 — 「田中 実」さんが2人入社した瞬間、主キーの「一意」という約束が守れなくなります。数百人規模の会社なら同姓同名は普通に起きます。
• 改姓 — 結婚などで姓が変わると主キーの値を書き換えることになります。主キーは他のテーブルから参照される「住所」のようなものなので、変更するとすべての参照先を追いかけて直す羽目になります。

つまり主キーに向くのは、①絶対に重複せず、②一度決めたら変わらない値です。氏名はどちらの条件も満たしません。

自然キーとサロゲートキー

主キーの選び方には2つの流儀があります。

実務では「意味を持たない連番なら絶対に変更する理由が生まれない」という安心感から、サロゲートキーが広く使われます。SQLiteでは INTEGER PRIMARY KEY と宣言した列に、値を指定せずINSERTすると自動で連番が振られます。

sqlite> CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
sqlite> INSERT INTO members (name) VALUES ('佐藤 花子');
sqlite> INSERT INTO members (name) VALUES ('田中 実');
sqlite> SELECT * FROM members;
1|佐藤 花子
2|田中 実

idを一切書いていないのに、1、2と自動で採番されました。MySQLの AUTO_INCREMENT、PostgreSQLの SERIAL も同じ発想の機能です。

2.3 外部キーとリレーション

第1章の社員テーブルには dept_id という列がありました。これは「部署テーブル(departments)のid列の値を入れる」列で、このように他のテーブルの主キーを参照する列を外部キー(FOREIGN KEY)と呼びます。

部署1つに対して社員は何人もいます。逆に、1人の社員が所属する部署は1つです。このような関係を1対多(いちたいた)の関係と呼び、リレーショナルデータベースで最も頻出する形です。ポイントは、外部キーは必ず「多」の側に置くこと。社員(多)の側に dept_id を持たせるのであって、部署の側に「社員1, 社員2, 社員3…」と列を増やすのではありません。

CREATE TABLE departments (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id      INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  dept_id INTEGER REFERENCES departments(id)  -- 外部キー
);

sqlite> PRAGMA foreign_keys = ON;
sqlite> INSERT INTO departments VALUES (10, '総務部');
sqlite> INSERT INTO employees VALUES (1, '佐藤 花子', 10);   -- 総務部は存在するのでOK
sqlite> INSERT INTO employees VALUES (2, '鈴木 一郎', 99);   -- 99番という部署は存在しない
Runtime error: FOREIGN KEY constraint failed (19)

存在しない部署への配属が、入り口で止められました。これが外部キー制約の価値です。「あとで気づいて直す」のではなく、間違ったデータをそもそも入れないのです。

多対多の関係と中間テーブル

では「社員とプロジェクト」の関係はどうでしょう。1人の社員は複数のプロジェクトを掛け持ちし、1つのプロジェクトには複数の社員が参加します。これは多対多の関係で、外部キー1本では表現できません。そこで、2つのテーブルの間に「組み合わせだけを記録するテーブル」を挟みます。これを中間テーブル(関連テーブル)と呼びます。

CREATE TABLE projects (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

-- 中間テーブル:「誰が」「どのプロジェクトに」参加しているかの組み合わせ
CREATE TABLE employee_projects (
  employee_id INTEGER NOT NULL REFERENCES employees(id),
  project_id  INTEGER NOT NULL REFERENCES projects(id),
  PRIMARY KEY (employee_id, project_id)
);

「佐藤さんが基幹システム更改とWebサイト刷新に参加している」なら、中間テーブルに (1, 1) (1, 2) という2行を入れるだけです。多対多は「1対多を2つ組み合わせたもの」に分解できる——この発想は、受講者と講座、商品と注文など、実務のあらゆる場面で再登場します。

2.4 ER図を読む・描く

テーブル同士の関係を図にしたものが ER図(Entity-Relationship Diagram)です。実務では設計書の中心になる図で、「読める」ことはIT部門の必須スキルです。ここまでの社員・部署・プロジェクトの関係をテキストで描くと、こうなります。

+-------------+          +--------------+          +--------------------+          +-----------+
| departments |          |  employees   |          | employee_projects  |          | projects  |
+-------------+          +--------------+          +--------------------+          +-----------+
| id (PK)     |─────────<| id (PK)      |─────────<| employee_id (FK)   |>─────────| id (PK)   |
| name        |  1     多| name         |  1     多| project_id  (FK)   |多      1 | name      |
+-------------+          | dept_id (FK) |          +--------------------+          +-----------+
                         +--------------+

読み方のポイントは線の両端です。─< のように線が枝分かれしている側(鳥の足に見えるので「カラスの足」と呼ばれます)が「多」、枝分かれしていない側が「1」です。上の図は左から「1つの部署に多くの社員」「1人の社員に多くの参加記録」「多くの参加記録が1つのプロジェクトに属する」と読めます。PKは主キー、FKは外部キーの略です。

2.5 CREATE TABLEで設計を形にする — 制約という防波堤

設計をSQLに落とすとき、主キー・外部キー以外にも「間違ったデータを入り口で止める仕組み」を宣言できます。これらを総称して制約(constraint)と呼びます。

全部入りの例を見てみましょう。第3章から使う社員テーブルの「制約強化版」です。

CREATE TABLE employees (
  id        INTEGER PRIMARY KEY,
  name      TEXT    NOT NULL,
  email     TEXT    UNIQUE,
  dept_id   INTEGER REFERENCES departments(id),
  hire_date TEXT    DEFAULT (date('now')),
  salary    INTEGER CHECK (salary >= 0)
);

制約に違反するデータを入れようとすると、DBMSが即座に拒否します。

sqlite> INSERT INTO employees (id, name, salary) VALUES (7, '山本 剛', -500);
Runtime error: CHECK constraint failed: salary >= 0 (275)
sqlite> INSERT INTO employees (id, salary) VALUES (8, 300000);
Runtime error: NOT NULL constraint failed: employees.name (1299)

まとめ

• 列にはデータ型を宣言する。SQLiteの主要型はINTEGER・TEXT・REAL・BLOBで、日付はTEXTで扱う。NULLは「0」でも「空文字」でもなく「値がない」状態
• 主キーは行を一意に特定する値。「重複せず、変わらない」ことが条件で、氏名は同姓同名・改姓のため不適格。実務では連番のサロゲートキーが定番
• 外部キーは他テーブルの主キーを参照する列で、1対多の「多」の側に置く。SQLiteでは PRAGMA foreign_keys = ON を忘れない
• 多対多は中間テーブルを挟んで1対多×2に分解する。ER図は「カラスの足」の側が多
• NOT NULL・UNIQUE・DEFAULT・CHECK・FOREIGN KEYの制約は「間違ったデータを入り口で止める」最後の防波堤
• 個人情報の列は必要最小限に。持っていないデータは漏れない

練習問題

PRAGMA foreign_keys = ON;

CREATE TABLE books (
  id     INTEGER PRIMARY KEY,
  title  TEXT NOT NULL,
  author TEXT NOT NULL
);

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

CREATE TABLE loans (
  id          INTEGER PRIMARY KEY,
  book_id     INTEGER NOT NULL REFERENCES books(id),
  user_id     INTEGER NOT NULL REFERENCES users(id),
  loan_date   TEXT NOT NULL DEFAULT (date('now')),
  return_date TEXT
);

sqlite> INSERT INTO books VALUES (1, 'SQL実践入門', '技術 太郎');
sqlite> INSERT INTO users VALUES (1, '佐藤 花子', '総務部');
sqlite> INSERT INTO loans (book_id, user_id) VALUES (1, 1);   -- 正常に登録できる
sqlite> INSERT INTO loans (book_id, user_id) VALUES (999, 1); -- 存在しない書籍
Runtime error: FOREIGN KEY constraint failed (19)