Oracle - Having foreign keys in more than one table.

Question

I'm not sure if one can do this but I need to have foreign key reference 2 tables.

Table1 has 2 columns (A PK, B)
Table2 has 2 columns (C PK, D)

Table3 has 3 columns (A PK, B PK, E) and is made up of the first two table.

What I am hoping to do is something like the following:

create table Table3     
(
  A Varchar2 (4),   
  C Varchar2 (10),  
  E Char (1),
    constraint PK_A_C primary key (A, C),
    CONSTRAINT FK_A_C
   FOREIGN KEY (A, C) 
   REFERENCES (Table1.A, Table2.B)
);

I hope that this makes some sort of sense.

Thanks

James

Answer 1

A given foreign key constraint describes a relationship from one child table to one parent table.

You can, however, have two foreign key constraints, each pointing to the respective table:

create table Table3     
(
  A Varchar2 (4),   
  C Varchar2 (10),  
  E Char (1),
    constraint PK_A_C primary key (A, C),
    CONSTRAINT FK_A
     FOREIGN KEY (A) 
     REFERENCES Table1(A),
    CONSTRAINT FK_B
     FOREIGN KEY (C) 
     REFERENCES Table2(B)
);

Answer 2

Use:

CONSTRAINT fk_a FOREIGN KEY (a) REFERENCES TABLE1(a)
CONSTRAINT fk_b FOREIGN KEY (c) REFERENCES TABLE2(b)

Reference:

• Foreign Key Constraints

Answer 3

Thank you everyone for your quick answer on this.