May 18, 2014 by Ozgur Ozden
Entity integrity and referential integrity, What are they?
Before we go into details why entity and referential integrity are important in a database, i would like to define these one more time.
Watt (2010) describes entity integrity as “ Every table requires a primary key. The primary key, nor any part of the primary key, can contain NULL values. This is because NULL values for the primary key means we cannot identify some rows.” Primary key must be unique and must specify a row in the table. Without entity integrity data will be repeated in many places and database will be unreliable. Entity integrity is obtained when each tuple is uniquely identified and a primary key is set.
Blaha (2005) describes referential integrity as “ a database constraint that ensures that references between data are indeed valid and intact.” in other words a primary key must have a corresponding foreign key in the database. Many of the RDBMS’s contain built in referential integrity rules that can be applied between two tables.
As an example, suppose table X contains a primary key and linked to another table Y via foreign key. Rules of referential integrity does not allow you to add record to table Y. Also according to the another rule, whenever you delete or update information from table X, relevant linked information in table Y is deleted or updates as well. This is called cascade delete or update.
We also have third data integrity rule called domain integrity. Thakur (2010) explains this as “Domain integrity rules are concerned with maintaining the correctness of attribute values within relations. A domain integrity rule therefore, is simply a definition of the type of the domain, and domain integrity is closely related to the familiar concept of type checking in programming languages. The definition of the type of a domain must be as precise as possible in order to avoid violations of domain integrity.”
as an example if we have an attribute called employee salary, EMP_SALARY, it is not enough to describe this as integer. In order to prevent negative values, very low and high values it is better to describe this attribute as positive integer and assign upper and lower limits if it is possible.
Advantages of referential & entity Integrity
There are many benefits of having a integrity in the database.
Data Quality is one of them. Database does not only store the data but also maintains the quality and the reliability of the data by having valid links between the tables. Referential and entity integrity is a way to have this data quality and maintain it this will of course affect the performance of the database.
Consistency in the database is another advantage. Less bugs and quick development time can also be considered as other benefits. Data integrity increases the security of the database.
Since database is controlled and maintained from a single center and stability will increase.
Data reliability is extremely important for a business in order to have solid predictions about the company’s future and stay competitive in the market. Applying data integrity rules to a database is may be one of the best ways to have a reliable data because if its rules and restrictions. When designing a database these rules must be applied carefully and time must be taken to establish the relations properly.
1- Michael Blaha (2005) Referential Integrity Is Important For Databases (online) Available at: http://www.odbms.org/download/007.02%20Blaha%20Referential%20Integrity%20Is%20Important%20For%20Databases%20November%202005.PDF [Accessed at: 13.9.2013]
2- Adrienne Watt (2010) Database Design (online) Available at :http://solr.bccampus.ca:8001/bcc/file/3e346f96-60d7-4d74-8eb1-7b508bf58eab/1/Database-Design-1362588850.pdf [accessed at: 12.9.2013]
3- Coronel, C., Morris, S., Rob, P., (2011), “Database Systems: Design, Implementation, and Management”, 9th Ed. Course Technology, Cengage Learning, Boston, MA
5-Dinesh Thakur (2010) What is Database Integrity (Online) Available at: http://ecomputernotes.com/database-system/adv-database/database-integrity [accessed at: 12.9.2013]