SQLアンチパターンのID リクワイアドパターンのデメリットと解決方法を選択

こちらの記事では、「SQLアンチパターン」に登場するIDリクワイアドについてまとめたものになります。

ID リクワイアドとは

全てのテーブルに「id」というカラムを用意して主キーにしなければならないという考えが原因で作られるテーブルのアンチパターンです。

一意に特定するために主キーとなる id は絶対必要！という発言がよく見受けられますが、すべてのテーブルに主キーとしての id 列を加えると、意図に反した影響が生じることがあります。

ID リクワイアドのデメリット

冗長なキーが作成されてしまう

以下のようにバグを扱うテーブルを Bags テーブルとして定義したときのことを考えます。

CREATE TABLE Bugs (  
  id           BIGINT NOT NULL PRIMARY KEY,  
  bug_id       VARCHAR(10) UNIQUE,  
  description  VARCHAR(1000)
);  

主キーは id であり、このテーブルの行を特定するための一意の値になります。一方で、bug_id もユニークIDとして定義されており id 列が冗長なキーとなっていると言えます。

重複行を許可してしまう

bug_id, product_id の組み合わせが一意となるような複合キーを持つ BugsProducts テーブルを考えます。

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])   
);  

慣習に習って、id を主キーとしてしまうと、bug_id, product_id の組み合わせが一意であることの保証がなくなってしまいます。この場合、二つのキーを UNIQUE キーの設定することができますが、それであれば主キーの id が冗長になってしまいます。

キーの意味が分かりにくくなる

全てのテーブルに id という名前の列が存在すると、テーブル同士を結合して表示する際に、どのテーブルの id なのかが分かりにくくなってしまいます。

例えば、Bugs テーブルと Accounts テーブルを結合して表示する時の SQL をみてみます。

SELECT b.id, a.id 
FROM Bugs b
INNER JOIN Accounts a ON b.assigned_to = a.id
WHERE b.status = 'OPEN';

Bugs テーブルにも Accounts テーブルにも id というカラムが存在するため、どちらの id を参照するのかがすぐにはわからないです。

データベースの中でも一意に特定しやすいように、bug_id, account_id とする方がわかりやすいかと思います。

USINGを使用する

二つのテーブルを結合する時、どちらのテーブルにも同じ名前の列があるとき、USING を使用して SQL を記述することができます。

SELECT * FROM Bugs as b
INNER JOIN BugsProducts as bp 
ON b.bug_id = bp.bug_id;

この SQL を 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列 を作成してしまうと、これまで紹介したデメリットに遭遇する可能性が高くなります。

ID リクワイアドの改善方法

わかりやすい列名にする

主キーの列名は、対象のエンティティを特定できるような名前を付けるとわかりやすくなります。

規則にとらわれない

ORM の制約により id という名前を使わざるを得ないこともあるかと思いますが、ORM によっては設定を変更することもできるため可能であれば上書きしてわかりやすい名前をつけるとよいと思います。

自然キーと複合キーの活用

一意であることが保証できる、NULL を許容しない、行の識別に使えるという条件を満たしている場合は、その列は自然キーとして使うことができます。

複合キーは、交差テーブルのように行を識別するための適切な方法が複数の属性列の組み合わせである場合に使います。

まとめ

主キーは id という名前でなければならないという固定概念にとらわれず状況に応じて不必要な主キーを作らない、わかりやすい名前のキーにすることが大切になります。