LoginSignup
4
1

More than 1 year has passed since last update.

@snyt45(snyt 45)in株式会社diddyworks

Rails+MySQLで参照整合性の崩壊を引き起こすデータ操作を検証してみた

Last updated at Posted at 2021-09-05

モチベーション

「スッキリわかるSQL入門」という書籍を進めていて、参照整合性の崩壊を引き起こすデータ操作が4パターンあるとのことを知り、外部キー周りの理解がなんとなくだったので実際に手を動かして検証してみようと思いました。

目的

この記事では、参照整合性の崩壊を引き起こすデータ操作を行った際に実際にどのような挙動になり、どのようにすれば防げるのかをRails+MySQLで検証します。

参照整合性とは

外部キーが指し示す先にきちんと行が存在してリレーションシップが成立していること。

参照整合性の崩壊を引き起こすデータ操作

「スッキリわかるSQL入門」という書籍で参照整合性の崩壊を引き起こすデータ操作として次の4つのパターンがあるとのことでした。

この4つのパターンを実際に試していこうと思います。

  1. 「ほかの行から参照されている」行を削除してしまう
  2. 「ほかの行から参照されている」行の主キーを変更してしまう
  3. 「存在しない行を参照する」行を追加してしまう
  4. 「存在しない行を参照する」行に更新してしまう

今回登場するテーブル

書籍を参考に「家計簿テーブル」と「費目テーブル」が登場します。

ER図

image.png

参照整合性が崩壊している例

例えば、以下の家計簿テーブルのid=2のレコードは、費目テーブルのid=2を参照しますが費目テーブルにはid=2のレコードが存在していないため参照整合性が取れていない状態になっています。

image_1.png

さっそく、検証してみる

何も意識せずにテーブルを作成する

今回は参照整合性の検証のため、インデックスやNOT NULL制約など何も考えずにテーブルを作成していきます。

# 家計簿テーブル作成
rails g model household_account_book date:date expense_item_id:integer memo:string income:integer expenditure:integer

# 費目テーブル作成
rails g model expense_item name:string

# マイグレーション実行
rails db:migrate

作成されたスキーマ(DB情報が反映されたもの)

# 家計簿テーブル
create_table "expense_items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t|
  t.string "name"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

# 費目テーブル
create_table "household_account_books", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t|
  t.date "date"
  t.integer "expense_item_id"
  t.string "memo"
  t.integer "income"
  t.integer "expenditure"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

データを用意する

費目テーブルのデータを作成します。

ExpenseItem.create!(name: "食費")
ExpenseItem.create!(name: "外食費")
ExpenseItem.create!(name: "交際費")
ExpenseItem.create!(name: "書籍代")
ExpenseItem.create!(name: "水道代")
ExpenseItem.create!(name: "電気代")
ExpenseItem.create!(name: "ガス代")
ExpenseItem.create!(name: "携帯代")
ExpenseItem.create!(name: "インターネット料金")
ExpenseItem.create!(name: "定期代")
ExpenseItem.create!(name: "病院代")
ExpenseItem.create!(name: "被服費")
ExpenseItem.create!(name: "日用品費")
ExpenseItem.create!(name: "住宅費")
ExpenseItem.create!(name: "給与")

家計簿テーブルのデータを作成します。
内容は適当に入力していきます。

HouseholdAccountBook.create!(date: Date.today, expense_item_id: 1, memo: "食費", income: nil, expenditure: 1000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 2, memo: "外食費", income: nil, expenditure: 2000) 
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 3, memo: "交際費", income: nil, expenditure: 3000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 4, memo: "書籍代", income: nil, expenditure: 4000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 5, memo: "水道代", income: nil, expenditure: 5000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 6, memo: "電気代", income: nil, expenditure: 6000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 7, memo: "ガス代", income: nil, expenditure: 7000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 8, memo: "携帯代", income: nil, expenditure: 8000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 9, memo: "インターネット料金", income: nil, expenditure: 9000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 10, memo: "定期代", income: nil, expenditure: 10000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 11, memo: "病院代", income: nil, expenditure: 11000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 12, memo: "被服費", income: nil, expenditure: 12000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 13, memo: "日用品費", income: nil, expenditure: 13000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 14, memo: "住宅費", income: nil, expenditure: 14000)
HouseholdAccountBook.create!(date: Date.today, expense_item_id: 15, memo: "給与", income: 1000000, expenditure: nil)

