技術めも

MariaDB10.2で導入されたCHECK CONSTRAINTについて挙動を確認してみました。

使用したバージョンは、10.3.8です。

まず、create table時に一緒に制約を入れてみる。

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nickname` varchar(32) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `reg_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `check_status` CHECK (`status` between 1 and 10)
)

これで、statusカラムは1以上10以下しか入らないはずで、以下のSQLを流してみる。

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u1', 1, now());
-> OK

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u2', 10, now());
-> OK

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u3', 0, now());
ERROR 4025 (23000): CONSTRAINT `check_status` failed for `test`.`users` 

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u4', 11, now()); 
ERROR 4025 (23000): CONSTRAINT `check_status` failed for `test`.`users` 

見事に、0と11が弾かれている。
もちろん、0と11を登録可能なようにするためには、一度制約を解除する必要がある。

次に、ALTERのときの動きを見てみる。

前回作って800万件入っている以下のテーブルを利用する。

 
create table user ( 
  id integer not null auto_increment, 
  name varchar(64), 
  money_total_virtual integer as (money_free + money_paid) virtual
  money_free integer, 
  money_paid integer, 
  primary key (id) 
); 

・通常カラムへの制約

MariaDB [test]> alter table user add constraint check_money_free check (money_free <= 100);
Query OK, 8388608 rows affected (59.516 sec)           
Records: 8388608  Duplicates: 0  Warnings: 0

・virtualカラムへの制約

MariaDB [test]> alter table user add constraint check_money_total_virtual check (money_total_virtual <= 200);
Query OK, 8388608 rows affected (1 min 0.316 sec)      
Records: 8388608  Duplicates: 0  Warnings: 0

やはり、大量のレコードがある状態では、制約を掛けるのも時間がかかることがわかる。

最後にCHECK制約の仕様をまとめておきます。

• すでに入っているデータが制約違反の場合、制約自体が掛からない
• 制約の発動はINSERT/UPDATE時
• primary keyに対しても制約は掛けられる
• auto_incrementに対しては制約は掛けることができない

合わせて公式のマニュアルも参照ください。

https://mariadb.com/kb/en/library/constraint/

以上