all-project/backend/sql/fix_quota_unique_constraint.sql

-- ========================================
-- 修复奖学金名额分配唯一约束问题
-- 创建时间: 2025-01-28
-- 问题描述: 唯一约束包含deleted字段导致逻辑删除时出现约束冲突
-- ========================================

USE hzdyjs_xg;

-- 1. 查看当前的唯一约束问题数据
SELECT
    batch_id,
    scholarship_type_id,
    target_dept_id,
    deleted,
    COUNT(*) as count_records,
    GROUP_CONCAT(id) as record_ids,
    GROUP_CONCAT(status) as statuses
FROM jcbt_scholarship_quota
GROUP BY batch_id, scholarship_type_id, target_dept_id, deleted
HAVING COUNT(*) > 1
ORDER BY batch_id, scholarship_type_id, target_dept_id;

-- 2. 查看可能存在冲突的记录（同一组合下有删除和未删除的记录）
SELECT
    q1.id as active_id,
    q1.batch_id,
    q1.scholarship_type_id,
    q1.target_dept_id,
    q1.deleted as active_deleted,
    q1.status as active_status,
    q2.id as deleted_id,
    q2.deleted as deleted_deleted,
    q2.status as deleted_status
FROM jcbt_scholarship_quota q1
INNER JOIN jcbt_scholarship_quota q2 ON
    q1.batch_id = q2.batch_id
    AND q1.scholarship_type_id = q2.scholarship_type_id
    AND q1.target_dept_id = q2.target_dept_id
    AND q1.deleted = 0
    AND q2.deleted = 1
ORDER BY q1.batch_id, q1.scholarship_type_id, q1.target_dept_id;

-- 3. 删除旧的唯一约束（包含deleted字段的约束）
ALTER TABLE jcbt_scholarship_quota DROP INDEX uk_batch_type_dept;

-- 4. 先清理可能的重复数据，然后重新添加不包含deleted字段的唯一约束
-- 这样可以避免删除时的约束冲突，同时保证业务数据的唯一性

-- 清理重复的已删除记录（保留最新的一条）
DELETE q1 FROM jcbt_scholarship_quota q1
INNER JOIN jcbt_scholarship_quota q2
WHERE q1.batch_id = q2.batch_id
  AND q1.scholarship_type_id = q2.scholarship_type_id
  AND q1.target_dept_id = q2.target_dept_id
  AND q1.deleted = 1
  AND q2.deleted = 1
  AND q1.id < q2.id;

-- 如果仍然存在同一业务组合的active和deleted记录，物理删除deleted记录
DELETE FROM jcbt_scholarship_quota
WHERE deleted = 1
AND EXISTS (
    SELECT 1 FROM jcbt_scholarship_quota q2
    WHERE q2.batch_id = jcbt_scholarship_quota.batch_id
      AND q2.scholarship_type_id = jcbt_scholarship_quota.scholarship_type_id
      AND q2.target_dept_id = jcbt_scholarship_quota.target_dept_id
      AND q2.deleted = 0
);

-- 重新添加不包含deleted字段的唯一约束
ALTER TABLE jcbt_scholarship_quota
ADD CONSTRAINT uk_batch_type_dept_business
UNIQUE (batch_id, scholarship_type_id, target_dept_id);

-- 5. 验证修复结果
SELECT
    '修复后检查' as check_type,
    batch_id,
    scholarship_type_id,
    target_dept_id,
    COUNT(*) as count_records,
    GROUP_CONCAT(CONCAT('id:', id, ',deleted:', deleted)) as record_details
FROM jcbt_scholarship_quota
WHERE deleted = 0
GROUP BY batch_id, scholarship_type_id, target_dept_id
HAVING COUNT(*) > 1
UNION ALL
SELECT
    '已删除记录检查' as check_type,
    batch_id,
    scholarship_type_id,
    target_dept_id,
    COUNT(*) as count_records,
    GROUP_CONCAT(CONCAT('id:', id, ',deleted:', deleted)) as record_details
FROM jcbt_scholarship_quota
WHERE deleted = 1
GROUP BY batch_id, scholarship_type_id, target_dept_id
HAVING COUNT(*) > 1;

-- 6. 显示当前表的约束信息
SHOW INDEX FROM jcbt_scholarship_quota WHERE Key_name LIKE '%batch%' OR Key_name LIKE '%type%' OR Key_name LIKE '%dept%';