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
orderstable might have a foreign key constraint on the
customer_idfield that references the
idfield in the
customerstable. This would ensure that every order in the
orderstable is associated with a valid customer in the
customerstable, and it would prevent orders from being placed by non-existent customers.
- In a database for a library, the
borrowerstable might have a foreign key constraint on the
book_idfield that references the
idfield in the
bookstable. 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
gradestable might have a foreign key constraint on the
student_idfield that references the
idfield in the
studentstable. This would ensure that every grade in the
gradestable 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