MySQL은 참조 무결성(referential integrity)을 위해 외래 키(Foreign Key)를 지원하지만, CHECK 제약 조건(CHECK Constraint)은 공식적으로 지원하지 않습니다. 다행히 트리거(TRIGGER)를 활용하면 CHECK 제약 조건과 동일한 효과를 내는 기능을 직접 구현할 수 있습니다.
예제로 이해하기
'car'라는 테이블이 있고, 자동차 등록 번호가 다음과 같은 고정 형식을 따라야 한다고 가정해 보겠습니다.
- 알파벳 2글자
- 대시(-) 1개
- 숫자 3자리
- 대시(-) 1개
- 알파벳 2글자
mysql> Create table car (number char(9));
Query OK, 0 rows affected (0.32 sec)
mysql> Insert into car values('AB-235-YZ');
Query OK, 1 row affected (0.10 sec)
위 값 'AB-235-YZ'는 올바른 형식입니다. 그렇다면 다음 쿼리처럼 잘못된 값을 삽입하면 어떻게 될까요?
mysql> insert into car values('AB-2X5-YZ');
Query OK, 1 row affected (0.04 sec)'AB-2X5-YZ'는 숫자가 들어가야 할 자리에 문자 'X'가 포함되어 있어 정해진 형식에 어긋나는 잘못된 값입니다. 만약 MySQL이 CHECK 제약 조건을 지원했다면 이러한 삽입은 차단되었을 것입니다.
BEFORE INSERT 트리거로 CHECK 제약 조건 에뮬레이트하기
잘못된 데이터의 삽입을 방지하기 위해 BEFORE INSERT 트리거를 생성할 수 있습니다. 이 트리거는 RLIKE(정규 표현식)로 새로 삽입되는 값(new.number)이 지정된 패턴과 일치하는지 검사하고, 일치하지 않으면 SIGNAL 문으로 오류를 발생시켜 삽입 작업을 중단시킵니다.
mysql> delimiter //
mysql> create trigger car_insert_value before insert 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.15 sec)
mysql> Delimiter ;
mysql> Delete from car;
Query OK, 2 rows affected (0.06 sec)
테스트를 위해 기존 데이터를 모두 삭제했습니다. 이제 잘못된 번호를 삽입하려고 하면 앞서 만든 트리거가 이를 차단하고 다음과 같은 오류를 발생시킵니다.
mysql> insert into car values('AB-2X5-YZ');
ERROR 1644 (45000): Not a Valid Number반면, 형식에 맞는 유효한 값은 정상적으로 삽입됩니다.
mysql> insert into car values('AB-235-YZ');
Query OK, 1 row affected (0.04 sec)정리
이처럼 BEFORE INSERT 트리거와 RLIKE 정규 표현식 검사, SIGNAL 오류 발생을 조합하면 MySQL에서도 CHECK 제약 조건과 같은 데이터 유효성 검사를 구현할 수 있습니다. 이 방법은 INSERT 시점에 잘못된 데이터가 테이블에 저장되는 것을 사전에 차단해 주므로, 데이터 무결성을 유지하는 데 매우 유용합니다.