基于其他字段的同一字段上的多个外键 [英] multiple foreign keys on same field, based on other field
问题描述
我有一个名为BidItem的表，它有一个名为
BidAddendum与外键相关的表。我有一个名为
BidFolder的表，它与BidItem和BidAddendum相关，基于一个名为RefId的名为
的列和一个名为Type的列，即类型1是一个关系
到BidItem和类型2是与BidAddendum的关系。
有没有办法指定一个允许
$ b $的外键b不同的类型表明关系应该存在哪个表
on？或者我必须有两个具有相同列的单独表格
（并删除类型列）??我宁愿没有多张
相同的牌桌。
I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based on a
column called RefId and one called Type, i.e. type 1 is a relationship
to BidItem and type 2 is a relationship to BidAddendum.
Is there any way to specify a foreign key that will allow for the
different types indicating which table the relationship should exist
on? Or do I have to have two separate tables with identical columns
(and remove the type column) ?? I would prefer not to have multiple
identical tables.
推荐答案
2005年3月2日15:29 ：16 - 0800，pb648174写道：
On 2 Mar 2005 15:29:16 -0800, pb648174 wrote:
我有一个名为BidItem的表，它有一个名为
BidAddendum的表，由外键与之相关。我有一个名为
BidFolder的表，它与BidItem和BidAddendum相关，基于一个名为RefId的列和一个名为Type的列，即类型1是与BidItem的关系，类型2是与BidAddendum的关系。
有没有办法指定一个外键，允许
不同的类型表明关系应该存在哪个表
？或者我必须有两个具有相同列的单独表格
（并删除类型列）??我宁愿没有多张相同的桌子。
I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based on a
column called RefId and one called Type, i.e. type 1 is a relationship
to BidItem and type 2 is a relationship to BidAddendum.
Is there any way to specify a foreign key that will allow for the
different types indicating which table the relationship should exist
on? Or do I have to have two separate tables with identical columns
(and remove the type column) ?? I would prefer not to have multiple
identical tables.
您好pb648174，
如果我理解正确的话，BidFolder中的每一行都与BidItem中的一行或BidAddendum中的一行相关。
。到目前为止是否正确？
我还认为BidItem和BidAddendum的RefId都是
PRIMARY KEY或UNIQUE列，因此可以使用此列在一个
FOREIGN KEY约束中？
我实现这个的方法是在
BidFolder中有两个RefId列table（当然适当命名），带有CHECK约束
以确保其中只有一个被填充而另一个为NULL：
CREATE TABLE BidFolder
（.......
，.......
，Item_RefId？DEFAULT NULL - 替换？
，Addendum_RefId？DEFAULT NULL - 正确的类型
，.....
，PRIMARY KEY（....）
，FOREIGN KEY（Item_RefId）REFERENCES BidItem
，FOREIGN KEY（Addendum_RefId）REFERENCES BidAddendum
，CHECK（（Item_RefId为NULL且Addendum_RefID不是NULL）
OR（Item_RefId不为NULL且Addendum_RefID为空））
）
Best，Hugo
-
（删除_NO_和_SPAM_来获取我的电子邮件地址）
Hi pb648174,
If I understand you correctly, each row in BidFolder is related to
either one row in BidItem or to one row in BidAddendum. Correct so far?
Am I also correct that both BidItem and BidAddendum have RefId as either
PRIMARY KEY or UNIQUE column, so that this column can be used in a
FOREIGN KEY constraint?
The way I would implement this, is to have two RefId columns in the
BidFolder table (of course appropriately named), with a CHECK constraint
to ensure that exactly one of them is populated and the other is NULL:
CREATE TABLE BidFolder
( .......
, .......
, Item_RefId ? DEFAULT NULL -- Replace ? with
, Addendum_RefId ? DEFAULT NULL -- the correct type
, .....
, PRIMARY KEY (....)
, FOREIGN KEY (Item_RefId) REFERENCES BidItem
, FOREIGN KEY (Addendum_RefId) REFERENCES BidAddendum
, CHECK ((Item_RefId IS NULL AND Addendum_RefID IS NOT NULL)
OR (Item_RefId IS NOT NULL AND Addendum_RefID IS NULL))
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
我不完全确定我明白你要做什么，但
你能做什么喜欢
BidItem
PK RefId
PK类型 - 默认设置为2
FK BidAddendum_RefId
FK BidAddendum_Type
BidAddendum
PK RefId
PK Type - 默认设置为1
FK BidItem_RefId
FK BidItem_Type
BidFolder
PK无论什么
FK RefId
FK类型
" pb648174" <去**** @ webpaul.net>在留言新闻中写道：< 11 ********************** @ l41g2000cwc.googleg roups.com> ...
I am not totally sure i understand what you are trying to do, but
could you do someting like
BidItem
PK RefId
PK Type - Set default to 2
FK BidAddendum_RefId
FK BidAddendum_Type
BidAddendum
PK RefId
PK Type - Set default to 1
FK BidItem_RefId
FK BidItem_Type
BidFolder
PK Whatever
FK RefId
FK Type
"pb648174" <go****@webpaul.net> wrote in message news:<11**********************@l41g2000cwc.googleg roups.com>...
I有一个名为BidItem的表，它有一个名为
BidAddendum的表，它通过外键与之相关。我有一个名为
BidFolder的表，它与BidItem和BidAddendum相关，基于一个名为RefId的列和一个名为Type的列，即类型1是与BidItem的关系，类型2是与BidAddendum的关系。
有没有办法指定一个外键，允许
不同的类型表明关系应该存在哪个表
？或者我必须有两个具有相同列的单独表格
（并删除类型列）??我不希望有多个相同的表。
I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based on a
column called RefId and one called Type, i.e. type 1 is a relationship
to BidItem and type 2 is a relationship to BidAddendum.
Is there any way to specify a foreign key that will allow for the
different types indicating which table the relationship should exist
on? Or do I have to have two separate tables with identical columns
(and remove the type column) ?? I would prefer not to have multiple
identical tables.
不，BidItem和附录没有type和refid字段。
Type和RefId列仅在BidFolder表中，用于将一个或多个BidFolders与BidItem（Type 1）或
$关联起来
b $ b BidAddendum（类型2）。我不想我可以根据1或2的类型做复合外键
，所以我想知道我如何设置外键
在这种情况下。
另一个人建议在BidFolder
表中使用两个不同的列，这些列可以使用，但前进不会非常灵活，
因为如果我有另一个关系来映射，我将不得不更新所有
表和存储过程而不是仅仅添加BidFolder条目
with a类型3.
No, BidItem and Addendum do not have the type and refid fields. The
Type and RefId columns are only in the BidFolder table and are used to
associate one or more BidFolders with either a BidItem (Type 1) or
BidAddendum (Type 2). I don''t htink I can do a compound foreign key
based on the type of 1 or 2, so I''m wondering how I set a foreign key
in this scenario.
Another person suggested using two different columns in the BidFolder
table, which would work but would not be very flexible moving forward,
since if I had another relationship to map, I would have to update all
the tables and stored procs instead of just adding BidFolder entries
with a type of 3.
这篇关于基于其他字段的同一字段上的多个外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