家計簿テーブルの状態。

image.png

費目テーブルの状態。

image.png

検証

1. 「ほかの行から参照されている」行を削除する

家計簿テーブルから参照されている費目テーブルの行を削除してみます。

→ 削除できました

irb(main):039:0> ExpenseItem.find_by(name: "食費").destroy
  ExpenseItem Load (0.4ms)  SELECT  `expense_items`.* FROM `expense_items` WHERE `expense_items`.`name` = '食費' LIMIT 1
   (0.2ms)  BEGIN
  ExpenseItem Destroy (0.3ms)  DELETE FROM `expense_items` WHERE `expense_items`.`id` = 1
   (2.6ms)  COMMIT
=> #<ExpenseItem id: 1, name: "食費", created_at: "2021-09-05 13:30:52", updated_at: "2021-09-05 13:30:52">

2. 「ほかの行から参照されている」行の主キーを変更する

家計簿テーブルから参照されている費目テーブルの行の主キーを変更してみます。

→ 変更できました。

irb(main):040:0> expense_item = ExpenseItem.find_by(name: "外食費")
  ExpenseItem Load (0.4ms)  SELECT  `expense_items`.* FROM `expense_items` WHERE `expense_items`.`name` = '外食費' LIMIT 1
=> #<ExpenseItem id: 2, name: "外食費", created_at: "2021-09-05 13:30:52", updated_at: "2021-09-05 13:30:52">

irb(main):041:0> expense_item.id = 99
=> 99

irb(main):042:0> expense_item.save!
   (0.3ms)  BEGIN
  ExpenseItem Update (0.3ms)  UPDATE `expense_items` SET `id` = 99, `updated_at` = '2021-09-05 13:55:48' WHERE `expense_items`.`id` = 2
   (4.6ms)  COMMIT
=> true

3. 「存在しない行を参照する」行を追加する

家計簿テーブルに費目テーブルに存在しない行を参照する行を追加してみます。

→ 追加できました。

irb(main):043:0> HouseholdAccountBook.create!(date: Date.today, expense_item_id: 999, memo: "存在しない費目", income: nil, expenditure: 999)
   (0.2ms)  BEGIN
  HouseholdAccountBook Create (0.3ms)  INSERT INTO `household_account_books` (`date`, `expense_item_id`, `memo`, `expenditure`, `created_at`, `updated_at`) VALUES ('2021-09-05', 999, '存在しない費目', 999, '2021-09-05 14:07:16', '2021-09-05 14:07:16')
   (2.9ms)  COMMIT
=> #<HouseholdAccountBook id: 16, date: "2021-09-05", expense_item_id: 999, memo: "存在しない費目", income: nil, expenditure: 999, created_at: "2021-09-05 14:07:16", updated_at: "2021-09-05 14:07:16">

4. 「存在しない行を参照する」行に更新する

家計簿テーブルで費目テーブルに存在する行を参照している行を存在しない行を参照するように更新してみます。

→ 更新できました。

irb(main):045:0> household_account_book = HouseholdAccountBook.find_by(memo: "外食費")
  HouseholdAccountBook Load (0.3ms)  SELECT  `household_account_books`.* FROM `household_account_books` WHERE `household_account_books`.`memo` = '外食費' LIMIT 1
=> #<HouseholdAccountBook id: 2, date: "2021-09-05", expense_item_id: 2, memo: "外食費", income: nil, expenditure: 2000, created_at: "2021-09-05 13:40:41", updated_at: "2021-09-05 13:40:41">
irb(main):046:0> household_account_book.expense_item_id = 998
=> 998
irb(main):047:0> household_account_book.save!
   (0.3ms)  BEGIN
  HouseholdAccountBook Update (0.3ms)  UPDATE `household_account_books` SET `expense_item_id` = 998, `updated_at` = '2021-09-05 14:10:33' WHERE `household_account_books`.`id` = 2
   (2.6ms)  COMMIT
