こんにちは、エンジニアの川本です。
主にBUYMAの決済・配送を担当しているチームでバックエンドの開発をしています。
この記事は Enigmo Advent Calendar 2023 の 20 日目の記事です。
個人開発でPlanetScaleというMySQL互換のサーバーレスデータベースを使用しているのですが、特筆すべき仕様として外部キーのサポートがありません。
外部キー制約はDBレベルで強い整合性を担保できる便利な手段ですが、PlanetScaleではその機能が利用できないので、アプリケーションレベルで整合性を担保する必要があります。
MySQLの外部キーのオプションにはいくつか種類がありますが、これらが使えない場合にアプリケーション側ではどのように担保すればよいのでしょうか？
今回は、Railsを例にしてアプリケーション側でMySQLの外部キーに相当する機能をどのように担保できるのかを検証してみようと思います。
余談：PlanetScaleについて
最近PlanetScaleはベータ版で外部キーをサポートし始めましたが、残念ながらHobbyプランではまだサポートされておりません。
PlanetScaleの基盤であるVitessはOnline DDLの機能を提供しており、それが原因で外部キーのサポートが長らく難しかったようです。
以下のドキュメントやブログには、PlanetScaleが外部キーをサポートできるようになるまでの背景や課題、そしてその克服方法についての詳細な情報が記載されています。興味がある方はぜひ読んでみてください。
外部キーのサポートが難しかった理由
外部キーをサポートするための取り組み
親子関係のテーブルを作成
まず親子関係にある、Parent, Childテーブルを作成してサンプルデータを入れる。
-- テーブル作成 mysql> CREATE TABLE parent ( -> id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; mysql> CREATE TABLE child ( -> id INT NOT NULL, -> parent_id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; -- テストデータをインサート mysql> INSERT INTO parent (id) VALUES (1), (2); mysql> INSERT INTO child (id, parent_id) VALUES (1, 1), (2, 1), (3, 2); -- データ構造の確認 mysql> SELECT * FROM parent p JOIN child c ON p.id = c.parent_id; +----+----+-----------+ | id | id | parent_id | +----+----+-----------+ | 1 | 1 | 1 | | 1 | 2 | 1 | | 2 | 3 | 2 | +----+----+-----------+
MySQLの外部キー制約
MySQLでは以下4つのON DELETE副次句で指定できる参照アクションがあります。
ON UPDATE副次句もありますが、今回はON DELETEに限定することにします。
ON DELETE CASCADE
親テーブルから行を削除し、子テーブル内の一致する行を自動的に削除する。
-- ON DELETE CASCADEを指定して外部キー制約を設定 mysql> ALTER TABLE child -> ADD CONSTRAINT fk_parent -> FOREIGN KEY (parent_id) -> REFERENCES parent(id) -> ON DELETE CASCADE; -- parentのid = 1のレコードを削除する mysql> DELETE FROM parent WHERE id = 1; -- parent_id = 1のchildのレコードも削除されていることを確認できる mysql> SELECT * FROM child; +----+-----------+ | id | parent_id | +----+-----------+ | 3 | 2 | +----+-----------+
ON DELETE SET NULL
親テーブルから行を削除し、子テーブルの外部キーカラムをNULLにする。
※ この設定をするときは、childのparent_idはNOT NULLにしない。
-- ON DELETE SET NULLを指定して外部キー制約を設定 mysql> ALTER TABLE child -> ADD CONSTRAINT fk_parent -> FOREIGN KEY (parent_id) -> REFERENCES parent(id) -> ON DELETE SET NULL; -- parentのid = 1のレコードを削除する mysql> DELETE FROM parent WHERE id = 1; -- parent_id = 1のchildのレコードのparent_idはNULLになっていることを確認 mysql> SELECT * FROM child; +----+-----------+ | id | parent_id | +----+-----------+ | 1 | NULL | | 2 | NULL | | 3 | 2 | +----+-----------+
ON DELETE RESTRICT or ON DELETE NO ACTION or 指定なし
親テーブルに対する削除操作は拒否されます。また、ON DELETE RESTRICT or ON DELETE NO ACTION or ON DELETE 指定なしは同じ挙動になります。以下の例ではON DELETE 指定なしで例を示します。
-- ON DELETE指定なしで外部キー制約を設定 mysql> ALTER TABLE child -> ADD CONSTRAINT fk_parent -> FOREIGN KEY (parent_id) -> REFERENCES parent(id) -- parentのid = 1のレコードを削除する -- childにはparent_id = 1のレコードがあるので削除拒否される mysql> DELETE FROM parent WHERE id = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`myapp_development`.`child`, CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) -- parentもchildも削除されていない mysql> SELECT * FROM parent p JOIN child c ON p.id = c.parent_id; +----+----+-----------+ | id | id | parent_id | +----+----+-----------+ | 1 | 1 | 1 | | 1 | 2 | 1 | | 2 | 3 | 2 | +----+----+-----------+
Rails側の実装方法
Railsでは、Active Recordのdependentオプションを使用して、MySQLの外部キー制約に相当する機能を実現できます。
dependentオプションは親レコードに対してActiveRecord::Persistence#destroyが実行されたときに、紐ずいている子レコードに対して実行されるメソッドのことです。
ON DELETE CASCADE
ON DELETE CASCADEに相当することは、delete_all, destory, destory_asyncのいずれかで実現することができます。これら3つは全て最終的に実現できることは同じですが、それぞれで以下のように挙動の違いがあります。
delete_all
delete_allは、parentに関連付けられたchildが一括で1つのSQLで削除します。
また、childに対してActiveRecord::Persistence#deleteが実行されるので、ActiveRecord::Persistence#destroy実行時に作用するbefore_destroyやafter_destroyといったコールバックや孫クラスのdependentオプションが実行されません。
そのため、単純に削除SQLを実行するだけなので関連するchildが多い場合にはdestroyよりパフォーマンスが向上する可能性があリます。
class Parent < ApplicationRecord self.table_name = 'parent' has_many :child, dependent: :delete_all end
irb(main):002> parent = Parent.find(1) irb(main):054> parent.destroy TRANSACTION (0.7ms) BEGIN Child Delete All (1.2ms) DELETE FROM `child` WHERE `child`.`parent_id` = 1 Parent Destroy (0.7ms) DELETE FROM `parent` WHERE `parent`.`id` = 1 TRANSACTION (1.9ms) COMMIT => #<Parent:0x0000ffffaf032050 id: 1>
destroy
destroyは、parentに紐づくchildを全て取得して1件ずつ削除します。
ActiveRecord::Persistence#destroyが実行されるため、before_destroyやafter_destroyなどのコールバックも実行され、孫クラスにあるdependentオプションも実行されます。
そのため、関連するchildが多いと発行されるSQLも増え、コールバックの実行や孫クラスのdependentオプションの実行が多くなり、delete_allよりもパフォーマンスが低下する可能性があります。
class Parent < ApplicationRecord self.table_name = 'parent' has_many :child, dependent: :destroy end
irb(main):002> parent = Parent.find(1) irb(main):062> parent.destroy TRANSACTION (0.3ms) BEGIN Child Load (1.0ms) SELECT `child`.* FROM `child` WHERE `child`.`parent_id` = 1 Child Destroy (1.3ms) DELETE FROM `child` WHERE `child`.`id` = 1 Child Destroy (1.1ms) DELETE FROM `child` WHERE `child`.`id` = 2 Parent Destroy (0.9ms) DELETE FROM `parent` WHERE `parent`.`id` = 1 TRANSACTION (1.2ms) COMMIT => #<Parent:0x0000ffffafdad888 id: 1>
destroy_async
destroy_asyncは、parentに関連する全てのchildを非同期で1件ずつ削除します。
紐づくchildが非常に多く、即時での削除を求められない場合に有効です。紐づくchildが多いと処理が最悪の場合はタイムアウトする可能性もあります。そのような場合、まずparentを削除してクライアントにレスポンスを速やかに返し、残りの紐づくchildは非同期で削除することで問題を解決できます。
class Parent < ApplicationRecord self.table_name = 'parent' has_many :child, dependent: :destroy_async end
irb(main):002> parent = Parent.find(1) irb(main):070> parent.destroy TRANSACTION (0.3ms) BEGIN Child Load (0.8ms) SELECT `child`.* FROM `child` WHERE `child`.`parent_id` = 1 Parent Destroy (0.8ms) DELETE FROM `parent` WHERE `parent`.`id` = 1 TRANSACTION (2.0ms) COMMIT Enqueued ActiveRecord::DestroyAssociationAsyncJob (Job ID: 63fc4528-934a-405c-9311-7bee9fb706b1) to Async(default) with arguments: {:owner_model_name=>"Parent", :owner_id=>1, :association_class=>"Child", :association_ids=>[1, 2], :association_primary_key_column=>:id, :ensuring_owner_was_method=>nil} => #<Parent:0x0000ffffae761700 id: 1> irb(main):071> Performing ActiveRecord::DestroyAssociationAsyncJob (Job ID: 63fc4528-934a-405c-9311-7bee9fb706b1) from Async(default) enqueued at 2023-12-16T09:16:43Z with arguments: {:owner_model_name=>"Parent", :owner_id=>1, :association_class=>"Child", :association_ids=>[1, 2], :association_primary_key_column=>:id, :ensuring_owner_was_method=>nil} Parent Load (3.0ms) SELECT `parent`.* FROM `parent` WHERE `parent`.`id` = 1 LIMIT 1 Child Load (5.1ms) SELECT `child`.* FROM `child` WHERE `child`.`id` IN (1, 2) ORDER BY `child`.`id` ASC LIMIT 1000 TRANSACTION (0.3ms) BEGIN Child Destroy (0.9ms) DELETE FROM `child` WHERE `child`.`id` = 1 TRANSACTION (2.0ms) COMMIT TRANSACTION (0.3ms) BEGIN Child Destroy (1.0ms) DELETE FROM `child` WHERE `child`.`id` = 2 TRANSACTION (1.7ms) COMMIT Performed ActiveRecord::DestroyAssociationAsyncJob (Job ID: 63fc4528-934a-405c-9311-7bee9fb706b1) from Async(default) in 63.81ms
ON DELETE SET NULL
nullify
ON DELETE SET NULLに相当することはnullifyで実現できます。
parentに紐づくchildのparent_idをnullに更新して、parentを削除しています。
class Parent < ApplicationRecord self.table_name = 'parent' has_many :child, dependent: :nullify end
irb(main):088> parent = Parent.find(1) irb(main):090> parent.destroy TRANSACTION (0.3ms) BEGIN Child Update All (5.0ms) UPDATE `child` SET `child`.`parent_id` = NULL WHERE `child`.`parent_id` = 1 Parent Destroy (3.3ms) DELETE FROM `parent` WHERE `parent`.`id` = 1 TRANSACTION (1.3ms) COMMIT => #<Parent:0x0000ffffae66f9a0 id: 1>
ON DELETE RESTRICT or ON DELETE NO ACTION or 指定なし
ON DELETE RESTRICTまたはON DELETE NO ACTIONに相当することは、
restrict_with_exceptionまたはrestrict_with_errorのいずれかで実現することができます。これら2つは全て最終的に実現できることは同じですが、それぞれで以下のように挙動の違いがあります。
restrict_with_exception
parentに紐づくchildが存在することを確認して、処理をロールバックしてActiveRecord::DeleteRestrictionErrorという例外を発生させます。
class Parent < ApplicationRecord self.table_name = 'parent' has_many :child, dependent: :restrict_with_exception end
irb(main):088> parent = Parent.find(1) irb(main):094> parent.destroy TRANSACTION (0.6ms) BEGIN Child Exists? (1.0ms) SELECT 1 AS one FROM `child` WHERE `child`.`parent_id` = 1 LIMIT 1 TRANSACTION (0.5ms) ROLLBACK /usr/local/bundle/gems/activerecord-7.0.8/lib/active_record/associations/has_many_association.rb:16:in `handle_dependency': Cannot delete record because of dependent child (ActiveRecord::DeleteRestrictionError)
restrict_with_error
parentに紐づくchildが存在することを確認して、処理をロールバックしてfalseを返します。
class Parent < ApplicationRecord self.table_name = 'parent' has_many :child, dependent: :restrict_with_error end
irb(main):088> parent = Parent.find(1) irb(main):098> parent.destroy TRANSACTION (0.5ms) BEGIN Child Exists? (0.6ms) SELECT 1 AS one FROM `child` WHERE `child`.`parent_id` = 1 LIMIT 1 TRANSACTION (0.4ms) ROLLBACK => false
最後に
ここまでの紹介で、RailsアプリケーションでMySQLの外部キー制約の参照アクションを実現する手段が理解できました。
ただし、データ整合性が担保されるのは、外部キー制約に準拠したアプリケーションからの実行時に限られます。もし、同じDBを参照するが外部キー制約に準拠していないアプリケーションが存在する場合、どのような影響が生じますでしょうか？
外部キー制約のないアプリケーションからの実行により、データ整合性が維持されなくなる可能性があります。このような事態を避けるためには、できるだけDBレベルで整合性を担保する方が望ましいです。
Planet Scaleのような外部キー制約をサポートしていないDBでは、今回紹介したようなアプリケーションの実装が有効であるかもしれません。しかし、外部キー制約がサポートされているDBでは、DBレベルでの制御が安全であると言えるでしょう。
明日の記事担当はデータエンジニアリングチームです！お楽しみに！
株式会社エニグモ すべての求人一覧
