Data integrity is a composed set of business rules that ensure the reliability, accessibility, consistency and accuracy of data store in a database. Data integrity is an important aspect to the design of a database that store records of any relational model. A database that utilizes rules such as data integrity; can increase performance, accuracy and stability. In simple terms: data integrity helps us prevent inserting the wrong types of entries in our database records.
Example of data integrity: cannot insert string data in integer column
Following figure is an example of Example of data integrity:
Types of Data Integrity:
- Entity integrity
- Domain integrity
- Referential integrity
Entity integrity:
Entity integrity refers to having uniqueness among entity. One of Data entity integrity example is having primary key in each table. Simply put, it uses the concept of primary key.
Referential integrity
Referring integrity refer to set a valid relationship between two table. Value in dependent table must match a value in primary key of reference table. In simple word it uses the concept of foreign key.
Example: Referring id of a table to another table
Domain integrity
Domain integrity basically is an acceptable value for a column or entering correct data for particular column.
Example: Setting data type of a column or setting range for a column.
Table Relationship:
Data integrity means valid data in tables. Referential integrity defines valid relationship of tables. Following are the different type of table relationship.
- One to one Relationship: it defines that one to one relationship between entities. For example one customer have one profile and one profile have one customer
- One to Many Relationships: it means one to many relationships between entities. For example one customer has many orders.
- Many to Many Relationships: it defines many to many relationships between entity. Let say customer can order multiple product item in a order. And each product item can be in multiple orders.
Example: In following example I am showing the implementation of data integrity in a database. Database is a collection of tables. In this example Customer and Orders are the Tables. Each table should have primary key according to concept of Entity Integrity. In Customer table example, we have four CustomerId, CustomerId _id is a primary key so that each row has unique value by this unique value we can find the customer name and Phone number easily. Let’s see another table. In order table, OrderId is a primary key and CustomerId is a foreign key. In order table example the customer that has 23 id have ordered computer, cap and jacket so that we can say one customer has many order in order table.
Summary: Data integrity is all about having accurate data in our database. It is used for verifying valid data in database, data validation and entity integrity, domain integrity and referential integrity.
If a database supports these features, it is the responsibility of the database to ensure data integrity as well as the consistency model for the data storage and retrieval. If a database does not support these features, it is the responsibility of the applications to ensure data integrity while the database supports the consistency model for the data storage and retrieval.