How to make referential integrity?

Question

I have an Oracle database with three tables :

Table1 : Vehicle

ID
Type
Item

Table2 : Car

ID
Brand

Table3 : Bus

ID
Bus Number

In table 1 :

if the type of the vehicle is Car, I want the column Item to store an ID from Car table ;
if the type of the vehicle is Bus, I want the column Item to store an ID from Bus table ;

This creates a problem when linking the tables with foreign keys constraints, because if, for example, I select a Car ID into Vehicle Table that doesn't exist in Bus Table, Oracle will raise an error.

What I'm doing actually is, I handle the tables without any relationships, but I'm facing the risk of inconsistency of data, in case a row from the tables Bus or Car are deleted.

Does anyone have an idea please ?

You cannot have a foreign key that references one table once, and another table in another case. The solution would be to have `CarID` and `BusID` on your base table, each referencing either the `Car` or `Bus` table, and making sure only one can be something other than NULL at any given time — marc_s, Jan 27 '14 at 11:37

score 1 · Accepted Answer · edited May 23 '17 at 12:03

Since Oracle supports deferred foreign keys, you can enforce both presence and exclusivity of the "child" rows. The exact technique is explained in: How to create multiple one to one's

The first part of the linked post doesn't use an explicit type discriminator, yet achieves both exclusivity and presence. Effectively, the NULL-ability of the "reverse" FKs acts as the type discriminator.
The second part of the linked post explains how the type discriminator can be used to achieve exclusivity (but not presence) even when not using deferred foreign keys.

I'm not sure this is a terribly good idea though - a less constrained but simpler model is often considered a justified compromise in such situation:

enter image description here

There is no type discriminator at all - you can easily determine if the parent is of given type, by checking if the corresponding row in the child table exists. It's rare to check the type without also getting the type-specific data, so this is unlikely to affect performance.

Obviously, more enforcement must now be done at the application level, but this is one of (rare) occasions where that's considered justified, due to simplicity and potential for better performance - there are fewer indexes, and therefore less index maintenance the DBMS has to do and less storage and cache pressure.

Thanks friend,your answer is really complete. – Hamidreza Jan 27 '14 at 12:52 — Hamidreza, Jan 27 '14 at 12:52

How to make referential integrity?

1 Answers1