How to make JPA enforce the right FK constraint among many of them?

36 Views Asked by At

I have 4 tables : TABLE_A, TABLE_B, TABLE_C, TABLE_RESULT

Constraints :

  1. FK_TABLE_A_ID is a foreign key refrerences IDA primary key in TABLE_A
  2. FK_TABLE_B_ID is a foreign key refrerences IDB primary key in TABLE_B
  3. FK_TABLE_C_ID is a foreign key refrerences IDC primary key in TABLE_C
  4. Only one foreign column is set at the same time

Table_A :

IDA Field_A1 Field_AN
1 dataA11 dataA12
2 dataA21 dataA22
3 dataA32 dataA32

Table_B :

IDB Field_B1 Field_BN
1 dataB11 dataB12
2 dataB21 dataB22
3 dataB32 dataB32

Table_C :

IDC Field_C1 Field_CN
1 dataB11 dataB12
2 dataB21 dataB22
3 dataB32 dataB32

TABLE_RESULT:

ID FK_TABLE_A_ID FK_TABLE_B_ID FK_TABLE_C_ID
1 1 NULL NULL
2 NULL 2 NULL
3 NULL NULL 3

Not allowed cases :

ID FK_TABLE_A_ID FK_TABLE_B_ID FK_TABLE_C_ID
1 NULL NULL NULL
2 1 2 NULL
3 1 NULL 3
4 NULL 2 3
5 1 2 3

Some tables are in production environnement, and this design is imposed i don't have other options like refactoring the existing code or modifying the db schema.

  1. I'm wondering how to map those relations in order to enforce the 4th constraint. Any suggestions and hints will be appreciated

  2. Is it possible to make FK_TABLE_A_OR_B_OR_C_ID reference multiple columns on multiple tables and add a new TYPE column to know which table is referenced.

ID FK_TABLE_A_OR_B_OR_C_ID TYPE
1 1 A
2 2 B
3 3 C
0

There are 0 best solutions below