influence/ddl/017_clean_start.sql

-- 1. 제약조건 삭제
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 = 'unique_active_partnership'
    LIMIT 1
);

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

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

-- 2. 특정 벤더-인플루언서 조합의 데이터만 삭제
DELETE FROM VENDOR_INFLUENCER_PARTNERSHIP
WHERE VENDOR_SEQ = 8 AND INFLUENCER_SEQ = 23;

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

-- 4. 데이터 삭제 확인
SELECT COUNT(*) as count 
FROM VENDOR_INFLUENCER_PARTNERSHIP
WHERE VENDOR_SEQ = 8 AND INFLUENCER_SEQ = 23;