Referential Integrity in Database
Efficiency in Database Design – Part 1
Division 3
Forward: In this division, we see how to make a database efficient. In this part of the series, we look at what is called Referential Integrity in Database.
By: Chrysanthus Date Published: 5 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Referential Integrity
Consider the following two tables:
Sales(SaleID, Date, EmployeeID, CustomerID)
SaleDetails(SaleID, ProductID, SoldPrice)
The relationship between the above two tables is one-to-many. One on the side of sales and many on the side of SaleDetails. The primary key for the Sales table is SaleID. The primary key for the SaleDetails table is composite (SaleID, ProductID). Consider the following two tables:
Employee(EmployeeID, Address, City, State, Country, Phone)
Orders(OrderID, Date, EmployeeID, SupplierID)
The relationship between the above two tables is one-to-many. One on the side of Employee and many on the side of Orders. The primary key for the Employee table is EmployeeID. Our interest in the Orders table is the foreign key, EmployeeID. One employee can make many orders.
Referential Integrity refers to the tight relationship between the rows of the two tables of one-to-many relationship.
Consider the last two tables above. You have the Employee table, containing a list of employees’ information. The relationship between the two tables is through the EmployeeID column in both tables. An EmployeeID value appears only once in the Employee table, but it can appear in more than one rows (in foreign key) in the Orders table. For every row in the Orders table, there is a corresponding row in the Employee table.
In a one-to-many relationship, you cannot add a row in the many side table, where there is no corresponding row (EmployeeID) in the one side table. Things like this will allow fraud in the database. Many DBMS give you the chance to allow or prevent this. When you code the DBMS to prevent this, we say you have enforced referential integrity.
Updating a Row
To update a row means to edit or modify the row. You should not modify a row on the many side giving it some new value (EmployeeID) when there is no corresponding row (EmployeeID) on the one side, for the same reasons given above. In this case, do not give a new EmployeeID on the many side. You are advised to enforce referential integrity for this. Also, if you modify a row (EmployeeID) in the one side, make sure any corresponding rows (EmployeeID) on the many side, are adjusted (unless you are adding a new employee on the one side). You are advised to enforce referential integrity to guard against such errors (tolerance).
If you delete a row on the many side, there is no problem. However, if you delete a row on the one side you have to delete the corresponding rows on the many side (unless the row did not have corresponding rows on the many side). Many DBMS give you the chance to allow or prevent this. When you code the DBMS to prevent this, we say you have enforced Cascade on Delete (so that deleting a row on the one side deletes the corresponding rows on the many side automatically).
When you learn a particular DBMS such as in Sybase or MySQL you will know how to code (enforced) referential integrity.
That is it for referential integrity. We take a break here and continue in the next part of the series.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT