【初心者向け】失敗から学ぶ外部キー制約

# はじめに

私は1年以上稼働しているシステムを運用しているエンジニアです。
今回は私がDB設計で失敗し外部キー制約について学んだことを記事にします。
少しでも皆さんの参考になれば幸いです。

# こんな人に読んで欲しい

- DB設計をしている
- DB設計を勉強中
- 外部キー制約について学びたい
- 運用中のテーブルに変更をかけたい

# 目次

- 皆さんにお伝えしたいこと
- 概要
- 失敗事例
- 間違った設計
- バグ調査
- 正しい設計
- 失敗から学んだこと
- まとめ

# 皆さんにお伝えしたいこと

まず初めに、エンジニアの皆さんにお伝えしたいことは<font color="red">漏れなくテストしよう！！！</font>です。
いきなりなんだ？？と思うかもしれませんが、結局本記事で最も伝えたいことはこれなので先にお伝えしました。
それでは概要をお伝えしていきます。

# 概要

## システム概要

登録した利用者情報を管理するシステムです。
利用者(エンドユーザー)はフォームから各情報を入力し、登録します。
管理者(クライアント)は管理画面から利用者情報を変更することができます。

- 運用期間：1年4ヶ月
- 利用者数：数万人
- 開発言語：
  - フロントエンド：Vue.js
  - バックエンド：Ruby on Rails
  - DBMS：MySQL

当時、既存システムは以下設計で運用していました。
※紹介にあたり設計を簡略化しています。

Customerテーブル

|項目名|概要|必須/任意|
|-|-|-|
|name|名前|必須|
|age|年齢|必須|
|phone_number|電話番号|必須|


Customerテーブルのマイグレーションファイル

```XXXXXXXX_create_customer.rb
class CreateCustomers < ActiveRecord::Migration[7.0]
  def change
    create_table :customers do |t|
      t.string :name, null: false
      t.integer :age, null: false
      t.string :phone_number, null: false

      t.timestamps
    end
  end
end
```

```schema.rb
create_table "customers", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
  t.string "name", null: false
  t.integer "age", null: false
  t.string "phone_number", null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end
```

## 経緯

利用者情報にお気に入り店舗情報を**必須項目**として追加したいという要望があり、
お気に入り情報マスタテーブルを作成し、利用者テーブルと外部キーで紐づけることにしました。

# 失敗事例

私はお気に入り情報マスタテーブルを設計し、新規利用者については問題なくレコード登録されていることをテストで確認しました。
しかし、管理画面から既存利用者情報を更新できないというバグを発見しました。

# 間違った設計

バグ発見時の設計について紹介します。
どこが間違っているのか考えながら見てみてください。

Customerテーブル

|外部キー|項目名|概要|必須/任意|
|-|-|-|-|
||name|名前|必須|
||age|年齢|必須|
||phone_number|電話番号|必須|
|FK|favorite_store_id|お気に入り店舗ID|必須|

FavoriteStoreマスタテーブル

|項目名|概要|必須/任意|
|-|-|-|
|name|店舗名|必須|

FavoriteStoreマスタテーブルのマイグレーションファイル

```XXXXXXXX_create_favorite_store.rb
class CreateFavoriteStores < ActiveRecord::Migration[7.0]
  def change
    create_table :favorite_stores do |t|
      t.string :name, null: false

      t.timestamps
    end
  end
end
```

CustomerテーブルへFavoriteStoreマスタテーブルの外部キーを設定するマイグレーションファイル

```XXXXXXXX_add_references_to_customer.rb
class AddReferencesToCustomer < ActiveRecord::Migration[7.0]
  def change
    add_column :customers, :favorite_store_id, :integer, null: false
  end
end
```

```schema.rb
create_table "customers", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
  t.string "name", null: false
  t.integer "age", null: false
  t.string "phone_number", null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.integer "favorite_store_id", null: false
end

create_table "favorite_stores", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
  t.string "name", null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end
```

CustomerのModelファイル

```customer.rb
class Customer < ApplicationRecord
  belongs_to :favorite_store
end
```

FavoriteStoreのModelファイル

```favorite_store.rb
class FavoriteStore < ApplicationRecord
  has_one :customer
end
```

# バグ調査

既存のCustomerレコードが実際に更新できないのかどうかをコンソール上で確認したところ以下エラーメッセージが出力されました。

```error.log
raise_validation_error: Favorite storeを入力してください (ActiveRecord::RecordInvalid)
```

また、既存のレコードを見てみるとこのようなデータになっていました。

```customer_record.log
#<Customer:0x0000ffff9b536560
id: 1,
name: "test taro",
age: 25,
phone_number: "080-1111-2222",
created_at: Thu, 18 Apr 2024 20:29:40.644320000 JST +09:00,
updated_at: Thu, 18 Apr 2024 20:29:40.644320000 JST +09:00,
favorite_store_id: 0>
```

## 原因

Customerテーブルへ`favorite_store_id`を必須項目として後から追加したことで、Railsの仕様で既存レコードには`id=0`が設定されていました。
レコード更新を行った際に`id=0`のFavoriteStoreレコードを紐づけることができず(idは1から設定される)エラーが発生しました。
また、この設計ではそもそも外部キー制約が設定されていません。

# 正しい設計

本来あるべき正しい設計を示します。
Customerテーブルへ外部キーを設定するマイグレーションファイルを以下のようにする必要があります。
**add_reference**を設定することで以下メリットがあります。

- {項目名}_idを自動設定してくれる
- インデックスを自動設定してくれる

```XXXXXXXX_add_references_to_customer.rb
class AddReferencesToCustomer < ActiveRecord::Migration[7.0]
  def change
    add_reference :customers, :favorite_store, foreign_key: true
  end
end
```

CustomerのModelファイル
**optional: true**を設定することで、既存レコードが`nil`であることを許容し、更新をかけることが可能となります。
※しかし、新規レコードの登録についてもnilを許容してしまうのでバリデーションをかけるなどして対応しましょう。

```customer.rb
class Customer < ApplicationRecord
  belongs_to :favorite_store, optional: true
end
```

また、既存のレコードは失敗設計時のデータと比較し**favorite_store_id**が`0`から`nil`になります。

```customer_record.log
#<Customer:0x0000ffff9b536560
id: 1,
name: "test taro",
age: 25,
phone_number: "080-1111-2222",
created_at: Thu, 18 Apr 2024 20:29:40.644320000 JST +09:00,
updated_at: Thu, 18 Apr 2024 20:29:40.644320000 JST +09:00,
favorite_store_id: nil>
```

# 失敗から学んだこと

改めて今回の失敗を通して最も大切だと思ったことは<font color="red">漏れなくテストしよう！！！</font>です。
結局、DB設計が間違っていたとしても`既存データが問題なく更新できること`がしっかりテストされていればバグに気づくことができますし、そこで正しい設計を理解することができます。

テストケースの作り方については[失敗しないテストケースの作り方と、効率よくテストを進める方法](https://qangaroo.jp/info/test-case-plan-do/)が参考になるかもしれません。併せて読んでみてください。

# まとめ

今回はシステムや経緯について省略しましたが、実際は数十万のデータや個人情報を扱っているため、これがリリースした後に発覚すれば大きな障害となってしまう可能性があります。
皆さんが失敗する前に何か参考になれば幸いです。
正しい設計について誤りやより良い方法があれば、ぜひコメントお願いします。
ここまで読んでくださりありがとうございました！