Foreign Key has to exist on either table

Question

I'm trying to get the following scenario to work:

I have three tables, and one of them (IncidentDetail) is to hold incident information of the other two (Incident and PendIncident). I want the IncidentDetail table to reference either an incident in the Incident, or PendIncident table so it could exist in either place. How would I set up the constraints?

Table 1 - Incident:

+--------------------+
| IncidentNbr | Desc |
+--------------------+

Table 2 - PendIncident:

+------------------------+
| PendIncidentNbr | Desc |
+------------------------+

Table 3 - IncidentDetail:

+-----------------------+
| IncidentNbr | Details |
+-----------------------+

The IncidentDetail table will have a FK constraint so that the IncidentDetail.IncidentNbr will need to have a value in either the Incident.IncidentNbr column OR the PendIncident.PendIncidentNbr column.

Is it possible to have a FK constraint on a single column that references into two different tables or will I need a second PendIncidentNbr column in the IncidentDetail table that has its own FK constraint to PendIncident.PendIncidentNbr?

Is that enough to ensure that the IncidentDetail table satisfies at least one of the FK constraints?

The other approach I can think of is to drop the FK constraints all together and use a check constraint where either the IncidentDetail.IncidentNbr column or IncidentDetail.PendIncidentNbr column has a value.

Answer 1

You can have FK constraints on a single column that references into two different tables but it will not work with your use case.

Since an incidentNbr exists either in Incident table or PendIncident table at any given point in time, having two FK constraints in IncidentDetail table will fail as soon as you attempt to insert a record in this child table. Since the incident exists in one parent table but not the other, it will throw an integrity constraint violation error w.r.t. second FK.

Using a check constraint can be a viable solution for this scenario.

Code snippet for quick reference -

Create table table_a(col_a number primary key);
Create table table_b(col_b number primary key);
Create table table_c(col_c number);

ALTER TABLE table_c
ADD CONSTRAINT fk_c_a
  FOREIGN KEY (col_c)
  REFERENCES table_a(col_a);

ALTER TABLE table_c
ADD CONSTRAINT fk_c_b
  FOREIGN KEY (col_c)
  REFERENCES table_b(col_b);

Insert into table_a values(100);
Insert into table_b values(200);
Insert into table_c values(100); —-This statement will throw integrity constraint violation error