MySQL GENERATED COLUMN으로 CHECK 제약 조건 에뮬레이트하기
MySQL은 참조 무결성(referential integrity)을 보장하기 위한 외래 키(Foreign Key)는 지원하지만, 데이터 유효성 검사용 CHECK 제약 조건은 지원하지 않습니다. 하지만 트리거(Trigger) 또는 생성된 컬럼(GENERATED COLUMN)을 활용하면 CHECK 제약 조건과 동일한 효과를 우회적으로 구현할 수 있습니다. 아래 예시를 통해 구체적인 방법을 살펴보겠습니다.
예시: 차량 등록번호 테이블
차량 등록번호를 저장하는 'car' 테이블이 있다고 가정해 보겠습니다. 등록번호는 다음과 같은 고정된 형식을 따라야 합니다.
알파벳 2자리 – 대시(-) – 숫자 3자리 – 대시(-) – 알파벳 2자리
mysql> Create table car2 (number char(9));
Query OK, 0 rows affected (0.32 sec)
mysql> Insert into car2 values('AB-235-YZ');
Query OK, 1 row affected (0.10 sec)
위 값은 형식에 맞는 올바른 값이므로 정상적으로 저장됩니다. 그렇다면 다음 쿼리에서 입력하려는 값은 어떨까요?
mysql> insert into car2 values('AB-2X5-YZ');
Query OK, 1 row affected (0.04 sec)위 값은 숫자 사이에 문자가 섞여 있어 고정된 형식에 어긋나는 잘못된 값입니다. 하지만 별도의 검증 장치가 없기 때문에 오류 없이 그대로 저장되어 버립니다.
GENERATED COLUMN을 이용한 CHECK 제약 조건 구현
INSERT와 UPDATE 시 값의 유효성을 자동으로 검사하려면, 정규 표현식 검사를 수행하는 가상 컬럼(VIRTUAL GENERATED COLUMN)을 NOT NULL 속성으로 추가하면 됩니다.
mysql> Alter table car2 add column number_validate char(0) as (case when number
rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
-> then '' end)
-> virtual not null;
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> Describe car2;
+-----------------+---------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------------------+
| number | char(9) | YES | | NULL | |
| number_validate | char(0) | NO | | NULL | VIRTUAL GENERATED |
+-----------------+---------+------+-----+---------+-------------------+
2 rows in set (0.00 sec)
mysql> Insert into car2 (number) values('AB-235-YZ');
Query OK, 1 row affected (0.09 sec)
동작 원리: 새로 추가된 number_validate 컬럼은 number 값이 정규 표현식 패턴에 부합하면 빈 문자열('')을, 부합하지 않으면 NULL을 반환합니다. 이 컬럼에는 NOT NULL 제약이 걸려 있으므로, 형식에 맞지 않는 값이 들어오면 NULL 반환으로 인해 오류가 발생하고 삽입·수정이 거부됩니다.
위 쿼리에서 유효한 번호는 정상적으로 삽입되었습니다. 반면 아래처럼 잘못된 형식의 값을 삽입하거나 수정하려고 하면 오류가 발생합니다.
mysql> Insert into car2 (number) values('AB-2X5-YZ');
ERROR 1048 (23000): Column 'number_validate' cannot be null
mysql> Update car2 set number='AB-2X5-YZ';
ERROR 1048 (23000): Column 'number_validate' cannot be null참고 사항
MySQL 8.0.16 버전부터는 CHECK 제약 조건이 공식적으로 지원되므로, 최신 버전 환경이라면 이러한 우회 기법 대신 네이티브 CHECK 구문을 사용하는 것이 좋습니다. 또한 VIRTUAL 방식의 생성 컬럼은 실제 데이터를 저장하지 않기 때문에 추가적인 디스크 공간을 소모하지 않는다는 장점이 있습니다.