Oracle - having foreign keys in multiple tables

Question

I'm not sure that this can be done, but I need to have tables of links to foreign keys.

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

Table 3 has 3 columns (A PK, B PK, E) and consists of the first two tables.

What I hope 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 this makes some sense.

thanks

James

Answer 1

The specified foreign key constraint describes the relationship from one child table to one parent table.

However, you can have two foreign key constraints, each of which points to a corresponding 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

Using:

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

Link:

• Foreign Key Constraints