Referential Integrity in DBMS – Rules & Example
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 thecustomer_id
field that references theid
field in thecustomers
table. This would ensure that every order in theorders
table is associated with a valid customer in thecustomers
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 thebook_id
field that references theid
field in thebooks
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 thestudent_id
field that references theid
field in thestudents
table. This would ensure that every grade in thegrades
table is associated with a valid student at the school, and it would prevent grades from being recorded for non-existent students.
More to Read
- Relational Database Benefits and Limitations
- Relational Vs Non Relational Database
- 13 Examples of Relational Database
- Relational Database Vs. Object-Oriented Database
- 9 Types of Databases
- Distributed Database
- Operational Database
- Personal Database
- Graph Databases
- Centralized Database