-- 修改外键约束为级联删除
-- 删除量表时，自动删除关联的测评记录和预警记录

-- 1. 先删除现有的外键约束
ALTER TABLE psy_warning DROP FOREIGN KEY fk_warning_assessment;

-- 2. 重新添加外键约束，设置为级联删除
ALTER TABLE psy_warning 
ADD CONSTRAINT fk_warning_assessment 
FOREIGN KEY (assessment_id) REFERENCES psy_assessment(assessment_id) 
ON DELETE CASCADE;

-- 3. 检查 psy_assessment 表是否有关联 psy_scale 的外键，如果有也改为级联删除
-- 先查看现有外键
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_SCHEMA = 'ry_xinli' 
AND REFERENCED_TABLE_NAME IS NOT NULL
AND TABLE_NAME IN ('psy_assessment', 'psy_warning', 'psy_assessment_answer', 'psy_assessment_report');

-- 4. 如果 psy_assessment_answer 有外键约束，也改为级联删除
-- ALTER TABLE psy_assessment_answer DROP FOREIGN KEY 外键名;
-- ALTER TABLE psy_assessment_answer 
-- ADD CONSTRAINT fk_answer_assessment 
-- FOREIGN KEY (assessment_id) REFERENCES psy_assessment(assessment_id) 
-- ON DELETE CASCADE;

-- 5. 如果 psy_assessment_report 有外键约束，也改为级联删除
-- ALTER TABLE psy_assessment_report DROP FOREIGN KEY 外键名;
-- ALTER TABLE psy_assessment_report 
-- ADD CONSTRAINT fk_report_assessment 
-- FOREIGN KEY (assessment_id) REFERENCES psy_assessment(assessment_id) 
-- ON DELETE CASCADE;

-- 验证修改结果
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS 
WHERE CONSTRAINT_SCHEMA = 'ry_xinli';