こんにちは。競プロで壁に跳ね返され続けているラクマの豊永です。
みなさんは、どんなときにデータベースに Unique 制約をつけるでしょうか。
私は過去に先輩とこんなやりとりをしたことがあります。
開発で新規追加するテーブル設計のレビューを受けている場面です。
私:「Unique 制約は必要ですか？」
先輩: 「必要ならつけるべき」
私:「...」
さて、データベースはMySQL、PostgreSQLなどありますが、今回は、「MySQL で Unique 制約をつける場面はどんなときか」というのを考えてみたいと思います。
Unique 制約とは
あるテーブル内のカラムのデータが1つであることを保証します。(一意性)
Web アプリケーションの場合で考えてみる
Web アプリケーション (Rails) でデータの一意性を保証したい場合
- Web アプリケーション側 (サーバー側) で、データ保存前に検証する
- MySQL 側に Unique 制約をつける
で実現すると思います。
さて最初の問いである「MySQL で Unique 制約をつける場面はどんなときか」の答えとして、
- Web アプリケーション側 (サーバー側) で、データ保存前に検証する
さえ満たしていれば、
- MySQL 側に Unique 制約をつける
は不要でしょうか。いや必要です。
必要になるケースについて見ていきましょう。
確認環境
$ rails --version Rails 5.2.3 $ mysql --version mysql Ver 14.14 Distrib 5.6.43, for osx10.13 (x86_64) using EditLine wrapper
検証
準備
MySQL
CREATE TABLE `hoges` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `uniq_test1` int(11) DEFAULT NULL, `uniq_test2` int(11) DEFAULT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_test2` (`uniq_test2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
INSERT INTO hoges VALUES (null, 11111, 22222, NOW(), NOW());
app/models/hoge.rb
class Hoge < ApplicationRecord
validates :uniq_test1, uniqueness: true
validates :uniq_test2, uniqueness: true
validate :hoge
def hoge
sleep(5)
end
end
Web アプリケーション側 (サーバー側)では、uniq_test1、uniq_test2 ともにデータ保存前に検証します。
今回は、rails console でモデルを create します。
$ rails c Running via Spring preloader in process 12528 Loading development environment (Rails 5.2.3) irb(main):002:0> ActiveRecord::Base.transaction do irb(main):003:1* Hoge.create(uniq_test1: 5, uniq_test2: nil) irb(main):004:1> end (1.9ms) BEGIN Hoge Exists (6.3ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` = 5 LIMIT 1 Hoge Exists (9.9ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` IS NULL LIMIT 1 Hoge Create (4.0ms) INSERT INTO `hoges` (`uniq_test1`, `created_at`, `updated_at`) VALUES (5, '2020-03-09 16:42:39', '2020-03-09 16:42:39') (6.9ms) COMMIT => #<Hoge id: 6, uniq_test1: 5, uniq_test2: nil, created_at: "2020-03-09 16:42:39", updated_at: "2020-03-09 16:42:39">
処理のおおまかな流れはこんな感じです。
- MySQL の transaction 開始
- データの一意性検証
- MySQL の transaction 終了 (ここでデータが作られる)
問題になるのはほぼ同時に処理が実行されたときです。
MySQL に Unique キー制約なし (悪いパターン)
コンソール1
$ rails c Running via Spring preloader in process 12742 Loading development environment (Rails 5.2.3) irb(main):001:0> ActiveRecord::Base.transaction do irb(main):002:1* Hoge.create(uniq_test1: 5, uniq_test2: nil) irb(main):003:1> end (2.2ms) SET NAMES utf8mb4, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483 (1.1ms) BEGIN Hoge Exists (3.2ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` = 5 LIMIT 1 Hoge Exists (1.4ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` IS NULL LIMIT 1 Hoge Create (4.3ms) INSERT INTO `hoges` (`uniq_test1`, `created_at`, `updated_at`) VALUES (5, '2020-03-09 16:55:12', '2020-03-09 16:55:12') (7.9ms) COMMIT => #<Hoge id: 7, uniq_test1: 5, uniq_test2: nil, created_at: "2020-03-09 16:55:12", updated_at: "2020-03-09 16:55:12">
コンソール2
$ rails c Running via Spring preloader in process 12757 Loading development environment (Rails 5.2.3) irb(main):001:0> ActiveRecord::Base.transaction do irb(main):002:1* Hoge.create(uniq_test1: 5, uniq_test2: nil) irb(main):003:1> end (1.8ms) SET NAMES utf8mb4, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483 (1.5ms) BEGIN Hoge Exists (2.0ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` = 5 LIMIT 1 Hoge Exists (5.1ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` IS NULL LIMIT 1 Hoge Create (5.1ms) INSERT INTO `hoges` (`uniq_test1`, `created_at`, `updated_at`) VALUES (5, '2020-03-09 16:55:13', '2020-03-09 16:55:13') (49.3ms) COMMIT => #<Hoge id: 8, uniq_test1: 5, uniq_test2: nil, created_at: "2020-03-09 16:55:13", updated_at: "2020-03-09 16:55:13">
Web アプリケーション側 (サーバー側) をすり抜けてしまいました！！！
MySQL に Unique キー制約あり (良いパターン)
コンソール1
$ rails c Running via Spring preloader in process 12742 Loading development environment (Rails 5.2.3) irb(main):001:0> ActiveRecord::Base.transaction do irb(main):002:1* Hoge.create(uniq_test1: nil, uniq_test2: 77777) irb(main):003:1> end (3.7ms) BEGIN Hoge Exists (1.4ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` IS NULL LIMIT 1 Hoge Exists (2.8ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` = 77777 LIMIT 1 Hoge Create (3.5ms) INSERT INTO `hoges` (`uniq_test2`, `created_at`, `updated_at`) VALUES (77777, '2020-03-09 16:57:01', '2020-03-09 16:57:01') (18.2ms) COMMIT => #<Hoge id: 9, uniq_test1: nil, uniq_test2: 77777, created_at: "2020-03-09 16:57:01", updated_at: "2020-03-09 16:57:01">
コンソール2
$ rails c
Running via Spring preloader in process 12757
Loading development environment (Rails 5.2.3)
irb(main):001:0> ActiveRecord::Base.transaction do
irb(main):002:1* Hoge.create(uniq_test1: nil, uniq_test2: 77777)
irb(main):003:1> end
(7.9ms) BEGIN
Hoge Exists (3.7ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` IS NULL LIMIT 1
Hoge Exists (1.8ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` = 77777 LIMIT 1
Hoge Create (5.4ms) INSERT INTO `hoges` (`uniq_test2`, `created_at`, `updated_at`) VALUES (77777, '2020-03-09 16:57:04', '2020-03-09 16:57:04')
(19.1ms) ROLLBACK
Traceback (most recent call last):
2: from (irb):4
1: from (irb):5:in `block in irb_binding'
ActiveRecord::RecordNotUnique (Mysql2::Error: Duplicate entry '77777' for key 'uniq_test2': INSERT INTO `hoges` (`uniq_test2`, `created_at`, `updated_at`) VALUES (77777, '2020-03-09 16:57:04', '2020-03-09 16:57:04'))
Web アプリケーション側 (サーバー側) をすり抜けたとしても、MySQL に保存したときに MySQL 側のエラーで弾くことができます。
まとめ
データの一意性を保証する必要があるなら、データベースに Unique キーをつけましょう！
副次的なメリットとして、Web アプリケーション側 (サーバー側) の実装漏れがあった場合も対処できます。
それでは失礼します。
