Referential Integrity in DBMS

Referential integrity is a property of a database that ensures the consistency and correctness of data in a table. It is typically enforced through the use of foreign keys, which are fields in a table that reference the primary key of another table.

Referential Integrity Rules

Referential integrity is a set of rules that ensure consistency and accuracy of data in a relational database by defining relationships between tables and enforcing integrity constraints. It is used to maintain the integrity of the relationships between tables. This is achieved by ensuring that the values in the foreign key columns of one table match the values in the primary key columns of another table. This way, referential integrity helps to ensure that data is valid, consistent and accurate across multiple tables in a database. The main rules of referential integrity are to ensure that:

A foreign key value in one table corresponds to an existing primary key value in another table.
When a primary key value is deleted, all foreign key values that reference it are also deleted or set to null.
When a primary key value is updated, all foreign key values that reference it are also updated.

Referential integrity is enforced by creating relationships between tables and enforcing integrity constraints, such as the use of foreign key constraints, which ensure that referential integrity is maintained in the database.

Referential Integrity Examples

Referential integrity ensures that every foreign key value in a table must either match a valid primary key value in the referenced table, or be NULL. This means that a row in the table with a foreign key cannot reference a primary key value that does not exist in the referenced table, and it also cannot be left blank if the foreign key constraint does not allow NULL values.

Here are some examples of how referential integrity might be used:

In a database for a retail store, the orders table might have a foreign key constraint on the customer_id field that references the id field in the customers table. This would ensure that every order in the orders table is associated with a valid customer in the customers table, and it would prevent orders from being placed by non-existent customers.
In a database for a library, the borrowers table might have a foreign key constraint on the book_id field that references the id field in the books table. This would ensure that every book borrowed by a patron is a valid book in the library’s collection, and it would prevent patrons from borrowing non-existent books.
In a database for a school, the grades table might have a foreign key constraint on the student_id field that references the id field in the students table. This would ensure that every grade in the grades table is associated with a valid student at the school, and it would prevent grades from being recorded for non-existent students.