Foreign Key referring one field of two tables in oracle

Question

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);

TMP_CARD((PK)num_card number, reg_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.

best regards,

if TMP_CARD table does not exist i simply would: ALTER TABLE `Active_Card` ADD CONSTRAINT `num_card_ibfk_1` FOREIGN KEY (`num_card`) REFERENCES `DEFINITIVE_CARD` (`num_card`); — Winter, May 06 '13 at 11:16
Not the answer I'm looking for: I'd like to know what the business process looks like that you're trying to model. — Colin 't Hart, May 06 '13 at 11:17
You can't. It would appear you need another column in Active_Card for the second num_card. So Active_Card would look like `Active_card((primary Key) Num_ID Number, definitive_num_card, number, tmp_num_card number, is_active)`.. though this seems odd to me. you might be better off normalizing the data so you have a Card_numbers table with fields of `(num_card, reg_date, name, create_date, Card_Type)` knowing that 1 or 2 fields will be null depending on type. — xQbert, May 06 '13 at 11:19
In order to have a entry in Active_Card it must exist an entry in DEFINITIVE_CARD or in TMP_CARD — Winter, May 06 '13 at 11:19
But making some assumptions about what you want, why don't you combine 2 or even all 3 of the tables so you have one table of `Cards` and a `status` field to tell you whether the card is `temporary` or `definitive`. — Colin 't Hart, May 06 '13 at 11:19
the tables DEFINITIVE_CARD and TMP_CARD are already in use by the system so i cannot change them. I am trying to make a table to hold if the card is activated or not in order to allow the card to register in some services — Winter, May 06 '13 at 11:23

score 2 · Accepted Answer · answered May 06 '13 at 11:33

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.

So we have FK in Active_Card that relates to both Definitive_Card and Tmp_Card.

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

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.

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*

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

Put Card_numbers in a separate table and though an associative table join back to Active_card
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.
Put all fields in Definitive_Card and tmp_card in the same table

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

score 2 · Answer 2 · answered May 07 '13 at 22:59

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.

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.

score 0 · Answer 3 · edited May 23 '17 at 12:18

0

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

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

edited May 23 '17 at 12:18

Community

1
1

answered May 06 '13 at 11:20

Branko Dimitrijevic

50,809
10
93
167

Foreign Key referring one field of two tables in oracle

3 Answers3