MySQL8.0のCHECK制約を試してみる

MySQL 8.0
この記事は最終更新から3年以上経過しています。内容が古くなっている可能性があります。

はじめに

MySQL8.0.16(2019/04/25リリース)で、新機能としてCHECK制約が追加されました。

同機能は他のRDMSには既に実装されていることが多く、MySQLユーザにとっては待望の機能と言えます。

本記事では、この機能の概要について説明したいと思います。

CHECK制約とは

CHECK制約は、その名の通りテーブルに挿入されるデータを精査して条件に見合ったものでない場合は弾いてくれる機能です。

MySQLでは、元々カラムごとに設定されたデータ型があり、その型に合わないデータは弾く仕様になっています(sql_modeの設定によってはエラーになりません)。

例: 数値型(int)の id カラムに対し、文字列(”aaa”)は入れることができない

CHECK制約を使えば、上記の仕様では防げないケースにも対応することができます。例えば、以下のような例が考えられます。

  • 未成年のユーザのデータ登録を弾きたい(ageカラム > 19)
  • 軽減税率対象の品目は消費税率を8%、それ以外は10%とする(特定ジャンルの商品であれば tax_rate = 0.08、それ以外は 0.1)
  • IPアドレスを数値データとして格納する

CHECK制約の使い方

CEHCK制約はテーブルに対して設定します。そのため、CREATE TABLEALTER TABLEを実行する時に条件句をつけることで有効になります。例えば、上記の例で age カラムに年齢制限を付けたい場合は以下のように実行できます。

このテーブルに対して19歳以下のユーザを登録しようとするとエラーで失敗します。この時、どのCHECK制約に抵触したことを知らせるメッセージがクライアントに返されますので、エラーの原因も明確に分かります。
勿論、20歳以上のユーザは問題なく登録することができます。

既に存在するテーブルは、ALTER TABLE … ADD CHECK/DROP CHECK文で制約の追加・削除ができます。この時、既にテーブルに制約に抵触するレコードが存在する場合、制約を設定できませんのでご注意ください。
ちなみに、CHECK制約を付与するDDLはオンラインDDLではないようです。

さらに、CHECK制約では case 文や正規表現も使用可能で、多少複雑な条件を設定できます。以下は、商品の分類が食品(軽減税率の対象)の時には税率が8%、それ以外は10%にしようとした例を示しています。

ちなみに、上記のcartテーブルの定義は、以下のDDLと同義です。こちらの方が定義としてはよりスマートかもしれません。

CHECK制約の注意点

CHECK制約にもいくつか機能としての制限事項があります。

  • auto_incrementを設定したカラムでは利用できません
  • 他のテーブルのカラムを制約として参照できません
  • 制約にストアドプロシージャ、ストアドファンクション、ユーザ定義関数を指定できません
  • サブクエリは利用できません
  • ON UPDATE, ON DELETEなど操作で使用される外部キーが貼られたカラムでは利用できません
  • CHECKの対象となるのはINSERT, UPDATE, REPLACE, LOAD DATA, LOAD XML(IGNOREオプション含む)

レプリケーションとCHECK制約

最後にCHECK制約をレプリケーション環境で使用するケースを考えてみます。

例えば、レプリカにのみCHECK制約を設定した場合などはどうなるかというとソースでINSERTが成功されてもレプリカでCHECK制約によってデータは挿入できません。

レプリカでは以下のようにCHECK制約のエラーが発生します。現実にソース・レプリカで別々のCHECK制約を設定するケースはないと思いますが、ご注意ください。

まとめ

以上、MySQLの新機能であるCHECK制約について説明しました。

今後、既にCHECK制約を利用している他の商用データベースからMySQLに移行する際も少し楽になるかもしれません。さらなる機能強化などにも注目していきたいと思います。

参考サイト

How to Use CHECK Constraint in MySQL 8


MySQL

 

スマートスタイルTECHブログについて

スマートスタイルTECHブログでは、日頃MySQLのサポート業務に従事している有資格者で構成された技術サポートチームがMySQLに関する技術情報を発信しています。データベースのお困りごとはお気軽にご相談下さい。

よかったらシェアしてね!
  • URLをコピーしました!
目次