三级数据库 - 外键 [英] Three level database - foreign keys

问题描述

我有一个具有以下结构的三级数据库（简化为仅显示主键）：

I have a three level database with the following structure (simplified to only show the primary keys):

Table A: a_id
Table B: a_id, b_id
Table C: a_id, b_id, c_id

所以表C的可能值将是这样的：

So possible values for table C would be something like this:

a_id  b_id  c_id
   1     1     1
   1     1     2
   1     1     3
   1     2     1
   1     2     2
   2     1     1
   2     2     1
   2     2     2
...

我现在不确定，外键应该如何设置;或者如果它们应该被设置为主键。我的想法是在表B上有一个外键 B.a_id - > A.a_id ，C上的两个外键C C.a_id - > A.a_id 和（C.a_id，C.b_id） - > （B.a_id，B.b_id）。

I am now unsure, how foreign keys should be set; or if they should be set for the primary keys at all. My idea was to have a foreign key on table B B.a_id -> A.a_id, and two foreign key on C C.a_id -> A.a_id and ( C.a_id, C.b_id ) -> ( B.a_id, B.b_id ).

我应该如何设置外键？外键是否从 C-> A 是必需的？或者我甚至需要外键，所有这些列都是主键的一部分？

Is that the way I should set up the foreign keys? Is the foreign key from C->A necessary? Or do I even need foreign keys at all given that all those columns are part of the primary keys?

谢谢。

推荐答案

如果表B和表A之间已经有外键，以确保表B只包含值为 a_id 存在于表A中，则表C和表A之间在 a_id 上的额外FK是不必要的。这当然需要表B和表A之间的FK关系是加密的，活跃的，并且不以任何方式被禁用或规避。

If you already have a foreign key between table B and table A to make sure table B only contains entries that have a value for a_id that exists in table A, then the extra FK between table C and table A on a_id is unnecessary. This requires, of course, that the FK relation between Table B and Table A is encorced, active, and not disabled or circumvented in any way.

使FK之间的链接表C和表B已经确保 TableC.a_id 只能引用 a_id 的有效值（因为这是有保证的在表B中通过表B和表A之间的FK关系）。

Making the FK link between table C and table B already guarantees that TableC.a_id can only refer to a valid value of a_id (since that's guaranteed in table B through the FK relation between Table B and Table A).

这篇关于三级数据库 - 外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！