MySql Unique index 사용시 soft Delete 설정

 

    - null값을 가지는 칼럼과 unique 제한 설정하면 unuque가 적용 안되는 문제 해결 방법

* unique 설정

ALTER TABLE articles

    ADD deleted_at TIMESTAMP NULL DEFAULT NULL,

    DROP INDEX name,

    ADD CONSTRAINT UNIQUE (name, deleted_at);

 

    - 문제 발생

null 값을 가지는 칼럼(deleted_at)과 함께 unique 설정을 하면 unique가 작동안함

* 해결 방법

 

ALTER TABLE articles

    ADD deleted int (1) GENERATED ALWAYS AS (IF(deleted_at IS NULL,  0, NULL)) VIRTUAL;

 

    - 버츄얼 칼럼(Virtual Column, Generated ) 생성

 

    - deleted_at이 NULL이면 0, 값이 있으면(삭제시) NULL 로 자동 설정되는 deleted칼럼 생성

 

        - 삭제시 deleted를 NULL로 설정하면 soft delete된것은 unique 제한을 받지 않음

            - 여러개 soft delete 자료를 생성 가능

//----------------------------------

// 참고

 

Dealing with MySQL nulls and unique constraint

https://medium.com/@aleksandrasays/dealing-with-mysql-nulls-and-unique-constraint-d260f6b40e60