主キー・IDの設計について #MySQL

はじめに

データベース設計を学ぶ人は、誰しもテーブルにとって主キーが大切であることを学びます。主キーはテーブルのすべての行が一意であることを保証するための重要な制約です。
本記事では、主キーをどのように設定するのが良いか、またそのアンチパターンを解説します。

主キーとIDの違い

本記事では度々、主キーとIDという単語が出てきますが、主キー≠IDです。

主キー（Primary Key）...データベースのテーブル内で、各行を一意に識別するためのカラム。一意であればIDだけでなく、コードもOK
ID（Identifier）...識別子（Identifier）は、一般的にあるエンティティを識別するために使用される値です。より広義で使われ、概念的な意味合いを持ちます。

主キー設計のアンチパターン

アンチパターン１：なんでもサロゲートキー（代替キー）をつける

すべてのテーブルに「ID」という名前のカラムを主キーを付けているケースをよく見かけます。
しかし、すべてのテーブルにIDカラムを加えると意図に反した影響が生じることがあります。

弊害１：冗長なキーができる

テーブルに「自然なキー」（ナチュラルキー）として使えそうなカラムが存在しても、慣習として「ID」という名前を付け、主キーとするケースがあります。
ナチュラルキーとはキーそのものに意味が含まれているキーで、業務的にそのテーブルをユニークにするキーです。

カテゴリーテーブルを例にしてみます。外部キー制約を持たせる時、「ID」か「CategoryID」のどちらを外部キーにするのか、実装者が迷う可能性があります。
また、「ID」という余分な列が存在するのは、単純に無駄です。

CREATE TABLE category (
    ID INT PRIMARY KEY AUTO_INCREMENT,-- とりあえずID
    CategoryID VARCHAR(255) UNIQUE　,-- ユニーク制約
    CategoryName VARCHAR(255) NOT NULL
);

INSERT INTO category (CategoryID, CategoryName) 
    VALUES ('C00101', 'Books');

一意（ユニーク）であり、必須であり、最初に決めた値から変更されることがないカラムは主キーとして利用することができます。

弊害２：重複行を許可してしまう

多対多の関係を表したい時、交差テーブル（中間テーブル）がよく用いられます。
この、交差テーブルの主キーをサロゲートキーにしてしまうと、関係を表したい値が一意であることを保証できなくなります。

カテゴリとタグの例を考えてみます。「CategoryID」と「TagID」が多対多の関係とし、主キーを「ID」とします。
この場合、「CategoryID」と「TagID」に同じ値を入れても、制約を付けていないため、重複を許可してしまいます。

CREATE TABLE CategoryTags (
    ID INT PRIMARY KEY AUTO_INCREMENT,-- とりあえずID
    CategoryID INT NOT NULL,
    TagID INT NOT NULL,
    FOREIGN KEY (CategoryID) REFERENCES category(CategoryID)
    FOREIGN KEY (TagID) REFERENCES tag(TagID)
);

INSERT INTO CategoryTags (CategoryID, TagID) 
    VALUES (101, 1),(101, 1);-- 重複を許可してしまう

交差テーブルの重複を防ぐには、「CategoryID」と「TagID」を複合主キーとするか、UNIQUE制約を宣言するようにしましょう。

アンチパターン２：IDに意味をもたせ、複数の目的として使う

IDの使い方に関するアンチパターンです。

学生テーブルを例に考えます。
学籍番号を「cb2024001」のように、学部名 + 入学年度 + 名前順を組み合わせることがあります。

ここまでは全く問題ありません。
この後、ロジック側がこの学籍番号をみてユーザー情報を判断することが、アンチパターンになります。

学生テーブルに対し、学部カラムをもたず、学籍番号から学部を判別するロジックを考えると、次のようなコードになります。

// コンピュータ学部を判断する
function isComputerScience($学籍番号) {
    return substr($学籍番号, 0, 2) === "cb";
}

// 芸術学部を判断する
function isArtFaculty($学籍番号) {
    return substr($学籍番号, 0, 2) === "ab";
}
...

このコードの問題点は、学部が増えた時にロジックを修正する必要がでるほか、cbがコンピューター学部ではなく、理学部に変更になった時、全く別の意味になってしまう点にあります。
IDに対し、ビジネスロジックを持たせたり、複数の意味合いを持たせることは避けましょう。

素直に、学籍番号カラムとは別に、学部カラムを持たせることがよいでしょう。

よい主キー・IDとは一体...?

よい主キー：わかりやすい列名にする

主キーには、わかりやすい列名をつけましょう。
主キーの名前は、対象のエンティティを表すものとし、スキーマ内で一意としてください。
カテゴリーテーブルの主キーは「CategoryID」が相応しいです。

そうすると、外部キーを設定する際も命名に迷わずにすみますし、ひと目見た時にわかりやすいです。

よいID：IDがずっと変わらず使い続ける

考慮すべきポイントは以下です。

IDの寿命
他のシステムとIDの整合性

IDの寿命

まず考えるのは、IDが何通りのラベルをつけられるかです。先ほどの学籍番号の例では最後の三桁を「001」のように、名前のあいう順としていましたが、一度に入学する学生が1000人以上の場合に三桁では対応できません。
基本的には、半永久的に枯渇しないようなIDを選択するのが望ましいです。物理設計時に、キツキツの桁数にするのではなく、あえて余裕を持った桁にすることも有効です。
また、限りなく重複がない文字列としてUUIDを検討するのも一つの手です。

他のシステムとIDの整合性

他のシステムとIDの整合性が取れているかという点はID設計で重要です。
システムが複数あり、共通IDを使いたい場合、最初からIDをどう共有するかという運用まで含めて事前に設計しておくことが大切です。
（学籍番号や商品ID、部品番号、SNSのユーザーIDなどは共通化するべきである可能性が高い。）

おわりに

主キー設計について、アンチパターンと考慮すべきポイントをまとめました。

参考文献

SQLアンチパターン　3章　IDリクワイド（とりあえずID）
https://www.oreilly.co.jp/books/9784873115894/

失敗から学ぶ RDBの正しい歩き方
https://gihyo.jp/book/2019/978-4-297-10408-5

漢のコンピューター道
https://nippondanji.blogspot.com/2013/12/blog-post_8.html