what is Referential integrity constraint in DBMS
Referential integrity is a system of rules that a DBMS uses to ensure that relationships between records in related tables are valid and that users don’t accidentally delete or amendment connected knowledge.
You can set referential integrity when the following conditions are below.
The matching Field from the first table may be a primary key or encompasses a distinctive index.
- The related fields have the same data type.
- Both tables belong to the same database. if the tables are linked tables, They must be of the same DBMS format, and you must open the database in which they are stored to set referential integrity. Referential integrity can’t be enforced for linked tables from database in other formats.
Referential integrity Rules
- You can’t enter a value in the foreign key field of the related table that doesn’t exist in the primary key of the primary table. However, you’ll be able to enter a Null price within the foreign key, specifying that the records are unrelated. For Example, you can’t have an order that is assigned a Null value in the customer ID field.
- You can’t delete a record from a primary table if matching records exist in a related table, For example, you can’t an employee record from the Employees table if there are orders allotted to the worker within the order table.
You can’t change a primary key value in the primary table if that record has a related record. For example, you can’t change an employee’s ID in the Employees table if there are orders assigned to that employee in the orders table.
Referential integrity Example
For example, if we have a tendency to delete record variety fifteen in an exceedingly primary table, we want to make certain that there’s no foreign key in any connected table with the worth of fifteen. we should always solely be able to delete a primary key if there are not any associated records. Otherwise, we might find yourself with the associated record.
Read also related article