MySQLはバージョン8.0.16からCHECK制約が追加されました。このCHECK制約は、MySQL 5.0の時代から上がっているバグレポート「MySQL Bugs: #3464: Constraints: support CHECK」にもあるように、MySQLで多くの方が待ちわびた機能のひとつかもしれません。
今回はMySQL 8.0.16で追加されたCHECK制約の機能を確認し、利用の際の注意点を見ていきましょう。
なお、利用している環境はCentOS 7で、MySQLはバージョン8.0.21を利用しています。
CHECK制約
CHECK制約は、テーブルにデータを(挿入も含む)更新する際に条件を満たすか検証し、もし満たさない場合はエラーにしてしまう機能になります。たとえば、UNSIGNED TINYINTで定義されたカラムには通常0~255までの数値を取ることができますが、CHECK制約を用いることで、0~10までの数値に限定することが可能です。
実際にテーブルを作成して動作を確認してみましょう。11より小さい数かどうか検証するCHECK制約をつけたテーブルを作成し、10と11の値を挿入してみます。
11の値はCHECK制約違反でエラーとなりました。今度は、すでにあるデータ10を11に変更してみます。
更新も同様にCHECK制約違反でエラーとなりました。
CHECK制約の作成
前節で動きを確認できたところで、CHECK制約の定義方法を確認しましょう。CHECK制約は、CHECK(式の評価)
で定義することができます。CHECK制約は、テーブル制約またはカラム制約として指定することができます。
カラム制約は、そのカラム自身のみを参照して定義する制約です。テーブル制約は、テーブルの列を複数参照して定義することが可能です。下のDDLの例でいうと、最初の2つのCHECK制約はカラム制約、あとの2つのCHECK制約はテーブル制約に当たります。なお、どちらの制約であってもCHECK制約の名称は任意につけることは可能で、指定しない場合は<テーブル名>_chk_<番号>
の形式で生成されます。
作成したCHECK制約の確認
作成したCHECK制約は、SHOW CREATE TABLE構文で確認することができます。また、INFORMATION_SCHEMA
のCHECK_CONSTRAINTS
テーブルから、CHECK制約の一覧を取得することができます。
実際にCHECK制約を運用してCHECK制約違反が出た場合は、performance_schema
のevents_errors_summary_by_*
系のテーブルのER_CHECK_CONSTRAINT_VIOLATED
のエラーカウントが計測されます。もし、CHECK制約によってエラーが起こっているけどアプリケーションで握り潰されてしまっている場合等は、こちらを参照するのもよいかもしれません。
CHECK制約の制限
CHECK制約の利用にはいくつか制限があります。以下の内容に当てはまるものについては利用できません。
- AUTO_INCREMENTを設定したカラム
- 他のテーブルのカラムを参照してCHECK制約を定義する
- ストアドファンクション、ストアドプロシージャの利用
- 変数
- サブクエリ
- 非決定性関数(NOW()やCURRENT_USER()等)
その他の詳細な条件については、MySQL公式ドキュメント13.1.20.6 CHECK Constraintsをご確認ください。
また、CHECK制約を変更する場合はALTER TABLE構文で変更する必要があります。ただし、データがすでにCHECK制約違反になるようであれば、CHECK制約違反としてALTER TABLE構文はエラーとなるため、事前にCHECK制約違反をしていなか確認する必要があります。
まとめ
今回はCHECK制約について簡単に見ていきました。実際に利用する際には、INT型ではなく日付や文字列型であったり、場合によってはCHECK制約の中でCASE文等を利用するかもしれません。
CHECK制約は正しい形でデータを入れるという面ではとても有用です。しかし、ガチガチに固めた制約だらけのテーブルの場合だと、データの復旧作業やテストデータの生成時等に弊害となってしまう恐れもあります。そのあたりも考慮しながら利用していくことをおすすめします。