CHECK 제약에 대해서

MySQL 8.0.16 부터 새롭게 추가된 CHECK 제약애 대해서 알아보자.

CHECK 제약
테이블에 데이터를 등록 또는 갱신할 때에 조건을 만족하는 지 검증하는 기능이다.

예를 들어, UNSIGNED TINYINT로 정의된 컬럼에는 0~255 까지의 숫자를 등록할 수 있지만 CHECK 제약을 이용하여 0~10 까지의 숫자로 한정하는 것이 가능하다.

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.

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 제약은 테이블 또는 컬럼에 지정하는 게 가능하다.

컬럼 제약은 해당 컬럼만을 참조하여 정의하는 제약이고, 테이블 제약은 테이블의 컬럼들을 참조하여 정의하는 게 가능하다.

mysql> CREATE TABLE t1(
    ->   num1 INT CHECK (num1 > 1),
    ->   num2 INT CONSTRAINT num2_chk CHECK (num2 > 0),
    -> CHECK (num1 *2 < num2), # 제약 명칭을 지정하지 않으면 <table_name>_chk_<number>로 자동생성
    -> CONSTRAINT t1_chk CHECK (num1 <> 0)
    -> );
Query OK, 0 rows affected (0.01 sec)

작성한 CHECK 제약 확인
SHOW CREATE TABLE구문으로 확인하는 것이 가능하다.
또한 INFORMATION_SCHEMA.CHECK_CONSTRAINT 테이블에서도 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 제약위반이 발생한 경우는 performance_schema.events_errors_summary_by_* 계열 테이블의 ER_CHECK_CONSTRAINT_VIOLATED 에러 카운트가 증가한다.

CHECK 제약에 의해 에러가 발생하고 있는 지 확인해야 하는 경우는 해당 계측을 참조하면 좋을 거 같다.

CHECK 제약의 제한
이용하는 데 있어서 몇 가지의 제한이 있다. 아래와 같은 경우는 CHECK 제약을 이용할 수 없다.

• AUTO_INCREMENT를 지정한 컬럼
• 다른 테이블의 컬럼을 참조하여 CHECK 제약 정의
• 사용자 함수 또는 프로시저 사용
• 변수 사용
• 서브 쿼리 사용
• NOW(), CURRENT_USER() 와 같은 비결정성 함수 사용

보다 상세한 조건은 공식 문서 참조. 

https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

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.

# Refer
https://mysqlserverteam.com/mysql-8-0-16-introducing-check-constraint/