[WEBINAR] Bulletproof: How Today's Business Leaders Stay on Top

Referential Integrity (RI)

Definition - What does Referential Integrity (RI) mean?

Referential integrity is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key. Thus, any primary key field changes must be applied to all foreign keys, or not at all. The same restriction also applies to foreign keys in that any updates (but not necessarily deletions) must be propagated to the primary parent key.

Techopedia explains Referential Integrity (RI)

Consider a bank database, which contains two tables:

  • CUSTOMER_MASTER Table: This holds basic customer/account holder data such as name, social security number, address, gender and date of birth.
  • ACCOUNTS_MASTER Table: This stores basic bank account data such as account type, account creation date, account holder and withdrawal limits.

To uniquely identify each customer/accountholder in the CUSTOMER_MASTER table, a primary key column named CUSTOMER_ID is created.

To identify a customer and bank account relationship in the ACCOUNTS_MASTER table, an existing customer in the CUSTOMER_MASTER table must be referenced. Thus, the CUSTOMER_ID column - also created in the ACCOUNTS_MASTER table - is a foreign key. This column is special because its values are not newly-created. Rather, these values must reference existing and identical values in the primary key column of another table, which is the CUSTOMER_ID column of the CUSTOMER_MASTER table.

Referential integrity is a standard that means any CUSTOMER_ID value in the CUSTOMER_MASTER table may not be edited without editing the corresponding value in the ACCOUNTS_MASTER table. For example, if Andrew Smith’s customer ID is changed in the CUSTOMER_MASTER table, this change also must be applied to the ACCOUNTS_MASTER table, thus allowing Andrew Smith’s account information to link to his customer ID.

Share this:

Connect with us

Email Newsletter

Join thousands of others with our weekly newsletter

The 4th Era of IT Infrastructure: Superconverged Systems
The 4th Era of IT Infrastructure: Superconverged Systems:
Learn the benefits and limitations of the 3 generations of IT infrastructure – siloed, converged and hyperconverged – and discover how the 4th...
Approaches and Benefits of Network Virtualization
Approaches and Benefits of Network Virtualization:
Businesses today aspire to achieve a software-defined datacenter (SDDC) to enhance business agility and reduce operational complexity. However, the...
Free E-Book: Public Cloud Guide
Free E-Book: Public Cloud Guide:
This white paper is for leaders of Operations, Engineering, or Infrastructure teams who are creating or executing an IT roadmap.
Free Tool: Virtual Health Monitor
Free Tool: Virtual Health Monitor:
Virtual Health Monitor is a free virtualization monitoring and reporting tool for VMware, Hyper-V, RHEV, and XenServer environments.
Free 30 Day Trial – Turbonomic
Free 30 Day Trial – Turbonomic:
Turbonomic delivers an autonomic platform where virtual and cloud environments self-manage in real-time to assure application performance.