はじめに
本エントリーは某社内で実施するデザインパターン勉強会向けの資料となります。
本エントリーで書籍「SQL アンチパターン」をベースに学習を進めます。書籍上でのサンプルコードはMySQLですが、本エントリーでのサンプルコードはT-SQLに置き換えて解説します。
IDリクワイアドとは
すべてのテーブルには「id」という名を持つ主キー列が存在しなければならない、という考えに起因するアンチパターンです。
主キーはテーブルのすべての行が一意であることを保証するものであり、また外部キーから参照されることでテーブル間の関連付けを行う重要な役目を持ちます。
しかし、すべてのテーブルに主キーとしてのid列を加えると、意図に反した影響が生じることがあります。
冗長なキーが作成されてしまう
以下の定義でテーブル「Bugs」を作成したとします。
| カラム物理名 | 主キー | ユニーク |
|---|---|---|
| id | ○ | |
| bug_id | ○ | |
| description |
CREATE TABLE Bugs (
id BIGINT NOT NULL PRIMARY KEY,
bug_id VARCHAR(10) UNIQUE,
description VARCHAR(1000),
-- 他の列...
);
bug_id列は各行で一意となっているため、「自然な」主キー（自然キー）として使えます。
したがって、id列は冗長なキーとなってしまいます。
重複行を許可してしまう
以下の定義で交差テーブル「BugsProducts」を作成したとします。
| カラム物理名 | 主キー | 外部キー |
|---|---|---|
| id | ○ | |
| bug_id | ○ | |
| product_id | ○ |
CREATE TABLE BugsProducts (
id BIGINT NOT NULL PRIMARY KEY,
bug_id BIGINT NOT NULL FOREIGN KEY REFERENCES [Bugs]([bug_id]),
product_id BIGINT NOT NULL FOREIGN KEY REFERENCES [Products]([product_id])
);
本来、bug_idとproduct_idの値の組み合わせが、テーブル上で一意であることを保証しなければなりません。
例）
■ Bugsテーブル
| bug_id | description |
|---|---|
| 1 | TimeoutException |
| 2 | NullReferenceException |
| ■ Productsテーブル |
| product_id | product_name |
|---|---|
| 100 | x |
| 101 | y |
| ■ BugsProductsテーブル |
| id | bug_id | product_id |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 2 | 101 |
BugsProductsテーブルでid列を主キーとして使用してしまうと、bug_idとproduct_idの組み合わせが常に一意であることを保証しなくなってしまい、以下のような重複データの挿入を許可してしまいます。
| id | bug_id | product_id |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 1 | 100 |
INSERT INTO BugsProducts (bug_id, product_id)
VALUES (1, 100), (1, 100);
交差テーブル内への重複データの挿入を防ぐためには、id列以外の2列にUNIQUE制約を宣言することもできますが、この場合主キーとしてのid列は冗長な列となってしまいます。
キーの意味が分かりにくくなる
「id」という列名は一般的で、明確な意味を持ちません。idという主キー名を持つ2つのテーブルを結合する際に留意する必要があります。
例）下記の2テーブル、「Bugs」と「Accounts」を結合し、データを抽出する場合
■ Accountsテーブル
| id | name |
|---|---|
| 1 | x |
| 2 | y |
| ■ Bugsテーブル |
| id | assigned_to | status |
|---|---|---|
| 100 | 1 | OPEN |
| 101 | 2 | CLOSE |
| ↓ |
SELECT b.id, a.id
FROM Bugs b
INNER JOIN Accounts a ON b.assigned_to = a.id
WHERE b.status = 'OPEN';
↓
■ 抽出結果
| id | assigned_id | status |
|---|---|---|
| 100 | 1 | OPEN |
この場合、Accountsテーブルのidをaccount_idに、Bugsテーブルのidをbug_idにすれば、抽出条件を「bug_id = 100」とするだけで、データベース上の1行を特定することができます。
USINGを使用する
2つのテーブルの結合条件を表すとき、両テーブルに同じ名前の列がある場合に、USINGを使用した書き方があります。
例）以下の2つのテーブル「Bugs」と「BugsProducts」を、同名の列「bug_id」で結合する場合
■ Bugsテーブル
| bug_id | description |
|---|---|
| 1 | TimeoutException |
| 2 | NullReferenceException |
| ■ BugsProductsテーブル |
| id | bug_id | product_id |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 2 | 101 |
| join構文を用いると、以下のように書きます。 |
SELECT * FROM Bugs as b
INNER JOIN BugsProducts as bp
ON b.bug_id = bp.bug_id;
USINGを使用すると、以下のように書き表すこともできます。
SELECT * FROM Bugs INNER JOIN BugsProducts USING (bug_id)
すべてのテーブルで主キーを必ず「id」という名前にしなければならないとすると、従属テーブル側のBugsProductsテーブルの外部キー列には参照するBugsテーブルの主キーと同じ名前は使えないため、USINGは使用できず、常に下記のような冗長な構文を使用しなければならなくなります。
SELECT * FROM Bugs as b
INNER JOIN BugsProducts as bp
ON b.id = bp.bug_id;
複合キーは使いにくい
複合主キーを参照する外部キーは、それ自体も複合外部キーでなければならないため、複合キーを使用する場合にはコードが長くなってしまいます。
複合主キーは使いにくい、という理由で安易に主キーとしてのid列を作成してしまうと、上述のような問題が生じる可能性があります。
解決策
アンチパターンとならない解決策として、以下の3点が挙げられます。
・わかりやすい列名にする
主キーの名前は、主キーが識別する対象のエンティティを表すものにすべきです。
・規則にとらわれない
多くのフレームワークは、idの規約を上書きし、別の名前を宣言することも可能です。
・自然キーと複合キーの活用
下記の特徴を持つ属性がテーブルに含まれる場合、その列は自然キーとして使えます。
・一意であることが保証できる
・NULLを許容しない
・行の識別に使える
複合キーは、交差テーブルのように行を識別するための適切な方法が複数の属性列の組み合わせである場合に使います。
まとめ
規約にとらわれず、場合に応じて列名をつけることが大切です。