Single FK referes to PK from multiple tables

Question

I have Table A, Table B, and Table C. Table C has a column which should be a FK for a PK. The PK could be either Table A's id or Table B's id. What is the best practice to design such tables?

I am thinking of creating 2 columns in Table C, where the first one would determine either Table A or Table B, and the second one represents the id of either Table A or Table B (depending on the first column). Is it the right way to do it?

Are you sure that you don't have the PK-FK relationship backwards? — Pieter Geerkens, May 01 '13 at 02:52

Menelaos · Accepted Answer · 2013-04-30T20:51:14.143

Using SQL UNION to Combine Two ResultSet with Different Joins

See: http://www.sqlfiddle.com/#!8/3519e/4

I have made an example with 3 tables:

CAR
TRUCK
DRIVER_ASSIGNED

You can create one query to join A with C, another to join B with C, and join the two result sets using UNION. For example:

(select * from `A`, 
`C`
where `A`.ID = `C`.`ID` and
`C`.`Type` like 'A')
UNION
(select * from `B`, 
`C`
where `B`.ID = `C`.`ID` and
`C`.`Type` like 'B')

In Relation to Classes and Sub-Classes

It seems to be that TABLE A and TABLE B are sub-types of another type/class. So, e.g. TABLE A may be cars, and TABLE B, may be trucks, but they extend vehicles.

In this case I think you need a fourth table, TABLE PARENT, that will combine the common fields of both A and B. C will use the primary key of TABLE PARENT as the foreign K.

TABLE A and B will contain both as foreign keys but maybe also as primary keys the primary key of table PARENT.

So, using my analogy with vehicles let us assume:

    TABLE A = CARS
    TABLE B = TRUCKS
    TABLE C = ASSIGNED_DRIVERS
    TABLE PARENT = VEHICLES

    TABLE VEHICLES - PARENT of A and B
    -------------
    ID (PK)
    HORSE POWER
    LICENSE PLATE
    etc...

    TABLE CARS - 
    -------------
    ID (PK)
    VEHICLE_ID (FK linking to VEHICLES.ID)
    NUMBER_SEATS
etc...

    TABLE TRUCKS - 
    -------------
    ID (PK)
    VEHICLE_ID (FK linking to VEHICLES.ID)
    HIGHT (meters)
    MAXIMUM_STORAGE_WEIGHT
etc...


    TABLE DRIVERS_ID - 
    -------------
    VEHICLE_ID (FK linking to VEHICLES.ID)
    DRIVER_OD
  START_DATE
END_DATE
etc...

So, the following method would save you from this problem and also be more semantically correct.

You can also check online documentation such as:

http://www.dssbooks.com/web/Files/LookInside/Web-DSS-Chapter-03.pdf (page 55) to see the theory between classes and subclasses.

And you don't even need `Cars(id)` and `Trucks(id)`. The `vehicle_id` can be PK and FK to `Vehicles` at the same time. — ypercubeᵀᴹ, Apr 30 '13 at 20:08
Yes, this is true! He can save some trouble and just have VEHICLE_ID in all three tables as PK. — Menelaos, Apr 30 '13 at 20:11

score 1 · Answer 2 · edited May 23 '17 at 11:49

I am thinking of creating 2 columns in Table C, where the first one would determine either Table A or Table B, and the second one represents the id of either Table A or Table B (depending on the first column). Is it the right way to do it?

No. You would be preventing the DBMS from enforcing the foreign key. You'd have to enforce the FK through triggers or the application code, which is more prone to errors and potentially less performant.

Either make two FK fields (one for Table A, other for Table B) and use a CHECK¹ to ensure only one of them is not NULL, or use inheritance.

Single FK referes to PK from multiple tables

2 Answers2

Using SQL UNION to Combine Two ResultSet with Different Joins

In Relation to Classes and Sub-Classes