Referential Integrity and Performance

There are three ways to implement Referential Integrity in a database system.  And each method results in different database performance.

1)
Primary and Foreign Keys:
Fastest.  Most structured.  Declarative.  No coding required.

2)
Triggers:
in between.

3)
Application code:
Slowest.  Least Structured.  Procedural.  The most coding required.   If the code is found in applications outside of Oracle, network traffic gets involved and is even slower.

No trigger, PLSQL, C, C++, or java code will ever verify referential integrity faster than built in primary and foreign keys.

One Reason:  to move from SQL, to PLSQL, and back, there are two switches that must occur.  A SQL to PLSQL switch.  And back again. These switches add an overhead that the built in SQL does not have.

You also have to add statements for error trapping, such as
IF NO_DATA_FOUND THEN

I haven’t found these concepts written in many places. My feeling is that they should be one of the first things taught about referential integrity, and a key question on the final if there is one.