[MySQL] Soft delete와 unique constraint

Soft delete인데 unique가 걸려있다...

제목을 어떻게 지어야할지 몰라서 저렇게 지었지만 회사 API 작성 중 회원가입 파트에서 막힌 부분이 있었다.
바로 탈퇴한 아이디인데 그 아이디로 다시 회원가입을 할 경우, 로직상 DB에서 레코드(row)를 삭제하는 것이 아닌, soft delete(논리 삭제)를 할 경우 unique해야 할 아이디가 이미 테이블에 존재한다며 에러를 내뿜었다.

이 부분에 대해 고민을 한 것이 2가지였다.

1. unique constraint를 없애고 비지니스 로직으로 논리 삭제된 아이디를 제외하고 중복된 아이디인지 검사
2. nickname과 deleted_at 컬럼 두개를 다중 컬럼 unique를 건다.

unique 삭제?

unique 조건을 삭제한다면 문제가 분명히 생길 것이다. 로직상으로 중복을 막았지만, 어떠한 문제가 발생해서 DB에 직접 데이터를 넣어야 할 경우, 혹은 다른 이유(엣지 케이스)로 중복 값이 들어갈 수 있다. 그래서 이 방법은 생각만 하고 바로 접었다.

다중 컬럼 unique

이 방법이 그나마 낫겠다고 생각을 하여 해당 방법으로 고민하던 중

• abc123이라는 아이디를 생성하고 탈퇴 -> deleted_at에 삭제한 시간 자동 기입
• abc123이라는 아이디를 다시 생성 -> 아이디는 중복이지만 deleted_at 컬럼이 달라지기 때문에 안됨

이렇게 생각했다. 그래서 방법을 찾던 중 이 방법과 비슷한 글을 찾게 되었다.

해결 방법

ALTER TABLE users
ADD not_archived BOOLEAN
GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) VIRTUAL;

not_achived라는 virtual column을 생성한다. 이 컬럼은 deleted_at 컬럼이 NULL이면 자동으로 1이 추가된다. NULL이 아니면(값이 삭제되면) 이 값이 NULL이 된다.

ALTER TABLE users
ADD CONSTRAINT UNIQUE (nickname, not_archived);

그 후 이 두 값을 unique로 묶어서 관리한다. 참고로 nickname에 기존에 걸려있던 constraint를 제거해야한다.

PS

추후에 VIRTUAL 이라는 SQL 문법에 대해 공부하여 출간하겠다.

Ref : https://gusiol.medium.com/soft-delete-and-unique-constraint-da94b41cff62