MySQL Null 과 함께 Unique

특정 데이터가 삭제되지 않았을 때는 중복을 허용하지 않고, 삭제(논리)된 경우에는 중복이 가능하게 요청을 받았다.

 

예를 들어 email이라는 컬럼이 위와 같이 되어야 한다고 하자.

'test@test.com'이 이미 사용되고 있다. 그렇다면 더 이상 다른 사용자는 해당 이메일을 사용할 수 없다.

 

 

그러나 해당 로우가 삭제되었다면, 즉, deleted_at이 기록된 경우에는 더 이상 email은 존재하지 않는 것으로 간주되어 중복이 가능하도록 설정해야 한다.

 

 

그냥 deleted_at과 원하는 컬럼을 묶어서 UNIQUE로 지정하면 되지 않을까?

-- email과 deleted_at을 묶어서 유니크로 지정
ALTER TABLE users
ADD CONSTRAINT UK_email_deleted_at UNIQUE(email, deleted_at);

 

안타깝게도 MySQL에서는 NULL은 무시해 버리게 된다.

 

그럼 어떻게 해야 할까?

 

가상컬럼을 이용해 보자

-- deleted_at을 대신할 컬럼 생성
ALTER TABLE users
ADD not_archived BOOLEAN
GENERATED ALWAYS AS (IF(deleted_at IS NULL, TRUE, NULL)) VIRTUAL;

-- email과 함께 UNIQUE로 묶기
ALTER TABLE users
ADD CONSTRAINT UK_email_not_archived UNIQUE(email, not_archived);

 

쿼리 실행 중에 자동으로 deleted_at의 값을 기반으로 not_archived라는 컬럼을 임시로 생성하고 해당 컬럼을 가상컬럼으로 지정해 주었다.

그리고 해당 컬럼을 email과 함께 UNIQUE로 묶어 주었다.

 

그냥 쉽게 말해 deleted_at이라는 컬럼이 NULL일 때를 대신해서 계산될 컬럼을 하나 추가했다고 보면 된다.

 

그리고 다시 중복된 email 삽입을 시도해 보면 에러가 발생한다.

 

중복된 email을 논리삭제 후 다시 시도해보면 정상적으로 들어가는 것을 볼 수 있다.

 

가성컬럼을 deleted_at이 NULL이 아닐 때 NULL이 되게 한 이유?

두 개의 컬럼을 유니크로 묶을 경우 NULL을 무시하기 때문에 아래와 같이 삭제되었을 때 중복이 된다.

 

 

ps) 기존 deleted_at과 함께 지정한 유니크키 삭제를 잊지 말자

 

참고

Soft Delete and Unique Constraint