Help us understand the problem. What is going on with this article?

SQLアンチパターン勉強会 第三回:IDリクワイアド

More than 1 year has passed since last update.

はじめに

本エントリーは某社内で実施するデザインパターン勉強会向けの資料となります。

本エントリーで書籍「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を許容しない
   ・行の識別に使える

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

まとめ

規約にとらわれず、場合に応じて列名をつけることが大切です。

ayayo
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away