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.

Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Jun 30 '20 at 12:07

Shantanu Kher · Accepted Answer · 2020-06-29T20:06:26.567

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

score 1 · Answer 2 · answered Jun 30 '20 at 07:30

No, a foreign key can only refer to one parent table.

You will either need two separate columns in INCIDENT_DETAIL, each with its own FK, or else combine INCIDENT and PENDINCIDENT into a single table with a type or status column.

The fact that you find yourself with a single column that seems to refer to either of two parent tables suggests to me that perhaps they are really the same thing at different states of processing.

Foreign Key has to exist on either table

2 Answers2