多个外键有意义吗? [英] Do multiple foreign keys make sense?

问题描述

一个表具有多个外键有意义吗?

can it make sense for one table to have multiple foreign keys?

假设我有三个表，Table A，Table B和Table C.如果我将表视为对象(并且在我的代码中将它们映射为对象)，则表A和B与表C都具有多对一的关系.表/对象A和B可以分别具有C的许多实例.因此，我设计的方式是Table C指向Table A和Table B的主键:换句话说，Table C具有2个外键键(a_Id和b_Id).

Suppose I have three tables, Table A, Table B and Table C. If I think of the tables as objects (and they are mapped to objects in my code), then both Tables A and B have a many to one relationship with Table C. I.e. Table/object A and B can each have many instances of C. So the way I designed it is that Table C points to the primary key in both Table A and Table B: in other words, Table C has 2 foreign keys (a_Id, and b_Id).

编辑:我也忘了提到Table A可以有许多Table B实例.因此Table B在表A中有一个外键.如果有所不同...

Edit: I forgot to mention also that Table A can have many instances of Table B. So Table B has a foreign key into Table A. If this makes a difference...

我想知道这是否有意义，或者有更好的方法吗?谢谢.

I am wondering if this makes sense or is there a better way to do it? Thanks.

推荐答案

这很好，但是请注意，只有C始终必须同时拥有A和B时才有意义.

This is fine, but note that it only makes sense if a C always has to have both an A and a B as a pair.

如果只希望A具有C，而B具有C，但与A和B无关，则应将外键放在A和B中，并使其可以为空.

If you just want A's to have C's and B's to have C's, but A and B are otherwise unrelated then you should put the foreign key in A and in B and allow it to be nullable.

更新:经过澄清，您似乎想要两个独立的关系:一个A可以有多个C，而一个B可以有多个C，但是一个C只能属于一个A或一个B.

Update: after clarification it seems you want two separate relationships: an A can have many Cs, and a B can have many Cs, but a C can only belong to one A or one B.

解决方案:这是两个单独的一对多关系，因此创建两个新表A_C和B_C，并将外键放在此处. A_C包含A的外键和C的外键.B_C相似.

Solution: It's two separate one-to-many relationships, so create two new tables A_C and B_C, and put the foreign keys there. A_C contains a foreign key to A and a foreign key to C. Similarly for B_C.

这篇关于多个外键有意义吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！