Computer >> 컴퓨터 >  >> 프로그래밍 >> MySQL

MySQL에서 트리거를 활용해 CHECK 제약 조건을 에뮬레이트하는 방법

MySQL은 참조 무결성을 위한 외래 키(Foreign Key)는 지원하지만, CHECK 제약 조건은 공식적으로 지원하지 않습니다. 하지만 다행히도 트리거(Trigger)를 활용하면 CHECK 제약 조건과 동일한 효과를 구현할 수 있습니다. 아래 예제를 통해 그 방법을 자세히 살펴보겠습니다.

예제 시나리오: 자동차 등록번호 형식 검증

다음과 같이 고정된 형식의 등록 번호를 저장하는 'car' 테이블이 있다고 가정해 보겠습니다. 형식 규칙은 영문자 2개 – 숫자 3개 – 영문자 2개(예: AB-235-YZ)입니다.

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은 기본적으로 이러한 삽입을 막아주지 않으므로, 트리거를 만들어 직접 검증 로직을 추가해야 합니다.

1단계: BEFORE INSERT 트리거 생성하기

잘못된 값의 삽입을 방지하기 위해 다음과 같이 BEFORE INSERT 트리거를 생성합니다.

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)

트리거의 작동 원리

이 트리거는 새로 삽입되는 값(NEW.number)이 정규 표현식 패턴 ^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$와 일치하지 않으면 SIGNAL SQLSTATE '45000'을 통해 에러를 발생시켜 삽입 작업을 중단시킵니다.

이제 잘못된 값을 삽입하려고 하면 앞서 생성한 트리거가 이를 차단하고 다음과 같은 오류를 반환합니다.

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)

2단계: BEFORE UPDATE 트리거 생성하기

INSERT 트리거만으로는 부족합니다. 기존 데이터를 수정할 때도 잘못된 값이 들어갈 수 있기 때문입니다. 실제로 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

위 쿼리는 '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

이제 UPDATE 문으로 잘못된 형식의 값을 수정하려고 하면 트리거가 이를 감지하고 에러를 발생시켜 데이터 무결성을 지켜줍니다.

정리

MySQL에서 CHECK 제약 조건을 대체하려면 다음 두 가지 트리거를 함께 사용하는 것이 좋습니다.

  • BEFORE INSERT 트리거: 새로운 데이터 삽입 시 형식 검증
  • BEFORE UPDATE 트리거: 기존 데이터 수정 시 형식 검증

두 트리거 모두 정규 표현식(RLIKE)으로 값의 유효성을 검사하고, 조건에 맞지 않으면 SIGNAL SQLSTATE '45000'으로 에러를 발생시키는 방식으로 동작합니다. 이렇게 하면 CHECK 제약 조건이 없는 환경에서도 데이터 무결성을 확실하게 보장할 수 있습니다.