本記事の目的

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

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

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

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

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

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

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列についても、デメリットを学ぶことができました。

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