=> true

検証結果

当たり前ですが、何も制約等を設定していないため、はこのように参照整合性の崩壊を引き起こすことができる状態になってしまっていることがわかりました。

外部キー制約を指定を行ってテーブルを作成する

参照整合性の崩壊は、絶対に避けなければなりません。

そういったミスを防ぐために、外部キー制約(FOREIGN KEY制約)があります。

外部キー制約を指定することで、先ほど検証した参照整合性の崩壊を引き起こすデータ操作を行った際に強制的に処理を中断するようになります。

実際に検証してみます。

外部キー制約を指定する

# 家計簿テーブルの費目idカラムに外部キー制約を指定
rails g migration AddExpenseItemIdToHouseholdAccountBook expense_item:references

# 生成されたマイグレーションファイル
class AddExpenseItemIdToHouseholdAccountBook < ActiveRecord::Migration[5.2]
  def change
    add_reference :household_account_books, :expense_item, foreign_key: true
  end
end

# データをリセットして、マイグレーションをやり直す
rails db:migrate:reset

これだとエラーになったため、最終的に下記のようにマイグレーションファイルを変更して再実行するとうまくいきました。
(rails db:rollbackもうまくいきました。)

class AddExpenseItemIdToHouseholdAccountBook < ActiveRecord::Migration[5.2]
  def up
    change_column :household_account_books, :expense_item_id, :bigint
    add_foreign_key :household_account_books, :expense_items, column: :expense_item_id
  end

  def down
    remove_foreign_key :household_account_books, column: :expense_item_id
    change_column :household_account_books, :expense_item_id, :integer
  end
end

スキーマファイルでも外部キー制約が指定されています。

ActiveRecord::Schema.define(version: 2021_09_05_143824) do

  create_table "expense_items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t|
    t.string "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "household_account_books", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t|
    t.date "date"
    t.bigint "expense_item_id"
    t.string "memo"
    t.integer "income"
    t.integer "expenditure"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["expense_item_id"], name: "fk_rails_619f5a0007"
  end

  add_foreign_key "household_account_books", "expense_items"
end

データを用意する

「何も意識せずにテーブルを作成する」と同じようにデータを用意。

検証

1. 「ほかの行から参照されている」行を削除する

家計簿テーブルから参照されている費目テーブルの行を削除してみます。

→ 削除できなくなりました！

irb(main):031:0> ExpenseItem.find_by(name: "食費").destroy
  ExpenseItem Load (0.3ms)  SELECT  `expense_items`.* FROM `expense_items` WHERE `expense_items`.`name` = '食費' LIMIT 1
   (0.2ms)  BEGIN
  ExpenseItem Destroy (0.5ms)  DELETE FROM `expense_items` WHERE `expense_items`.`id` = 1
   (2.4ms)  ROLLBACK
Traceback (most recent call last):
        1: from (irb):31
ActiveRecord::StatementInvalid (Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails (`app_development`.`household_account_books`, CONSTRAINT `fk_rails_619f5a0007` FOREIGN KEY (`expense_item_id`) REFERENCES `expense_items` (`id`)): DELETE FROM `expense_items` WHERE `expense_items`.`id` = 1)

2. 「ほかの行から参照されている」行の主キーを変更する

家計簿テーブルから参照されている費目テーブルの行の主キーを変更してみます。

→ 変更できなくなりました！

irb(main):032:0> expense_item = ExpenseItem.find_by(name: "外食費")
  ExpenseItem Load (0.3ms)  SELECT  `expense_items`.* FROM `expense_items` WHERE `expense_items`.`name` = '外食費' LIMIT 1
=> #<ExpenseItem id: 2, name: "外食費", created_at: "2021-09-05 15:16:46", updated_at: "2021-09-05 15:16:46">
irb(main):033:0> expense_item.id = 99
=> 99
irb(main):034:0> expense_item.save!
   (0.3ms)  BEGIN
  ExpenseItem Update (0.7ms)  UPDATE `expense_items` SET `id` = 99, `updated_at` = '2021-09-05 15:20:28' WHERE `expense_items`.`id` = 2
   (3.4ms)  ROLLBACK
