Improving performance by Declarative Referential Integrity (DRI)

Declarative Referential Integrity (DRI) ensures integrity of the database by a properly managed primary key and foreign key relationship. Correctly defined primary keys and foreign keys help query optimizer to select the best-suited execution plan for the query.

It has been observed many times that developers create a master table with a primary key and use that primary key field in the child table but don't define a foreign key in the table schema. This is not a good practice because by initiating a foreign key in the child table, you ensure that each record in the child table has a reference key in the parent table. This is a good thing, right? But, let us clarify that, by maintaining the parent and foreign key properly, we not only achieve integrity in the database but are also able to gain performance benefits. We have an example to prove it.

Getting ready

There is no automatic way to find this behavior; this is simply a...