Blog

論理設計⑥ 代理キー

2024.12.28

本記事の目的

テーブルにおいて、主キーに該当するカラムが存在しない場合があります。そのような場合、代理キーを使用する方法があります。本記事では、代理キーを採用するメリットや具体的な実装方法について解説します。

主キーに該当するカラムがない

テーブルにおいて、各行を一意に識別するための自然キーが存在しない場合、データの整合性を確保するために主キーを設ける必要があります。このような場合には、代理キー（Surrogate Key）を採用することが一般的です。

システム開発の経験がある人ならピンとくるかもしれませんが、データベース設計でよく見かける「ID」というカラムも、この代理キーの一つです。たとえば、自動的に連番が振られる「オートナンバリング」の仕組みを使ったカラムが典型的な代理キーの例です。 ※詳細については後述します。

1. 自然キーが存在しない場合

テーブル内のデータに基づく一意な識別子（自然キー）が存在しない場合、代理キーが必要になります。例えば、以下のようなデータでは、一意な識別子が明確でないため、代理キーを採用する必要があります。

名前	年齢	性別
佐藤太郎	30	男性
鈴木次郎	25	男性

2. 自然キーが複数カラムにまたがる場合

自然キーが複数のカラムを組み合わせて初めて一意性を持つ場合、外部キーを設定する際に複雑になります。代理キーを採用することで、設計や管理が簡潔になります。

例: 複合キーの例（代理キーを導入しない場合）

CREATE TABLE orders (
    user_id INT,
    product_id INT,
    PRIMARY KEY (user_id, product_id)
);

代理キーを導入する場合：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT
);

3. 自然キーが変更される可能性がある場合

自然キーがデータ内容に依存している場合、変更が発生するとリレーションに影響を及ぼします。代理キーを採用すると、自然キーが変更されてもテーブル間のリレーションを維持できます。

例: email を自然キーにした場合、ユーザーがメールアドレスを変更すると問題になる可能性があります。

CREATE TABLE users (
    email VARCHAR(255) PRIMARY KEY, -- 問題が発生する可能性
    name VARCHAR(100)
);

代理キーを使用することで解決：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    name VARCHAR(100)
);

これらのケースでは、代理キーを使用することでテーブル設計がシンプルになり、保守性が向上します。

オートナンバリング

オートナンバリングとは、データベースにおいて、指定された列に自動的に一意の数値を割り振る仕組みのことです。主に主キーや代理キーとして利用され、行ごとに一意な識別子を生成する際に便利です。

実現方法としては、

シーケンスオブジェクト
ID列

の二つがあります。

シーケンスオブジェクト

シーケンス（順番）の名前の通り、順番を表すデータベースオブジェクトです。 SELECT 文を使用してシーケンスにアクセスすることで、一意な連番を取得することができます。例えば、PostgreSQLでシーケンスを作成して利用する方法は以下の通りです。

// START 1 は連番の開始値を指定します。
// INCREMENT 1 は次の値をどれだけ増加させるかを指定します。
CREATE SEQUENCE example_seq START 1 INCREMENT 1; 

SELECT nextval('example_seq') AS new_id;
new_id
-------
1

次に紹介する「ID列」より細かく設定することができます。ただし、MySQLではシーケンスはサポートされていません。その代わりとして、MySQLでは「採番テーブル」を利用する方法がよく使われます。採番テーブルとは、一意な連番を管理するための専用のテーブルを作成し、そのテーブルを利用して連番を生成する仕組みです。

ID列

ID列は、データベーステーブルにおいて各行（レコード）を一意に識別するために設けられる列（カラム）のことです。多くの場合、主キー（Primary Key）として設定され、他のテーブルとリレーションを結ぶ際の基準としても利用されます。

以下は、MySQLでID列を設定する例です

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 自動採番されるID列
    name VARCHAR(100),
    email VARCHAR(255)
);

実装自体は非常に簡単なので、よく見かけるかと思います。 ID列を設定するだけで、データ挿入時に自動的に一意な値が割り当てられるため、非常に効率的です。これにより、開発者はIDの管理を気にすることなく、データの整合性と一意性を確保できます。

ただ、シーケンスオブジェクトほど指定できるオプションが少なく、DBMSによってID列の実装が異なるため、他のDBに移植する際は注意が必要です。

MySQL
- AUTO_INCREMENT
PostgreSQL
- SERIAL / BIGSERIAL
- GENERATED AS IDENTITY
Oracle
- IDENTITY
SQL Server
- IDENTITY

と言った感じで、移植性が低い傾向にあります。

シーケンスオブジェクトとID列、どちらを使う？

必ずしも『xxxがベスト』という意見ではありませんが、『基本的には』実装がシンプルな『ID列』を推したいです。

これまで開発してきたシステムでは、このID列で十分な場合が多かったからです。そのため、『基本的には』ID列を使う方向で問題ないと考えています。

ただし、複数のテーブルで同一の採番を使用したい場合や、専用の管理が必要な場合など、より高度な要件がある場合はシーケンスオブジェクトを利用する方が便利かなと思っています。今のところ、そのような要件には出会っていませんが、将来的にそのようなケースが発生した際には、シーケンスオブジェクトを検討してみたいと考えています。

終わり

私はMySQLを使うプロジェクトが多かったため、正直シーケンスオブジェクトを使う頻度は非常に少なかったです。普段当たり前に使っていたID列についても、デメリットを学ぶことができました。

これまで何も考えずに使っていた部分もあったので、今後はこれらを念頭に設計できたらと思います！それでは、お付き合いいただきありがとうございました:)

よみこみちゅう...

J-Nagano