データの一意性を保証するとき、データベース (MySQL) で Unique 制約をつけた方が良さそうな件 #MySQL

みなさんは、どんなときに MySQL で Unique 制約をつけるでしょうか。

「必要ならつける！」が答えなのですが、どんな時に必要でしょうか。

今回は、「MySQL で Unique 制約をつける場面はどんなときか」というのを考えてみたいと思います。

Unique 制約とは

あるテーブル内のカラムのデータが1つであることを保証します。(一意性)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 1.7.3.1 PRIMARY KEY および UNIQUE インデックス制約

Web アプリケーションの場合で考えてみる

Web アプリケーション (Rails) でデータの一意性を保証したい場合

Web アプリケーション側 (サーバー側) で、データ保存前に検証する
MySQL 側に Unique 制約をつける

で実現すると思います。

さて最初の問いである「MySQL で Unique 制約をつける場面はどんなときか」の答えとして、

1. Web アプリケーション側 (サーバー側) で、データ保存前に検証する

さえ満たしていれば、

2. 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 側のエラーで弾くことができます。

まとめ

データの一意性を保証する必要があるなら、MySQL に Unique キーをつけましょう！

副次的なメリットとして、Web アプリケーション側 (サーバー側) の実装漏れがあった場合も対処できます。