influence/ddl/015_fix_unique_constraint.sql

-- MariaDB 호환 DDL
-- 기존 제약조건 확인 및 삭제
SET @constraint_name = (
    SELECT CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'VENDOR_INFLUENCER_PARTNERSHIP'
    AND COLUMN_NAME = 'VENDOR_SEQ'
    AND REFERENCED_TABLE_NAME IS NULL
    AND CONSTRAINT_NAME != 'PRIMARY'
    LIMIT 1
);

SET @sql = IF(@constraint_name IS NOT NULL,
    CONCAT('ALTER TABLE VENDOR_INFLUENCER_PARTNERSHIP DROP INDEX ', @constraint_name),
    'SELECT "No unique constraint found to drop"'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 새로운 제약조건 추가
ALTER TABLE VENDOR_INFLUENCER_PARTNERSHIP
ADD CONSTRAINT unique_active_partnership 
UNIQUE KEY (VENDOR_SEQ, INFLUENCER_SEQ, IS_ACTIVE);

-- 기존 데이터 정리 (선택적)
-- 1. 각 벤더-인플루언서 조합에 대해 가장 최근의 비활성 레코드만 남기고 삭제
DELETE p1 FROM VENDOR_INFLUENCER_PARTNERSHIP p1
INNER JOIN VENDOR_INFLUENCER_PARTNERSHIP p2
WHERE p1.VENDOR_SEQ = p2.VENDOR_SEQ
AND p1.INFLUENCER_SEQ = p2.INFLUENCER_SEQ
AND p1.IS_ACTIVE = 'N'
AND p2.IS_ACTIVE = 'N'
AND p1.SEQ < p2.SEQ;