MySQL은 참조 무결성을 위해 외래 키(Foreign Key)를 지원하지만, 아쉽게도 CHECK 제약 조건(CHECK CONSTRAINT)은 지원하지 않습니다. 하지만 다행히 트리거(Trigger)를 활용하면 CHECK 제약 조건과 동일한 기능을 구현할 수 있습니다. 아래 예제를 통해 그 방법을 자세히 알아보겠습니다.
예제: 차량 등록번호 형식 검증
'car'라는 테이블이 있다고 가정해 보겠습니다. 이 테이블의 등록 번호는 영문 두 글자 - 숫자 세 자리 - 영문 두 글자와 같은 고정된 형식을 가져야 합니다.
mysql> Create table car (number char(9)); Query OK, 0 rows affected (0.32 sec)
값 업데이트 시 CHECK 제약 조건을 에뮬레이트하는 BEFORE UPDATE 트리거 생성
먼저, 잘못된 형식의 값으로 테이블을 업데이트하려고 시도하면 MySQL은 이를 아무런 제한 없이 허용합니다.
mysql> update car set number='AB-2X5-YZ'; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0
위 쿼리처럼 규칙에 어긋난 값('AB-2X5-YZ'는 두 번째 부분에 문자가 포함되어 형식에 맞지 않음)이 그대로 'car' 테이블에 저장됩니다. 이러한 문제를 방지하려면 BEFORE UPDATE 트리거를 생성하여 잘못된 데이터가 업데이트되기 전에 차단할 수 있습니다.
mysql> delimiter //
mysql> create trigger car_update_value before update on car
-> for each row
-> begin
-> if new.number not rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
-> then
-> signal sqlstate '45000' set message_text = 'Not a valid number';
-> end if;
-> end //
Query OK, 0 rows affected (0.11 sec)
mysql> Delimiter ;
mysql> update car set number='AB-2X5-YZ';
ERROR 1644 (45000): Not a valid number위 코드의 핵심 동작 원리는 다음과 같습니다.
- RLIKE 연산자: 정규 표현식
^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$를 사용하여 새로 입력되는 값(new.number)이 올바른 형식인지 검사합니다. - SIGNAL 문: 형식이 올바르지 않으면 SQLSTATE '45000'(사용자 정의 오류)과 함께 'Not a valid number'라는 오류 메시지를 발생시켜 업데이트 작업을 중단시킵니다.
트리거가 적용된 후 동일한 잘못된 값으로 업데이트를 시도하면 ERROR 1644 (45000): Not a valid number 오류가 발생하며, 잘못된 데이터가 테이블에 저장되지 않습니다. 이처럼 BEFORE UPDATE 트리거와 SIGNAL 문을 조합하면 MySQL에서 CHECK 제약 조건의 역할을 효과적으로 대체할 수 있습니다.