Understanding Referential Integrity Constraints

by Ahlyzik M
Summary:

When developing a logical data model there are certain constraints that must be held between strong and weak entities. One of the constraints between the entities is called a referential integrity constraint. Below is an example that author Lockwood Lyon gives is the referential integrity constraint between both strong and weak entities:

  • The CUSTOMER table is the parent table, and contains rows whose unique identifier (the primary key) is the value in column customer_number.
  • The ORDER table is the child table, and contains rows having values for customer_number (the foreign key) that are required to correspond to values in the CUSTOMER table.

Considering these rules, the terms for deletion are as follows: If there is a restriction, the user will not be able to delete the column; otherwise, the attributes in all of the entities will be deleted as this is called the cascading rule. According to Lyon, there are a number of reasons as to why referential integrity constraints are enforced during development. When using a CASE tool to model the data, there is code generated that connects the two entities that determines how they should be joined. Referential Integrity relationships can be easily documented when the delete rules are defined in the database. There are also disadvantages which include lack of performance considering that if the weak attribute is queried, the strong attribute must be queried as well.

Reflection:

I see this business rule as fit for the process because the rules are implied when constructing the data model. When defining the database, I do not think that the referential integrity should be enforced as it slows down performance which is one thing that is imperative to have if one wants to have a fast and efficient system. Apparently this is somewhat of an issue when developers are implementing the database as they do not see the design fit during the process.

Citation:

Lyon, Lockwood. (2019, March 23). Referential Integrity: Best Practices IBM DB2. Retrieved from http://www.databasejournal.com/features/db2/article.php/3870031/Referential-Integrity-Best-Practices-for-IBM-DB2.htm