The Relavance Of Referential Integrity

The Relevance of Referential Integrity

Databases are the core of Information Systems. Without databases, there would be no place to store valuable data that needs to be retrieved at any given time. There is a necessity to ensure the quality of a database in order to ensure the quality of the information system. To ensure the quality of a system, referential integrity must be in place to control the data. This facility maintains relationships between data and protecting data integrity.

What is Referential Integrity?

The relational model enforces referential integrity. That means one must enable the system referential integrity feature to meet relational rules. This can be as simple as just enabling referential integrity between tables. There are also consequences of doing so or not doing so. There are three types of integrity:

* Entity- The user must uniquely identify each record.

* Referential - Each foreign key value must have a matching primary key value in a related table (or be Null)

* Business - These are rules that are specific to the business and unrelated to relational database theory.

With referential integrity disabled, data can be entered at anytime as long as there is no violation of other table or field properties such as validation rules and data types. Basically referential integrity enforces rules that limit what data can be modified, added, or deleted. When referential integrity is enabled, a new foreign key value cannot be entered until a matching primary key value exists in a related table. A primary key value can not be changed if there is a matching foreign key value in a related table. If primary keys are deleted this condition will create orphans.

Why the need for Referential Integrity?

After creating a database, there is the process of normalization, creating primary keys and relationships. The thought of referential integrity may not be important. Suppose there is a database that needs to be maintained. This database stores product and order data. Now let's suppose natural data is used for the primary key and there needs to be an update to a particular customer's primary key value. Later the customer is upset because a delivery was never received. Or the customer gets and order but never gets and invoice. What happened? Well, when the customer's primary key value was changed, the foreign key value for the customer's related records were not updated and orphans were created. Orphans are foreign key records that do not match the primary key value. In other words, there is no way for the database system to match order details to the customer. Without referential integrity, one would enter unmatched foreign key values. The relational model does not allow these orphan records. Even though the database system may allow one to create orphans, the developer's job is to prevent them. The easiest way to prevent orphans is to implement referential integrity.

When to implement Referential Integrity?

One should enable referential integrity to every database. There are certain conditions that must be met before referential integrity is implemented:

* The relationship for which there is enforcement of referential integrity must be based on a primary key or a unique index.

* Referential integrity can not be enforced between tables in two different databases. The tables must be in the same database.