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. The ACCOUNTS_MASTER table does not hold account holder information, with the exception of storing the CUSTOMER_ID as a foreign key. By the same logic, if a customer ID in the bank-account table is permitted without a corresponding customer ID in the customer master table, then the bank account would exist without an account-holder, which is impossible. Similarly, if a customer ID is deleted from the CUSTOMER_MASTER table, then all corresponding entries in the ACCOUNTS_MASTER table must be deleted. Again, this implies that if a customer is deleted from this database, each bank account linked to this customer also must be deleted. Referential integrity is a fundamental relational database concept. However, certain database systems implement referential integrity differently. For example, some systems do not allow foreign and primary keys to be edited simultaneously. In most cases, the primary key must be deleted before the foreign key may be edited. Thus, a database should be designed with fixed primary and foreign keys that do not entail or require post-creation changes. For example, a customer may decide to close a bank account. Instead of deleting the customer’s account from the CUSTOMER_MASTER table, an extra column named ACTIVE may be used to delineate customer status, i.e., Y for active and N for inactive. If a customer does any business with this bank (e.g., opens an account or secures a loan), this customer’s status in the CUSTOMER_MASTER table must be active; i.e., there must be a Y in the CUSTOMER_MASTER table’s ACTIVE column.
Posted by:
How Can Analytics Improve Business Free Webinar

Connect with us

Techopedia on Linkedin
Techopedia on Linkedin
Tweat cdn.techopedia.com
Techopedia on Twitter


'@Techopedia'
Sign up for Techopedia's Free Newsletter!
Techwise Webinar Series
How Can Analytics Improve Business?
Register for this episode of TechWise to learn from two of the most experienced analysts in the business: Dr. Robin Bloor, Chief Analyst of The Bloor Group, and Dr. Kirk Borne, Data Scientist, George Mason University.

Email Newsletter

Join 138,000+ IT pros on our weekly newsletter