I have 4 tables : TABLE_A, TABLE_B, TABLE_C, TABLE_RESULT
Constraints :
- FK_TABLE_A_ID is a foreign key refrerences IDA primary key in TABLE_A
- FK_TABLE_B_ID is a foreign key refrerences IDB primary key in TABLE_B
- FK_TABLE_C_ID is a foreign key refrerences IDC primary key in TABLE_C
- 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.
I'm wondering how to map those relations in order to enforce the 4th constraint. Any suggestions and hints will be appreciated
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 |