MySQLにも、MySQL8.0.16からCheck制約が実装されているので、改めてMySQLとPostgreSQLにおけるCheck制約の基本動作を確認してみます。
In case of MySQL
以下の様にMySQLでもCheck制約を実装する事が出来る様になっています。
mysql> CREATE TABLE T_8030_CHK(
-> id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> emp_id int NOT NULL,
-> salary DECIMAL(10,2) NOT NULL CONSTRAINT CHECK (salary > 0)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into T_8030_CHK(emp_id,salary) values(1,1000.00);
Query OK, 1 row affected (0.08 sec)
mysql> insert into T_8030_CHK(emp_id,salary) values(2,1200.00);
Query OK, 1 row affected (0.01 sec)
mysql> insert into T_8030_CHK(emp_id,salary) values(3,1200);
Query OK, 1 row affected (0.01 sec)
mysql> select * from T_8030_CHK;
+----+--------+---------+
| id | emp_id | salary |
+----+--------+---------+
| 1 | 1 | 1000.00 |
| 2 | 2 | 1200.00 |
| 3 | 3 | 1200.00 |
+----+--------+---------+
3 rows in set (0.00 sec)
mysql> insert into T_8030_CHK(emp_id,salary) values(4,0);
ERROR 3819 (HY000): Check constraint 'T_8030_CHK_chk_1' is violated.
mysql> select * from T_8030_CHK;
+----+--------+---------+
| id | emp_id | salary |
+----+--------+---------+
| 1 | 1 | 1000.00 |
| 2 | 2 | 1200.00 |
| 3 | 3 | 1200.00 |
+----+--------+---------+
3 rows in set (0.00 sec)
mysql> insert into T_8030_CHK(emp_id,salary) values(4,10);
Query OK, 1 row affected (0.01 sec)
mysql> insert into T_8030_CHK(emp_id,salary) values(4,0.00);
ERROR 3819 (HY000): Check constraint 'T_8030_CHK_chk_1' is violated.
mysql> insert into T_8030_CHK(emp_id,salary) values(4,-1);
ERROR 3819 (HY000): Check constraint 'T_8030_CHK_chk_1' is violated.
mysql> select * from T_8030_CHK;
+----+--------+---------+
| id | emp_id | salary |
+----+--------+---------+
| 1 | 1 | 1000.00 |
| 2 | 2 | 1200.00 |
| 3 | 3 | 1200.00 |
| 4 | 4 | 10.00 |
+----+--------+---------+
4 rows in set (0.00 sec)
設定確認：CHECK制約は、以下の様にinformation_schemaから確認する事が出来ます。
mysql> show tables from information_Schema like '%CONSTRAINTS';
+---------------------------------------------+
| Tables_in_information_schema (%CONSTRAINTS) |
+---------------------------------------------+
| CHECK_CONSTRAINTS |
| REFERENTIAL_CONSTRAINTS |
| TABLE_CONSTRAINTS |
+---------------------------------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM information_schema.check_constraints;
+--------------------+-------------------+------------------+----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE |
+--------------------+-------------------+------------------+----------------+
| def | CONFIRM | T_8030_CHK_chk_1 | (`salary` > 0) |
+--------------------+-------------------+------------------+----------------+
1 row in set (0.02 sec)
エラー確認：発生しているかは、以下のPerformance_Schemaを確認する事でも確認出来る。
mysql> show tables from performance_schema like 'events_errors_summary_by_%';
mysql> select * from performance_schema.events_errors_summary_by_account_by_error where SUM_ERROR_RAISED <> 0;
CHECK制約の定義変更
mysql> select * from T_8030_CHK;
+----+--------+---------+
| id | emp_id | salary |
+----+--------+---------+
| 1 | 1 | 1000.00 |
| 2 | 2 | 1200.00 |
| 3 | 3 | 1200.00 |
| 4 | 4 | 10.00 |
+----+--------+---------+
4 rows in set (0.00 sec)
mysql> insert into T_8030_CHK(emp_id,salary) values(5,0);
ERROR 3819 (HY000): Check constraint 'T_8030_CHK_chk_1' is violated.
mysql> show create table T_8030_CHK\G
*************************** 1. row ***************************
Table: T_8030_CHK
Create Table: CREATE TABLE `T_8030_CHK` (
`id` int NOT NULL AUTO_INCREMENT,
`emp_id` int NOT NULL,
`salary` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `T_8030_CHK_chk_1` CHECK ((`salary` > 0))
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
mysql> alter table T_8030_CHK DROP check `T_8030_CHK_chk_1`;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table T_8030_CHK ADD CHECK (salary >= 0);
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into T_8030_CHK(emp_id,salary) values(5,0);
Query OK, 1 row affected (0.01 sec)
mysql> show create table T_8030_CHK\G
*************************** 1. row ***************************
Table: T_8030_CHK
Create Table: CREATE TABLE `T_8030_CHK` (
`id` int NOT NULL AUTO_INCREMENT,
`emp_id` int NOT NULL,
`salary` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `T_8030_CHK_chk_1` CHECK ((`salary` >= 0))
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
mysql>定義変更時のロック：yokuさんのブログに書かれている様にDROPはオンラインで可能だが、ADDの時はCOPY処理になるので参照のみしか出来なくなるので要留意。
mysql> show create table T_8030_CHK\G
*************************** 1. row ***************************
Table: T_8030_CHK
Create Table: CREATE TABLE `T_8030_CHK` (
`id` int NOT NULL AUTO_INCREMENT,
`emp_id` int NOT NULL,
`salary` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `T_8030_CHK_chk_1` CHECK ((`salary` >= 0))
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
mysql> alter table T_8030_CHK DROP check T_8030_CHK_chk_1 ,ALGORITHM= INPLACE,LOCK= NONE;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table T_8030_CHK\G
*************************** 1. row ***************************
Table: T_8030_CHK
Create Table: CREATE TABLE `T_8030_CHK` (
`id` int NOT NULL AUTO_INCREMENT,
`emp_id` int NOT NULL,
`salary` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
mysql> alter table T_8030_CHK ADD CHECK (salary >= 0) ,ALGORITHM= INPLACE,LOCK= NONE;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
mysql> alter table T_8030_CHK ADD CHECK (salary >= 0) ,ALGORITHM = COPY,LOCK = NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
mysql> alter table T_8030_CHK ADD CHECK (salary >= 0) ,ALGORITHM = COPY,LOCK = SHARED;
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> show create table T_8030_CHK\G
*************************** 1. row ***************************
Table: T_8030_CHK
Create Table: CREATE TABLE `T_8030_CHK` (
`id` int NOT NULL AUTO_INCREMENT,
`emp_id` int NOT NULL,
`salary` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `T_8030_CHK_chk_1` CHECK ((`salary` >= 0))
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
mysql>
制約に違反するDMLは勿論エラーになります
mysql> show create table T_8030_CHK\G
*************************** 1. row ***************************
Table: T_8030_CHK
Create Table: CREATE TABLE `T_8030_CHK` (
`id` int NOT NULL AUTO_INCREMENT,
`emp_id` int NOT NULL,
`salary` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `T_8030_CHK_chk_1` CHECK ((`salary` >= 0))
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)
mysql> select * from T_8030_CHK;
+----+--------+---------+
| id | emp_id | salary |
+----+--------+---------+
| 1 | 1 | 1000.00 |
| 2 | 2 | 1200.00 |
| 3 | 3 | 1200.00 |
| 4 | 4 | 10.00 |
| 6 | 5 | 0.00 |
+----+--------+---------+
5 rows in set (0.00 sec)
mysql> update T_8030_CHK set salary = 0.01 where id = 6;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from T_8030_CHK;
+----+--------+---------+
| id | emp_id | salary |
+----+--------+---------+
| 1 | 1 | 1000.00 |
| 2 | 2 | 1200.00 |
| 3 | 3 | 1200.00 |
| 4 | 4 | 10.00 |
| 6 | 5 | 0.01 |
+----+--------+---------+
5 rows in set (0.00 sec)
mysql> update T_8030_CHK set salary = -1 where id = 6;
ERROR 3819 (HY000): Check constraint 'T_8030_CHK_chk_1' is violated.
mysql>留意
AUTO_INCREMENT属性を持つカラムおよび他のテーブルのカラムを除き、生成されていないカラムおよび生成されたカラムは許可。
リテラル、決定的組込み関数および演算子を使用できます。 関数は、テーブル内の同じデータが指定された場合、接続ユーザーとは関係なく、複数の起動で同じ結果が生成される場合は決定論的です。 非決定的で、この定義に失敗する関数の例: CONNECTION_ID(), CURRENT_USER(), NOW()。
ストアドファンクションおよびユーザー定義関数は使用できません。
ストアドプロシージャおよびストアドファンクションのパラメータは使用できません。
変数 (システム変数、ユーザー定義変数およびストアドプログラムローカル変数) は使用できません。
サブクエリーは許可されません。
他のテーブルのカラムを制約として参照する事はできません
13.1.20.6 CHECK 制約
制約式の評価では、評価時に有効な SQL モードが使用されます。 式のいずれかのコンポーネントが SQL モードに依存している場合、すべての使用中に SQL モードが同じでないかぎり、テーブルの使用方法によって結果が異なることがあります。
備考：INFORMATION_SCHEMA.CHECK_CONSTRAINTS テーブルは、テーブルで定義された CHECK 制約に関する情報を確認する事が可能です。(Since: MySQL8.0.16)
mysql> CREATE TABLE T_CHECK (
-> `c1` int(11) DEFAULT NULL,
-> `c2` int(11) DEFAULT NULL,
-> `c3` int(11) DEFAULT NULL,
-> CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
-> CONSTRAINT `c2_positive` CHECK ((`c2` > 0)),
-> CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)),
-> CONSTRAINT `t1_chk_2` CHECK ((`c1` > 10)),
-> CONSTRAINT `t1_chk_3` CHECK ((`c3` < 100)),
-> CONSTRAINT `t1_chk_4` CHECK ((`c1` > `c3`))
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected, 3 warnings (0.05 sec)
mysql> select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE |
+--------------------+-------------------+-----------------+----------------+
| def | POC | c1_nonzero | (`c1` <> 0) |
| def | POC | c2_positive | (`c2` > 0) |
| def | POC | t1_chk_1 | (`c1` <> `c2`) |
| def | POC | t1_chk_2 | (`c1` > 10) |
| def | POC | t1_chk_3 | (`c3` < 100) |
| def | POC | t1_chk_4 | (`c1` > `c3`) |
+--------------------+-------------------+-----------------+----------------+
6 rows in set (0.01 sec)
mysql>
In Case of PostgreSQL
PostgreSQLには、
confirm=# CREATE SEQUENCE check_pg13_5chk START 1;
CREATE SEQUENCE
confirm=# create table T_PG13_5CHK (
confirm(# id integer DEFAULT nextval('check_pg13_5chk'),
confirm(# emp_id integer,
confirm(# salary integer check(salary > 0)
confirm(# );
CREATE TABLE
confirm=# \d+ T_PG13_5CHK;
Table "public.t_pg13_5chk"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+--------------------------------------+---------+--------------+
id | integer | | | nextval('check_pg13_5chk'::regclass) | plain | |
emp_id | integer | | | | plain | |
salary | integer | | | | plain | |
Check constraints:
"t_pg13_5chk_salary_check" CHECK (salary > 0)
Access method: heap
confirm=#
基本的な挙動：CHECK制約の挙動は他のRDBMS同様です。
confirm=# insert into T_PG13_5CHK(emp_id,salary) values(1,1000);
INSERT 0 1
confirm=# insert into T_PG13_5CHK(emp_id,salary) values(1,2000);
INSERT 0 1
confirm=# insert into T_PG13_5CHK(emp_id,salary) values(1,0);
ERROR: new row for relation "t_pg13_5chk" violates check constraint "t_pg13_5chk_salary_check"
DETAIL: Failing row contains (3, 1, 0).
confirm=# select * from T_PG13_5CHK;
id | emp_id | salary
----+--------+--------
1 | 1 | 1000
2 | 1 | 2000
(2 rows)
confirm=#CHECK制約を含まないテーブルを作成
confirm=# create table T_PG13_5CHK2 (
confirm(# emp_id integer,
confirm(# join_date timestamp,
confirm(# calendar timestamp default CURRENT_TIMESTAMP
confirm(# );
CREATE TABLECHECK制約の追加
confirm=# ALTER table T_PG13_5CHK2 ADD CONSTRAINT check_join_date check (join_date >= calendar);
ALTER TABLE
CHECK制約の挙動確認：加入日が本日より未来の日付であるかどうか？
confirm=# insert into T_PG13_5CHK2(emp_id,join_date) values(1,'2022-10-13');
ERROR: new row for relation "t_pg13_5chk2" violates check constraint "check_join_date"
DETAIL: Failing row contains (1, 2022-10-13 00:00:00, 2022-10-14 15:58:08.724939).
confirm=# insert into T_PG13_5CHK2(emp_id,join_date) values(1,'2022-10-14');
ERROR: new row for relation "t_pg13_5chk2" violates check constraint "check_join_date"
DETAIL: Failing row contains (1, 2022-10-14 00:00:00, 2022-10-14 15:58:12.803171).
confirm=# insert into T_PG13_5CHK2(emp_id,join_date) values(1,'2022-10-15');
INSERT 0 1
confirm=# select * from T_PG13_5CHK2;
emp_id | join_date | calendar
--------+---------------------+----------------------------
1 | 2022-10-15 00:00:00 | 2022-10-14 15:58:16.097406
(1 row)
confirm=# select *,now() from T_PG13_5CHK2;
emp_id | join_date | calendar | now
--------+---------------------+----------------------------+-------------------------------
1 | 2022-10-15 00:00:00 | 2022-10-14 15:58:16.097406 | 2022-10-14 15:58:33.676086+09
(1 row)
confirm=#
半角英数のみを許容する列になる様に制限した場合
create table T_PG13_5CHK3 (
emp_id integer,
note VARCHAR(100) CHECK (note::text ~ '^[a-zA-Z0-9]+$'::text),
calendar timestamp default CURRENT_TIMESTAMP
);
制約に違反するDMLは勿論エラーになります
confirm=# \d t_pg13_5chk;
Table "public.t_pg13_5chk"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+--------------------------------------
id | integer | | | nextval('check_pg13_5chk'::regclass)
emp_id | integer | | |
salary | integer | | |
Check constraints:
"t_pg13_5chk_salary_check" CHECK (salary > 0)
confirm=# update t_pg13_5chk set salary = 1500 where id = 2;
UPDATE 1
confirm=# select * from t_pg13_5chk;
id | emp_id | salary
----+--------+--------
1 | 1 | 1000
2 | 1 | 1500
(2 rows)
confirm=# update t_pg13_5chk set salary = 0 where id = 2;
ERROR: new row for relation "t_pg13_5chk" violates check constraint "t_pg13_5chk_salary_check"
DETAIL: Failing row contains (2, 1, 0).
confirm=#検査制約は最も汎用的な制約の種類です。 これを使用して、特定の列の値が論理値の式を満たす（真の値）ように指定できます。 例えば、製品価格を必ず正数にするには以下のようにします。
検査制約では、検査式が真またはNULL値と評価された場合に、条件が満たされることに注意して下さい。 ほとんどの式は、演算項目に一つでもNULLがあればNULLと評価されるので、検査制約では制約対象の列にNULL値が入るのを防げません。 列がNULL値を含まないようにするために、次節で説明する非NULL制約を使用することができます。
5.4.1. 検査制約