oracle 外键引用oracle中两个表的一个字段

Foreign Key referring one field of two tables in oracle

提问by Winter

I am trying to set a Foreign Key by refering to different tables.

我试图通过引用不同的表来设置外键。

Imagine the tables:

想象一下这些表：

Active_Card((primary key)Num_id number, num_card number,is_active varchar2);

Active_Card((primary key)Num_id number, num_card number,is_active varchar2);

TMP_CARD((PK)num_card number, reg_date date);

TMP_CARD((PK)num_card number, reg_date date);

DEFINITIVE_CARD((PK num_card number, name varchar, create_date date)

DEFINITIVE_CARD((PK num_card number, name varchar, create_date date)

So num_card is foreign key of Active_Card. My problem is how to refer both tables (TMP_CARD and DEFINITIVE_CARD) in Active_card.

所以 num_card 是 Active_Card 的外键。我的问题是如何在 Active_card 中引用两个表（TMP_CARD 和 DEFINITIVE_CARD）。

best regards,

此致，

回答by xQbert

Let's explain why this wouldn't be a good idea and go from there:

让我们解释为什么这不是一个好主意，然后从那里开始：

1st Let's say the RDBMS lets you do this.

1st 假设 RDBMS 允许您执行此操作。

So we have FK in Active_Cardthat relates to both Definitive_Cardand Tmp_Card.

所以我们有与 和Active_Card相关的FK 。 Definitive_CardTmp_Card

Now lets say we have a definitive_card#s of 123, 456 and a tmp_card#s of 123, 789

现在假设我们有definitive_card123、456 的 #s 和tmp_card123、789 的 #s

Foreign keys enforce referential integrity. So 123 would be ok to have since it exists in both. 456 and 789 wouldn't be as it doesn't exist in both tables.

外键强制执行参照完整性。所以 123 是可以的，因为它存在于两者中。456 和 789 不会，因为它在两个表中都不存在。

But this isn't wanted. you indicated that it needed to be in either table not both as indicated in comment stating: *In order to have a entry in Active_Card it must exist an entry in DEFINITIVE_CARD or in TMP_CARD*

但这不是想要的。您指出它需要在任一表中，而不是在评论中指出：*为了在 Active_Card 中有一个条目，它必须在 DEFINITIVE_CARD 或 TMP_CARD 中存在一个条目*

So since you don't want the same card_number in both you really have 2-3 choices:

因此，由于您不想在两者中使用相同的 card_number，因此您实际上有 2-3 种选择：

1. Put Card_numbers in a separate table and though an associative table join back to Active_card
2. Put a 2nd card_number column in Active_card and depending on what field is populated tells you what table to go to for additional information.
3. Put all fields in Definitive_Card and tmp_card in the same table

1. 将 Card_numbers 放在单独的表中，然后通过关联表连接回 Active_card
2. 在 Active_card 中放置第二个 card_number 列，并根据填充的字段告诉您要转到哪个表以获取其他信息。
3. 将 Definitive_Card 和 tmp_card 中的所有字段放在同一个表中

Each of these options have their own list of pros and cons: but without fully understanding the business requirements, we couldn't say which is best for your situation.

这些选项中的每一个都有自己的优点和缺点列表：但在不完全了解业务需求的情况下，我们无法确定哪个最适合您的情况。

We can say what your trying simply will not work in a RDBMS; but any of the 3 options above would and meet the requirements of being able to add appropriate foreign keys

我们可以说您的尝试在 RDBMS 中根本行不通；但上述 3 个选项中的任何一个都将满足能够添加适当外键的要求

回答by Michael O'Neill

I think the proposed design requirement is suspect. I've seen this design requirement before be borne from poorly constructed object models making their way into poorly constructed relational models.

我认为提议的设计要求是可疑的。我之前已经看到这种设计要求是由构造不良的对象模型导致的，它们进入了构造不良的关系模型。

Nonetheless, a materialized view can implement the design requirement if that requirement is that the active_card.num_card must reference both tables.

尽管如此，如果要求 active_card.num_card 必须引用两个表，则物化视图可以实现设计要求。

create table active_card( 
   num_id number primary key, 
   num_card number, 
   is_active varchar2(5) );
create table tmp_card( 
   num_card number primary key, 
   reg_date date );
create table definitive_card( 
   num_card number primary key, 
   name varchar(256), 
   create_date date );

create or replace view card_v as
select tc.num_card
  from tmp_card tc
 where exists ( select null 
                  from definitive_card dc 
                 where dc.num_card = tc.num_card );

alter table active_card 
   add constraint active_card_num_card_fk 
   foreign key (num_card) 
   references card_v (num_card); -- won't work

But you can't create foreign keys against views...so...create a materialized view:

但是你不能针对视图创建外键......所以......创建一个物化视图：

create materialized view log on tmp_card;
create materialized view log on definitive_card;

create materialized view card_mv
   build immediate 
   refresh fast 
   on commit
   as 
   select tc.num_card
     from tmp_card tc
    where exists ( select null 
                     from definitive_card dc 
                    where dc.num_card = tc.num_card )
;

alter table active_card 
   add constraint active_card_num_card_fk 
   foreign key (num_card) 
   references card_mv (num_card); -- works

insert into tmp_card values ( 123, sysdate );
insert into tmp_card values ( 456, sysdate );
insert into tmp_card values ( 789, sysdate );
insert into definitive_card values ( 123, 'OneTwoThree', sysdate );
insert into definitive_card values ( 789, 'SevenEightNine', sysdate );
insert into definitive_card values ( 111, 'OneOneOne', sysdate );

commit;

Now...

现在...

insert into active_card values ( 1, 123, 'true' ); -- succeeds
insert into active_card values ( 1, 456, 'true' ); -- fails
insert into active_card values ( 1, 111, 'true' ); -- fails

delete from tmp_card where num_card = 789; -- succeeds
commit;

delete from tmp_card where num_card = 123; -- succeeds, but
commit; --fails

If the design requirement is that active_card.num_card must be present in either and not both of the two tables, the materialized view definition would need to be adjusted.

如果设计要求是 active_card.num_card 必须出现在两个表中的一个而不是两个表中，则需要调整物化视图定义。

回答by Branko Dimitrijevic

I suspect you don't want to refer to both tables at the same time. You want to refer to one orthe other.

我怀疑您不想同时引用两个表。你想指的是一个或另一个。

To do that, use separate exclusive FKs. Alternatively, use inheritance. More info here.

为此，请使用单独的专用 FK。或者，使用继承。更多信息在这里。