Jul 30, 2020
What Is Data Integrity & How To Enforce It In A Database?
Data Integrity resembles an umbrella that refers to the accuracy, consistency, and correctness of data stored in a database. Data integrity is not about physical security, fault tolerance, or data preservation (backups). One can also think of data integrity in terms of an old objective as: 'garbage in, garbage out'. Data integrity is all about keeping the garbage data out. Data integrity refers to the correctness and completeness of data with respect to a database. A user can insert, update or delete the values in a Database and in order to restrict or constraint the user’s data integrity can be enforced. For example: Numeric columns/cells in a table should be restricted to accept alphabetic data.
Database Integrity is a central underlying aspect or term in the implementation of database technology. Having faith in the data correctness is a prerequisite for using the data in business, research or decision-making applications. So it’s for sure that the factors of ensuring database security and reliability cannot be ignored. One also has to consider the use of error checking and correction codes to deal with the integrity issues and improve the performance of database systems.
For example, the integrity of data in the EMP01 and EMP02 databases requires that the book title in the titles table should have a publisher in the publisher table. If one selects insert books which with a valid publisher into tables it will for sure violate the data integrity of EMP01 and EMP02 databases.
Enforcing Data Integrity in Databases
Basically, there are four primary types of data integrity: entity, domain, referential and user-defined.
Entity integrity applies at the row level; domain integrity applies at the column level, and referential integrity applies at the table level.
1. Entity Integrity in database ensures a table does not have any duplicate rows and is uniquely identified.
2. Domain Integrity requires that a set of data values fall within a specific range (domain) in order to be valid. In other words, domain integrity defines the permissible entries for a given column by restricting the data type, format, or range of possible values.
3. Referential Integrity is concerned with keeping the relationships between tables synchronized.
4. User-defined Integrity refers to specific business rules not covered by the other integrity categories. It is typically implemented through triggers and stored procedures. Data integrity is enforced by features such as check constraints, triggers, views, stored procedures, user-defined functions, and/or referential constraints.
Data Integrity Testing Tools
Here is a list of best data integrity testing tools that you can use to maintain the consistency, security, and accuracy of data.
Data Factory: Data Factory is powered by Microsoft Azure. It is the serverless, cost-effective, easy-to-use, intelligent, and fully managed data integration solution for transferring, preparing, and changing large volumes of data.
Oracle Data Integrator: Oracle Data Integrator by Oracle Company is the powerful data integration platform that can fulfill all your data integrity requirements from high-performance batch loads, trickle-feed integration processes to SOA supported data services. You can use the latest version of ODI 12c to improve the user experience and productivity at the workplace.
IBM InfoSphere Information Server for Data Integration: If you want to load, transform, and extract the data in any style, IBM Infosphere Information Server is ideal for your data integrity tasks. It provides multiple options for delivering data and has a common metadata framework with many in-built transformation functions to increase performance.
Talend: Talend is another best tool for data integrity, but you have to pay some amount after completing the trial period to enjoy the deployment in any environment and safety for your business. It allows you to integrate data quickly from any source and avoid bad quality data before entering your systems.
Data Integrity in DBMS (Database Management System)
In DBMS, data integrity refers to the consistency and accuracy of data. When it comes to creating databases, the major focus is given on how data integrates into the system and how it maintains throughout its entire life cycle. There are various types of data integrity used in the database world, such as Entity Integrity, Referential Integrity, Domain Integrity, and User-Defined Integrity. Maintaining data integrity in database or correctness of data is crucial while taking backups, storing, or updating the data into the system; otherwise, it can accidentally pose a severe risk or corrupt your personal or business information.
Data Integrity Example:
If there is one customer in one profile, then there will be only one unique customer profile. For example - customer id, customer name, customer phone number, customer address. In that case, You can't add two or more customers with the same id because it can show duplicate values and violate data integration rules.
Advantages of Data Integrity Constraints
Some of the advantages of enforcing integrity constraints in DBMS are:
1. Enforcing business rules in the code of a database application.
2. Stored procedures can be used with ease in order to completely control access of data.
3. The Enforcement of business rules can be done easily with triggered stored database procedures.
Maintaining Integrity of the data is a cornerstone function of the DBMS. Integrity constraints that the DBMS maintains are the business rules that are defined by the enterprise. The DBMS should have the capability to enforce data integrity for all the applications that use the data.
Data integrity rules defined centrally in the database is independent of the applications that use the database. When data integrity is implemented directly in the database, the application developer does not need to worry about coding of all the data integrity features directly into the application. Potential problems associated with coding data integrity into the application are:
- Additional programming required
- Inconsistencies and potential errors
- It is difficult to make changes
- Weaker security (i.e. it is easier to bypass)
