はじめに
MySQL8.0.16（2019/04/25リリース）で、新機能としてCHECK制約が追加されました。
同機能は他のRDMSには既に実装されていることが多く、MySQLユーザにとっては待望の機能と言えます。
本記事では、この機能の概要について説明したいと思います。
CHECK制約とは
CHECK制約は、その名の通りテーブルに挿入されるデータを精査して条件に見合ったものでない場合は弾いてくれる機能です。
MySQLでは、元々カラムごとに設定されたデータ型があり、その型に合わないデータは弾く仕様になっています（sql_modeの設定によってはエラーになりません）。
例： 数値型（int）の id カラムに対し、文字列（”aaa”）は入れることができない
|
1 2 |
mysql> INSERT INTO `t1` VALUES ("aaa"); ERROR 1366 (HY000): Incorrect integer value: 'aaa' for column 'id' at row 1 |
CHECK制約を使えば、上記の仕様では防げないケースにも対応することができます。例えば、以下のような例が考えられます。
- 未成年のユーザのデータ登録を弾きたい（ageカラム > 19）
- 軽減税率対象の品目は消費税率を8%、それ以外は10%とする（特定ジャンルの商品であれば tax_rate = 0.08、それ以外は 0.1）
- IPアドレスを数値データとして格納する
CHECK制約の使い方
CEHCK制約はテーブルに対して設定します。そのため、CREATE TABLEやALTER TABLEを実行する時に条件句をつけることで有効になります。例えば、上記の例で age カラムに年齢制限を付けたい場合は以下のように実行できます。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> CREATE TABLE `users` (`id` INT, `name` TEXT, `age` TINYINT unsigned CONSTRAINT `age_check` CHECK (age > 19)); mysql> SHOW CREATE TABLE `users`\G *************************** 1. row *************************** Table: users Create Table: CREATE TABLE `users` ( `id` int DEFAULT NULL, `name` text, `age` tinyint unsigned DEFAULT NULL, CONSTRAINT `age_check` CHECK ((`age` > 19)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.04 sec) |
このテーブルに対して19歳以下のユーザを登録しようとするとエラーで失敗します。この時、どのCHECK制約に抵触したことを知らせるメッセージがクライアントに返されますので、エラーの原因も明確に分かります。
勿論、20歳以上のユーザは問題なく登録することができます。
|
1 2 3 4 5 |
mysql> INSERT INTO `users` VALUES (1,"Takahashi",18); ERROR 3819 (HY000): Check constraint 'age_check' is violated. mysql> INSERT INTO `users` VALUES (2,"Murata",20); Query OK, 1 row affected (0.03 sec) |
既に存在するテーブルは、ALTER TABLE … ADD CHECK/DROP CHECK文で制約の追加・削除ができます。この時、既にテーブルに制約に抵触するレコードが存在する場合、制約を設定できませんのでご注意ください。
ちなみに、CHECK制約を付与するDDLはオンラインDDLではないようです。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> ALTER TABLE `users` DROP CHECK `age_check`; Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE `users` ADD CONSTRAINT `age_check_2` CHECK (`age` > 20); ERROR 3819 (HY000): Check constraint 'age_check_2' is violated. mysql> UPDATE `users` SET `age` = 25 WHERE `id` =2; mysql> ALTER TABLE users ADD CONSTRAINT `age_check_2` CHECK (`age` > 20); Query OK, 1 row affected (0.17 sec) Records: 1 Duplicates: 0 Warnings: 0 |
さらに、CHECK制約では case 文や正規表現も使用可能で、多少複雑な条件を設定できます。以下は、商品の分類が食品（軽減税率の対象）の時には税率が8%、それ以外は10%にしようとした例を示しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> CREATE TABLE `cart` ( `item_id` INT PRIMARY KEY, `item_name` TEXT, `item_genre_id` TINYINT, `item_price` SMALLINT, `tax_rate` DECIMAL(3,2), CONSTRAINT `tax_check` CHECK (( CASE WHEN (`item_genre_id` < 11) THEN (CASE WHEN (`tax_rate` = 0.08) THEN 1 ELSE 0 END) ELSE (CASE WHEN (`tax_rate` = 0.1) THEN 1 ELSE 0 END) END ) = 1 )); ### id：10以下が軽減税率対象ジャンルと規定 mysql> CREATE TABLE `item_genres` (`genre_id` INT PRIMARY KEY, `genre_name` TEXT); mysql> INSERT INTO `item_genres` VALUES (1, "Meat"),(2, "Fish"),(3, "Vegetable"),(4, "Drink"),(10, "Other foods"); mysql> INSERT INTO `item_genres` VALUES (11, "Liquor"),(22, "Necessities"); ### 飲食品を cart に入れる時は、"tax_rate = 0.08"にしないとエラーになります mysql> INSERT INTO `cart` VALUES (100, "牛肉 200g", 1, 450, 0.08); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `cart` VALUES (101, "サンマ 3匹パック", 2, 600, 0.1); ERROR 3819 (HY000): Check constraint 'tax_check' is violated. |
ちなみに、上記のcartテーブルの定義は、以下のDDLと同義です。こちらの方が定義としてはよりスマートかもしれません。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> CREATE TABLE `cart` ( `item_id` INT PRIMARY KEY, `item_name` TEXT, `item_genre_id` TINYINT, `item_price` SMALLINT, `tax_rate` DECIMAL(3,2), CONSTRAINT `tax_check` CHECK ( (`item_genre_id` < 11 AND `tax_rate` = 0.08) OR (`item_genre_id` > 11 AND `tax_rate` = 0.1) )); |
CHECK制約の注意点
CHECK制約にもいくつか機能としての制限事項があります。
- auto_incrementを設定したカラムでは利用できません
- 他のテーブルのカラムを制約として参照できません
- 制約にストアドプロシージャ、ストアドファンクション、ユーザ定義関数を指定できません
- サブクエリは利用できません
- ON UPDATE, ON DELETEなど操作で使用される外部キーが貼られたカラムでは利用できません
- CHECKの対象となるのはINSERT, UPDATE, REPLACE, LOAD DATA, LOAD XML（IGNOREオプション含む）
レプリケーションとCHECK制約
最後にCHECK制約をレプリケーション環境で使用するケースを考えてみます。
例えば、レプリカにのみCHECK制約を設定した場合などはどうなるかというとソースでINSERTが成功されてもレプリカでCHECK制約によってデータは挿入できません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
### レプリカで以下を実行 mysql> ALTER TABLE `users` DROP CHECK `age_check_2`; mysql> ALTER TABLE `users` ADD CONSTRAINT `age_check_3` CHECK (`age` > 24); ### ソースで以下を実行 mysql> INSERT INTO `users` VALUES (3,"Kubota", 23); Query OK, 1 row affected (0.05 sec) ### レプリカにデータが挿入されていないことが分かります mysql> SELECT * FROM users; +------+--------+------+ | id | name | age | +------+--------+------+ | 2 | Murata | 25 | +------+--------+------+ 1 row in set (0.00 sec) |
レプリカでは以下のようにCHECK制約のエラーが発生します。現実にソース・レプリカで別々のCHECK制約を設定するケースはないと思いますが、ご注意ください。
|
1 |
2020-10-12T08:54:08.407343Z 5 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Could not execute Write_rows event on table test.users; Check constraint 'age_check_3' is violated., Error_code: 3819; handler error No Error!; the event's master log mysql-bin.000005, end_log_pos 7942, Error_code: MY-003819 |
まとめ
以上、MySQLの新機能であるCHECK制約について説明しました。
今後、既にCHECK制約を利用している他の商用データベースからMySQLに移行する際も少し楽になるかもしれません。さらなる機能強化などにも注目していきたいと思います。
参考サイト
How to Use CHECK Constraint in MySQL 8
