第136回　CHECK制約を利用してみよう

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の値を挿入してみます。

mysql> CREATE TABLE t1(num TINYINT UNSIGNED CHECK(num < 11));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1(num) VALUES (10);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1(num) VALUES (11);
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.

11の値はCHECK制約違反でエラーとなりました。今度は、すでにあるデータ10を11に変更してみます。

mysql> SELECT * FROM t1;
+------+
| num  |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> UPDATE t1 SET num = 11 WHERE num = 10;
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.

更新も同様にCHECK制約違反でエラーとなりました。

CHECK制約の作成

前節で動きを確認できたところで、CHECK制約の定義方法を確認しましょう。CHECK制約は、CHECK(式の評価)で定義することができます。CHECK制約は、テーブル制約またはカラム制約として指定することができます。

カラム制約は、そのカラム自身のみを参照して定義する制約です。テーブル制約は、テーブルの列を複数参照して定義することが可能です。下のDDLの例でいうと、最初の2つのCHECK制約はカラム制約、あとの2つのCHECK制約はテーブル制約に当たります。なお、どちらの制約であってもCHECK制約の名称は任意につけることは可能で、指定しない場合は<テーブル名>_chk_<番号>の形式で生成されます。

mysql> CREATE TABLE t1(
    ->   num1 INT CHECK (num1 > 1),
    ->   num2 INT CONSTRAINT num2_chk CHECK (num2 > 0),
    -> CHECK (num1 *2 < num2),
    -> CONSTRAINT t1_chk CHECK (num1 <> 0)
    -> );
Query OK, 0 rows affected (0.01 sec)

作成したCHECK制約の確認

作成したCHECK制約は、SHOW CREATE TABLE構文で確認することができます。また、INFORMATION_SCHEMAのCHECK_CONSTRAINTSテーブルから、CHECK制約の一覧を取得することができます。

mysql> SELECT * FROM information_schema.check_constraints;
+--------------------+-------------------+-----------------+-------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE            |
+--------------------+-------------------+-----------------+-------------------------+
| def                | check_database    | t1_chk_1        | (`num1` > 1)            |
| def                | check_database    | num2_chk        | (`num2` > 0)            |
| def                | check_database    | t1_chk_2        | ((`num1` * 2) < `num2`) |
| def                | check_database    | t1_chk          | (`num1` <> 0)           |
+--------------------+-------------------+-----------------+-------------------------+
4 rows in set (0.01 sec)

実際に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制約違反をしていなか確認する必要があります。

mysql> insert into t1 values(0),(1),(2);
Query OK, 3 rows affected (0.01 sec)
mysql> ALTER TABLE t1 ADD CONSTRAINT CHECK(num > 0);
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.

まとめ

今回はCHECK制約について簡単に見ていきました。実際に利用する際には、INT型ではなく日付や文字列型であったり、場合によってはCHECK制約の中でCASE文等を利用するかもしれません。

CHECK制約は正しい形でデータを入れるという面ではとても有用です。しかし、ガチガチに固めた制約だらけのテーブルの場合だと、データの復旧作業やテストデータの生成時等に弊害となってしまう恐れもあります。そのあたりも考慮しながら利用していくことをおすすめします。