Traceback (most recent call last):
        1: from (irb):34
ActiveRecord::StatementInvalid (Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails (`app_development`.`household_account_books`, CONSTRAINT `fk_rails_619f5a0007` FOREIGN KEY (`expense_item_id`) REFERENCES `expense_items` (`id`)): UPDATE `expense_items` SET `id` = 99, `updated_at` = '2021-09-05 15:20:28' WHERE `expense_items`.`id` = 2)

3. 「存在しない行を参照する」行を追加する

家計簿テーブルに費目テーブルに存在しない行を参照する行を追加してみます。

→ 追加できなくなりました！

irb(main):035:0> HouseholdAccountBook.create!(date: Date.today, expense_item_id: 999, memo: "存在しない費目", income: nil, expenditure: 999)
   (0.2ms)  BEGIN
  HouseholdAccountBook Create (0.6ms)  INSERT INTO `household_account_books` (`date`, `expense_item_id`, `memo`, `expenditure`, `created_at`, `updated_at`) VALUES ('2021-09-05', 999, '存在しない費目', 999, '2021-09-05 15:21:26', '2021-09-05 15:21:26')
   (3.1ms)  ROLLBACK
Traceback (most recent call last):
        2: from (irb):35
        1: from (irb):35:in `rescue in irb_binding'
ActiveRecord::InvalidForeignKey (Mysql2::Error: Cannot add or update a child row: a foreign key constraint fails (`app_development`.`household_account_books`, CONSTRAINT `fk_rails_619f5a0007` FOREIGN KEY (`expense_item_id`) REFERENCES `expense_items` (`id`)): INSERT INTO `household_account_books` (`date`, `expense_item_id`, `memo`, `expenditure`, `created_at`, `updated_at`) VALUES ('2021-09-05', 999, '存在しない費目', 999, '2021-09-05 15:21:26', '2021-09-05 15:21:26'))

4. 「存在しない行を参照する」行に更新する

家計簿テーブルで費目テーブルに存在する行を参照している行を存在しない行を参照するように更新してみます。

→ 更新できなくなりました！

irb(main):036:0> household_account_book = HouseholdAccountBook.find_by(memo: "外食費")
  HouseholdAccountBook Load (0.3ms)  SELECT  `household_account_books`.* FROM `household_account_books` WHERE `household_account_books`.`memo` = '外食費' LIMIT 1
=> #<HouseholdAccountBook id: 2, date: "2021-09-05", expense_item_id: 2, memo: "外食費", income: nil, expenditure: 2000, created_at: "2021-09-05 15:16:54", updated_at: "2021-09-05 15:16:54">
irb(main):037:0> household_account_book.expense_item_id = 998
=> 998
irb(main):038:0> household_account_book.save!
   (0.3ms)  BEGIN
  HouseholdAccountBook Update (0.6ms)  UPDATE `household_account_books` SET `expense_item_id` = 998, `updated_at` = '2021-09-05 15:22:19' WHERE `household_account_books`.`id` = 2
   (3.4ms)  ROLLBACK
Traceback (most recent call last):
        1: from (irb):38
ActiveRecord::InvalidForeignKey (Mysql2::Error: Cannot add or update a child row: a foreign key constraint fails (`app_development`.`household_account_books`, CONSTRAINT `fk_rails_619f5a0007` FOREIGN KEY (`expense_item_id`) REFERENCES `expense_items` (`id`)): UPDATE `household_account_books` SET `expense_item_id` = 998, `updated_at` = '2021-09-05 15:22:19' WHERE `household_account_books`.`id` = 2)

検証結果

外部キー制約を追加したことにより、ROLLBACKを行い例外が発生するようになりました。

まとめ

外部キーを持つテーブルのカラムには、外部キー制約を指定して参照整合性の崩壊を引き起こすデータ操作ができないようにしよう！

4
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
1