对 1:M 关系实施数据驱动的 1:1 约束
使用 SQL Server 2K8 R2，我有两个相关的表 - 成员和调查问卷。每个表都有一个称为 ID 的 BigInt PK。调查问卷具有会员 ID，可与正在填写表格的会员联系起来。 随着时间的推移，会员可能会多次完成调查问卷。业务要求是每个成员每年最多完成一次调查问卷。据我所知，这无法使用简单的CHECK CONSTRAINT来解决。
有没有“干净”的方法来做到这一点？我希望避免做类似以下的事情：
CREATE TRIGGER tr_Questionnaire_Insert
ON Questionnaire
INSTEAD OF INSERT
AS BEGIN
-- Check for a violation of once questionnaire per calendar year and if found, call RAISERROR
-- Otherwise continue with INSERT
END;
我不想使用这种方法的原因主要是因为它要求我（或任何将长期维护此解决方案的人）记住在以下情况下维护触发器 ：调查问卷的模式应该改变。
Using SQL Server 2K8 R2, I have two related tables - Member and Questionnaire. Each table has a BigInt PK called ID. Questionnaire has MemberID to relate back to the member who is in the process of completing the form. Over time, a member may complete the questionnaire many times. The business requirement is that each member complete a questionnaire at most once per year. AFAIK this can't be solved using a simple CHECK CONSTRAINT.
Is there is "clean" way to do this? I'm hoping to avoid doing something like the following:
CREATE TRIGGER tr_Questionnaire_Insert
ON Questionnaire
INSTEAD OF INSERT
AS BEGIN
-- Check for a violation of once questionnaire per calendar year and if found, call RAISERROR
-- Otherwise continue with INSERT
END;
The reason I don't want to use this approach is primarily because it requires me (or whomever will be maintaining this solution in the long-term) to remember to maintain the trigger if the schema for Questionnaire should change.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
只需在调查问卷表上创建一个包含 MemberId 和年份的唯一索引即可。
Just create a unique index on the the questionnaire table that includes MemberId and year.