influence/ddl/006_fix_unique_constraint_fundamental.sql

-- DDL 006: UNIQUE 제약조건 근본적 수정
-- 생성일: 2024-12-20
-- 목적: 중복 키 오류 근본 해결을 위한 UNIQUE 제약조건 수정

-- 1. 기존 UNIQUE 제약조건 제거
ALTER TABLE `VENDOR_INFLUENCER_MAPPING` 
DROP INDEX `unique_vendor_influencer_status`;

-- 2. 새로운 UNIQUE 제약조건 추가 (IS_ACT='Y'인 레코드만 적용)
-- MySQL 8.0+에서는 함수 기반 인덱스 지원
-- 아래 방식은 MySQL 5.7에서도 호환 가능한 방식

-- 방법 A: 조건부 UNIQUE 인덱스 (MySQL 8.0+)
-- ALTER TABLE `VENDOR_INFLUENCER_MAPPING` 
-- ADD UNIQUE INDEX `unique_active_vendor_influencer_status` 
-- (`VENDOR_SEQ`, `INFLUENCER_SEQ`, `STATUS`) 
-- WHERE `IS_ACT` = 'Y';

-- 방법 B: 가상 컬럼을 이용한 UNIQUE 제약조건 (MySQL 5.7+ 호환)
-- 1) 가상 컬럼 추가 (IS_ACT가 'Y'일 때만 값을 가짐)
ALTER TABLE `VENDOR_INFLUENCER_MAPPING` 
ADD COLUMN `UNIQUE_KEY_HELPER` VARCHAR(50) 
GENERATED ALWAYS AS (
  CASE 
    WHEN `IS_ACT` = 'Y' THEN CONCAT(`VENDOR_SEQ`, '-', `INFLUENCER_SEQ`, '-', `STATUS`)
    ELSE NULL 
  END
) STORED;

-- 2) 가상 컬럼에 UNIQUE 인덱스 적용
ALTER TABLE `VENDOR_INFLUENCER_MAPPING` 
ADD UNIQUE INDEX `unique_active_vendor_influencer_status` (`UNIQUE_KEY_HELPER`);

-- 3. 기존 데이터 정리 (중복 제거)
-- 활성 상태에서 중복된 레코드가 있다면 가장 최근 것만 남기고 나머지는 비활성화
UPDATE `VENDOR_INFLUENCER_MAPPING` v1
JOIN (
  SELECT 
    `VENDOR_SEQ`, 
    `INFLUENCER_SEQ`, 
    `STATUS`,
    MAX(`SEQ`) as latest_seq
  FROM `VENDOR_INFLUENCER_MAPPING`
  WHERE `IS_ACT` = 'Y'
  GROUP BY `VENDOR_SEQ`, `INFLUENCER_SEQ`, `STATUS`
  HAVING COUNT(*) > 1
) v2 ON v1.`VENDOR_SEQ` = v2.`VENDOR_SEQ` 
    AND v1.`INFLUENCER_SEQ` = v2.`INFLUENCER_SEQ` 
    AND v1.`STATUS` = v2.`STATUS`
    AND v1.`SEQ` < v2.latest_seq
SET v1.`IS_ACT` = 'N', 
    v1.`MOD_DATE` = NOW()
WHERE v1.`IS_ACT` = 'Y';

-- 4. 검증 쿼리 (중복 레코드 확인)
-- 아래 쿼리 결과가 0이어야 함
SELECT 
  `VENDOR_SEQ`, 
  `INFLUENCER_SEQ`, 
  `STATUS`, 
  COUNT(*) as duplicate_count
FROM `VENDOR_INFLUENCER_MAPPING`
WHERE `IS_ACT` = 'Y'
GROUP BY `VENDOR_SEQ`, `INFLUENCER_SEQ`, `STATUS`
HAVING COUNT(*) > 1;