MySQL道普請便り

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

この記事を読むのに必要な時間:およそ 2 分

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

著者プロフィール

深町日出海(ふかまちひでみ)

GMOメディア株式会社のデータベースエンジニア。主にOracleとMySQLを担当。得意なプログラム言語はJava。MySQLの好きなところはTABLEやINDEXの識別子に64byteまで使えるところ。

Twitter:@lhfukamachi