CODEWITHSUNDEEP
oracle-databaserelationshipsreferential-integrity
Thomas Carlton
Thomas Carlton

Reputation: 5968

How to make referential integrity?

I have an Oracle database with three tables :

Table1 : Vehicle

Table2 : Car

Table3 : Bus

In table 1 :

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 ?

Upvotes: 0

Views: 149

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52147

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.

Upvotes: 1

Related Questions