如何维护公共伟大父表的引用完整性？
假设我有一个包含“问题组”的表格
GroupID | value --------+------ 42 | How often do you 9071 | Other question ...
，并且对于每组问题我都有“问题”和“可能的答案”
Group | QuestionID | value ------+------------+------ 42 | 5 | ... brush your teeth? 42 | 89 | ... go to the movies? 9071 | 709 | ... another question ... ...
Group | Answer | value ------+--------+------ 42 | 134 | several times per day 42 | 135 | twice a day 42 | 71 | once a day 42 | 803 | every other day 42 | 8 | once a week 42 | 666 | once a month ...
现在，在 PHP/HTML 中，我对问题和可能的答案进行（虚拟）完整叉积第 42 组构建一个 2 条目表，用户将在其中选择他/她的答案（HTML 版本)
How often do you | N/d | 2/d | 1/d | d*2 | 1/w | 1/m | ----------------------+-----+-----+-----+-----+-----+-----+ ... brush your teeth? | ( ) | (X) | ( ) | ( ) | ( ) | ( ) | ... go to the movies? | ( ) | ( ) | ( ) | ( ) | (X) | ( ) |
我知道我需要插入牙齿的答案（问题、答案...）值（5、135、...）
和插入电影的答案（问题、答案...）值（89, 8, ...）
问题是：有没有办法强制执行问题和答案，在answers 表中，“属于”同一组问题吗？
我愿意插入答案（问题、答案...）值（709、71、...）
被禁止，因为问题 709“属于”组 9071，答案 71 属于组 42。
Lets say I have a table with "Groups of Questions"
GroupID | value --------+------ 42 | How often do you 9071 | Other question ...
And, for each group of questions I have "questions" and "possible answers"
Group | QuestionID | value ------+------------+------ 42 | 5 | ... brush your teeth? 42 | 89 | ... go to the movies? 9071 | 709 | ... another question ... ...
Group | Answer | value ------+--------+------ 42 | 134 | several times per day 42 | 135 | twice a day 42 | 71 | once a day 42 | 803 | every other day 42 | 8 | once a week 42 | 666 | once a month ...
Now, in PHP/HTML, I do a (virtual) full cross product of questions and possible answers of group 42 to build a 2-entry table where the user will select his/her answers (HTML version)
How often do you | N/d | 2/d | 1/d | d*2 | 1/w | 1/m | ----------------------+-----+-----+-----+-----+-----+-----+ ... brush your teeth? | ( ) | (X) | ( ) | ( ) | ( ) | ( ) | ... go to the movies? | ( ) | ( ) | ( ) | ( ) | (X) | ( ) |
I know I'll need toinsert into answers (question, answer, ...) values (5, 135, ...) for teeth
andinsert into answers (question, answer, ...) values (89, 8, ...) for movies
The question is: is there a way to enforce that both question and answer, in the answers table, "belong" to the same group of questions?
I'd like thatinsert into answers (question, answer, ...) values (709, 71, ...)
be disallowed, because question 709 "belongs" to group 9071 and answer 71 belongs to group 42.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
好吧，答案与问题有关，而不是与小组有关（至少不是直接），所以我认为“可能的答案”表应该是：
然后当你插入答案时，你给出QuestionId 和 AnswerId，它们一起引用“可能的答案”，从而间接引用该组。
Well, the answer relates to the question, not to the group (at least not directly), so I would have thought the "possible answers" table should be:
Then when you insert an answer, you give the QuestionId and AnswerId, and together they reference the "possible answer" and thereby, indirectly, the group.
您包含
Group、QuestionID和Answer列，然后创建两个外键，其中一个位于(Group, QuestionID)另一个在(Group, Answer)上。You include the columns
Group,QuestionIDandAnswerthen create two foreign keys, one on(Group, QuestionID)and the other on(Group, Answer).