MySQL 조건부 유니크 제약 조건 설정하기

서비스 개발 중 “ 중복된 인증코드 번호는 존재할수 없다 ” 문제에대해 어떻게 해결했는지 포스팅해보겠습니다.

개요

회원 가입 시, 학생(ROLE_STUDENT) 계정에는 대문자 3개 + 숫자 4개로 이루어진 인증코드번호를 부여해야 합니다. 이때 학생 계정끼리만 인증코드번호가 중복되지 않도록 제약을 설정하고 싶다면 어떻게 해야 할까요?

PostgreSQL에서는 조건부 유니크 인덱스를 사용할 수 있지만, MySQL은 조건부 유니크 제약을 직접 지원하지 않습니다. 이 글에서는 이 문제를 어떻게 해결했는지, 실제 사례를 통해 쉽게 설명해보겠습니다.

문제 상황

처음에는 인증코드번호 컬럼 자체에 유니크 제약을 설정하려고 했습니다.

ALTER TABLE USER ADD CONSTRAINT UNIQUE_인증코드번호 UNIQUE (인증코드번호);

하지만 이 방법에는 치명적인 문제가 있었습니다:

1. 학생뿐만 아니라 부모(ROLE_PARENT) 계정에도 유니크 제약이 적용됩니다.
2. 부모 계정이 실수로 인증코드번호를 가지게 되면 제약 조건에 위배됩니다.
3. 향후 정책 변경 시 유연성이 부족합니다.
4. 결론적으로, 이 방법은 우리가 원하는 조건부 유니크를 만족하지 못했습니다.

MySQL의 UNIQUE와 NULL 처리 방식

MySQL의 UNIQUE 제약은 NULL 값을 중복으로 간주하지 않습니다.

• 부모 계정의 인증코드번호가 모두 NULL인 경우에는 유니크 제약에 걸리지 않습니다.
• 그러나 부모 계정에도 실수로 인증코드번호가 부여되거나, 중복된 값이 들어간다면 제약 조건에 위배될 수 있습니다.
• 이로 인해 단순한 UNIQUE 제약으로는 요구 사항을 만족할 수 없었습니다.

해결 방법: 가상 컬럼 + 유니크 인덱스

MySQL에서는 가상 컬럼(Generated Column) 을 활용하여 조건부 유니크 제약과 유사한 효과를 얻을 수 있습니다.

1. 가상 컬럼 생성

ALTER TABLE USER
ADD COLUMN STUDENT_인증코드번호 VARCHAR(255)
GENERATED ALWAYS AS (
    IF(ROLE = 'ROLE_STUDENT', 인증코드번호, NULL)
) STORED;

• ROLE이 ROLE_STUDENT일 경우에만 인증코드번호 값을 복사합니다.
• ROLE_PARENT인 경우에는 NULL로 저장됩니다.

2. 유니크 인덱스 설정

CREATE UNIQUE INDEX UNIQUE_인증코드번호_FOR_STUDENT
ON USER (STUDENT_인증코드번호);

• NULL 값은 유니크 제약의 대상이 아니므로, 부모 계정은 제약 조건에 영향을 받지 않습니다.
• 학생 계정끼리만 인증코드번호의 중복 여부를 검사하게 됩니다.

결과

• 학생 계정에만 유니크 제약이 적용됩니다.
• 부모 계정은 영향을 받지 않으므로 정책 변경에도 유연하게 대응할 수 있습니다.
• 복잡한 로직 없이 DB 레벨에서 문제를 해결했습니다.