はじめに

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

こんな人に読んで欲しい

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

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

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

概要

システム概要

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

運用期間：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>

失敗から学んだこと

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

テストケースの作り方については失敗しないテストケースの作り方と、効率よくテストを進める方法が参考になるかもしれません。併せて読んでみてください。

まとめ

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

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