SQL多对多关系引用的数据完整性问题及优化方案咨询

当前设计的核心问题是quiz_responses无法保证quiz_id与device_id的组合合法性，可能出现不属于对应测验的设备评分记录，这类数据完整性漏洞会直接导致业务统计、数据分析失真，绝对不可接受，以下是几种可靠的解决方案：

1. 数据库层面通过复合外键强制约束 #

这是最直接的根源性解决方法：

• 先给db.quiz_device设置复合主键（或唯一复合索引，确保quiz_id+device_id的组合唯一）：

ALTER TABLE db.quiz_device 
ADD PRIMARY KEY (quiz_id, device_id);

• 给db.quiz_responses添加复合外键，关联quiz_device的复合主键：

ALTER TABLE db.quiz_responses
ADD CONSTRAINT fk_quiz_device_response
FOREIGN KEY (quiz_id, device_id) 
REFERENCES db.quiz_device(quiz_id, device_id);

这样数据库会直接拦截所有非法组合的插入操作，从底层保证数据合法性。

2. 应用层前置校验（备选方案） #

如果受限于现有架构无法修改数据库约束，可在写入quiz_responses前，先查询quiz_device表验证quiz_id与device_id的组合是否存在。只有验证通过后，再执行插入操作。
但这种方式依赖代码逻辑的严谨性，无法应对并发场景或代码疏漏，可靠性远低于数据库约束。

3. 调整表结构（适配特定业务场景） #

如果业务允许，可给quiz_device新增独立主键quiz_device_id：

ALTER TABLE db.quiz_device 
ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

然后修改quiz_responses结构，用quiz_device_id替代原有的quiz_id和device_id，并建立外键关联：

ALTER TABLE db.quiz_responses
DROP COLUMN quiz_id,
DROP COLUMN device_id,
ADD COLUMN quiz_device_id INT,
ADD CONSTRAINT fk_quiz_device_response
FOREIGN KEY (quiz_device_id) REFERENCES db.quiz_device(id);

这种方式通过单一外键就能确保关联合法性，同时简化了quiz_responses的结构，但需要业务逻辑适配新的关联方式。

内容的提问来源于stack exchange，提问作者Jeppe Christensen