两个表，每个表都有一个引用另一个的外键

I am trying to create a database to store some questions and answers for a quiz

我正在尝试创建一个数据库来存储测验的一些问题和答案

I have so far two tables:

到目前为止我有两张桌子:

questions: (Question ID(PK), question string, correct answer ID)

问题:(问题ID(PK),问题字符串,正确答案ID)

answers: (Answer ID(PK), answer string, question ID)

答案:(答案ID(PK),答案字符串,问题ID)

I'm having trouble setting up the foreign key constraints. Surely I need to make sure that correct answer ID exists in answers and that also question ID in answers exists in the questions table. However, when trying to add these foreign keys in SQliteStudio, I am getting errors which suggest I cannot add a foreign key referencing table A > B when there is already a foreign key constraint that goes from B > A.

我在设置外键约束时遇到问题。当然,我需要确保答案中存在正确的答案ID,并且问题表中还存在答案中的ID问题。但是,当尝试在SQliteStudio中添加这些外键时,我收到的错误表明,当已经存在来自B> A的外键约束时,我无法添加引用表A> B的外键。

4 个解决方案

#1

This behavior is correct. Otherwise, you'd get a chicken-and-egg problem: you would not be able to insert an answer without inserting a row for the question first, and you also would not be able to insert a question without first inserting a valid answer for it. You will get a similar issue trying to delete a question or an answer being referenced.

这种行为是正确的。否则,你会遇到鸡与蛋的问题:如果没有先插入一行问题,你将无法插入答案,如果没有先输入有效的答案,你也无法插入问题。它。尝试删除被引用的问题或答案时,您会遇到类似的问题。

A typical solution to this is adding a column is_correct to the answer table.

一个典型的解决方案是在答案表中添加一列is_correct。

#2

http://www.sqlite.org/foreignkeys.html#fk_schemacommands

The ALTER TABLE command works differently in two respects when foreign key constraints are enabled:

启用外键约束时,ALTER TABLE命令在两个方面的工作方式不同:

It is not possible to use the "ALTER TABLE ... ADD COLUMN" syntax to add a column that includes a REFERENCES clause, unless the default value of the new column is NULL. Attempting to do so returns an error.

除非新列的默认值为NULL,否则无法使用“ALTER TABLE ... ADD COLUMN”语法添加包含REFERENCES子句的列。尝试这样做会返回错误。

...

The intent of these enhancements to the ALTER TABLE and DROP TABLE commands is to ensure that they cannot be used to create a database that contains foreign key violations, at least while foreign key constraints are enabled.

ALTER TABLE和DROP TABLE命令的这些增强的目的是确保它们不能用于创建包含外键冲突的数据库,至少在启用外键约束时。

#3

I think this situation can be modeled with a relation table:

我认为这种情况可以用关系表建模:

QA: (Question ID (FK), Answer ID (FK), Correct, ...) and the 2 FK's represent the PK of table.

QA :(问题ID(FK),答案ID(FK),正确,......)和2 FK代表表格的PK。

#4

Specific to your problem: There is no problem on setting up foreign key constraint among two tables both referring one another when tables are empty in spite of whether foreign key column is nullable or not.

特定于您的问题:尽管外键列是否可以为空,但是当表为空时,在两个表之间设置外键约束都没有问题。

But when Both these tables are filled with data then we have to keep in mind primary key data and nullable type of foreign key column. for example Table Question

但是当这两个表都填充了数据时,我们必须记住主键数据和可空类型的外键列。例如表问题

QuestionID Question AnwerID 1 'Question1' 1 2 'Question2' 2

QuestionID问题AnwerID 1'问题1'1 2'问题2'2

AnswerID Answer QuestionID 3 'Answer1' 3 4 'Answer2' 4

答案ID答案问题ID 3'答案1'3 4'答案2'4

Now if you try to set up foreign key constraint then u fails because these are already violating the foreign key rules.

现在,如果您尝试设置外键约束,则失败,因为这些已经违反了外键规则。

Simple thing is You will be able to set up foreign key constraint only when your current data does not violate them.

简单的事情是,只有在当前数据不违反外键约束时,才能设置外键约束。

If the foreign key columns does not allow null in case of your scenario then after setting up foreign key constraint, You are limited to only current data in third column (QuestionID or AnswerID)

如果在您的方案情况下外键列不允许为null,那么在设置外键约束之后,您仅限于第三列中的当前数据(QuestionID或AnswerID)
Even if you are able to implement it using NULL then same applies to the deletion. You have to trac both table: setting null to corresponding column in referring table Drop table will not work

即使您能够使用NULL实现它,那么同样适用于删除。您必须trac两个表:将null设置为引用表中的相应列Drop表将不起作用

Better Approach: These QuestionID, AnswerID should be mapped to another table SchemaName.[Mapping] Use your logic for correct answer ID. consider N*N mapping whether you support or not.

更好的方法:这些QuestionID,AnswerID应该映射到另一个表SchemaName。[Mapping]使用你的逻辑来获得正确的答案ID。考虑是否支持N * N映射。

Do not link both table internally.

不要在内部链接两个表。

#1