[MySQL] 부분적 Unique 제약 만들기
PostgreSQL이나 MongoDB 같은 몇몇 DB들은 인덱스를 조건적으로 설정하는 훌륭한 Partial Index 기능을 제공한다. 이건 부분적인 Unique 제약을 거는 것에 굉장히 유용하다.
예를 들면 deleted_at이 null인 것에 대해서만 유일성을 건다거나 하는 상황 말이다.
하지만 MySQL은 여러모로.. 후진적인 부분이 많은 DB라서 이런건 없다.
다만 약간 에둘러서 동작을 흉내낼 수는 있다.
위에서 언급했던 "deleted_all이 null 인 것에만" 인덱스 조건을 걸고 싶다면 expression index라는 조금 다른 대안을 응용해볼 수 있다.
만약 이런 테이블이 있다면
CREATE TABLE users (
id integer PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email text,
deleted_at datetime
);보통 이메일을 유일키로 걸고 싶을 것이다.
그런데 삭제된 user는 유일키 조건에서 제외하고 싶다면?
CREATE UNIQUE INDEX idx_email ON users
(email, (CASE WHEN deleted_at IS NULL THEN true ELSE NULL END));좀 복잡해보이는데, 이런 식으로 걸면 된다.
두번째 인덱스 조건에다가는 특이하게도 표현식을 걸었는데, deleted_at가 null이면 true를 반환하고, 아니면 null을 반환하도록 했다.
알다시피 RDB들에서 유일키 항목 값에 null이 포함되면 그 유일키 제약은 반쯤 망가진다. 제약을 걸지 못하는 것이다. 그래서 저러면 deleted_at이 NULL인 것만 non-null 값이 들어가니 유일키가 동작한다.
한번 돌려보자.
먼저 테이블과 인덱스를 만들고
적당히 데이터를 던졌다.
-- 데이터 초기화
INSERT INTO users (name, email)
VALUES
('Alice', 'alice.foo@gmail.com'),
('Bob', 'bob.bar@gmail.com');
-- 실패 (유일키 제약에 걸림)
INSERT INTO users (name, email)
VALUES
('Bob', 'bob.bar@gmail.com');
-- 성공 (유일키 제약에 안걸림)
INSERT INTO users (name, email, deleted_at)
VALUES
('Bob', 'bob.bar@gmail.com', now());
그럼 기대한 대로 잘 동작한다.
partial index처럼 실제로 인덱스 크기를 줄이지도 못하고 사용법도 복잡한 편이지만, 어떻게 할 수는 있다.