Referential integrity is a rule that preserves the defined relationship between the table when records are added or deleted. Relationships are also an important part of a database system.
Different types of tables with different information are linked with each other to form a database. To learn more about relationships in the database, read that complete article.
Relationships in Database
Different tables in a database can be joined together by establishing a relationship between them. Different types of relationships are as follows:
- One-to-One Relationship
- One-to-Many Relationship
- Many-to-Many Relationship
One-to-One Relationship
This type of relationship is used when “for each record in the first table, there is only one record in the second table and for each record in the second table, there is only one record in the first table”.
Example:
For example, we have two tables named Student and Marks. The student table contains the basic information of the students and the Marks table contains the marks of the students.
Both tables are joined with a one-to-one relationship. It means that for each record in the Student table, there is only one record in the Marks table and vice versa. Each Roll No occurs once in both tables.
One-to-Many Relationship
This type of relationship is used when “for each record in the first table, there are one or more records in the second table and for each record in the second table, there is only one record in the first table”.
Example:
For example, we have two tables named Student and Subject. The student table contains the basic information of the students and the Subject table contains the marks of the students in different subjects. Both tables are joined with a one-to-many relationship.
It means that for each record in the Student table, there can be one or more records in the Subject table and for each record in the Subject table, there can be only one record in the Student table.
There are more records in the Subject table against one record in the Student table because one student studies many subjects.
Many-to-Many Relationship
This type of relationship is used when “for each record in the first table, there are one or more records in the second table and for each record in the second table, there are one or more records in the first table”.
Example:
For example, we have two tables named Authors and Books. The author’s table contains the information of the authors and the Books table contains the information of the books.
Both tables are joined with a many-to-many relationship. It means that for each record in the Author table, there can be one or more records in the Books table and vice versa.
It means that one author may have written one or more books. Similarly, one book may have been written by one or more authors.
Referential Integrity
A rule that preserves the defined relationships in a database between the table when records are added or deleted is called referential integrity. it means that you cannot enter a value in the foreign key field that does not have a corresponding value in the primary key field of the related table.
Referential integrity is applied when the data of an entity is stored in more than one table. When two tables are linked through a relationship, one table is called the master table and the other is called the child table.
The most important advantages of using referential integrity are as follows:
- If the record does not exist in the master table, it cannot be stored in the child table.
- A record cannot be deleted from the master table if a related record exists in the child table.
Example:
The following figure shows two tables. The master table contains the Registration No, Name, and Class of the students. The child table contains Registration No, Subject, and Marks of the students. Both tables are connected through relationships. The Child table is connected to the Master table with Registration No.
In the above example, there are two tables Master and Child. The user cannot enter data in the Child table without first entering the corresponding data in the Master table.
Suppose the user wants to enter the result of a student with Registration No 96-AG-1940. He has to enter the record in the Master table and then he can enter the details of that student in the Child table.
Similarly, if the user wants to delete a record in the Master table, he has to delete the corresponding records in the Child table.
Read Also: