What is Data Integrity in SQL?

Data Integrity in SQL refers to the database's presence of true and correct data. Repetitive values, inaccurate values, or broken relationships among tables are not wanted when we save data in a database. So, let us just look at an example of how broken relationships in a database might lead to data inconsistency.

For example, a user may inadvertently input a mobile number into the City field in the dB table. If the system enforces data integrity, then the users will be prohibited from making such errors.

Possible table data when data integrity is not enforced:

Ensuring data integrity in SQL ensures that the data is intact and unmodified throughout the life cycle. This covers data collection, storage, changes, migrations, restorations, etc. Whenever data is processed, there's also a chance that it will become distorted, which can happen either maliciously or by accident too.

We have already discussed what data integrity is in SQL. Let’s get to know the various types of data integrity in SQL and how we can check the data integrity in SQL databases.

How to Check Data Integrity in SQL?

Constraints can be used to maintain data integrity in a SQL database. The constraints describe the rules that must be followed while doing actions such as updating, deleting, and inserting data to maintain data integrity.

Here are a few ways by which the programmer can ensure data integrity in DBMS:

• Examine the main columns for each table to see if there is any inaccurate data. (Characters in any of the fields,  negative %, etc.)
• Attempt to remove an entry that is still being referenced by data from another table.
• Detect data inconsistencies and update them into pertinent tables to determine if any errors appear.
• If data in a table is updated, ensure that all other pertinent data is also updated. You must guarantee that replicated servers or databases are in sync and have consistent data. Various types of data integrity in SQL help the users maintain the data integrity in databases.

What are the Types of Data Integrity in SQL?

In SQL, we basically have four types of data integrity. Let’s go through all of them in detail:

1. Domain integrity
2. Entity integrity
3. Referential integrity
4. User-defined integrity

Domain Integrity:

The authenticity of inputs for a particular column is called domain integrity in SQL. The first step in ensuring domain integrity is choosing the right data format for a column. Additional steps may include defining the format of data with proper constraints as well as restrictions regarding the range of acceptable values. In SQL, the data types allowed are mostly numeric, textual, date, and so on. Each entry made by the user for a column must be present in the data type's domain.

For instance, if we need to store the wage of various workers in a table named the employee details table, we could specify that it must be a numeric value. Any input other than numeric-like characters would indeed be unacceptable, and DBMS would generate errors if we attempted to provide such input.

Entity Integrity:

Entity integrity requires that every row in the table should have distinct records. Thus, there must be no duplicate rows.

A primary key could be created to accomplish this. A primary key can be defined as a unique identifier stored in the main key field; no two rows present in a table can have a similar unique identifier. In SQL, the main key value, also known as the primary key, should not be Empty or NULL. If the primary key is NULL, then each row in a table cannot be identified uniquely.

For example, in an employee table, the ID of the employees can be considered the primary key since no two employees in an organization have the same value. Also, there cannot be any employee without an employee id.

Referential Integrity

Relationships are fundamental to referential integrity in SQL. Whenever 2 or maybe more tables are linked, we must guarantee that the foreign key value is always matched with the value of the primary value. A situation in which the foreign key's value has no corresponding primary key value in the main table is incorrect. As a result, the record would be considered an orphaned record in SQL.

When there is no connected entry in the primary table, the user cannot add records to a related table. It does not allow the users to modify entries in a primary table, which causes orphaned records in a related table in SQL. When there are corresponding related records, the user cannot delete records from a primary table.

User-Defined Integrity

This type of integrity allows the user to implement business rules to any database which are not covered by the other 3 types of data integrity discussed in the article above.

Examples of Data Integrity in SQL

Let’s go through some of the examples of data integrity and its violation:

• If a user attempts to provide a date that is outside the permitted limit.
• If a user enters a mobile number in the incorrect format.
• A software problem that attempts to remove the incorrect record in a table.
• If the user attempts to remove a record from a database, however, that record is referenced in another table as a relationship component.
• A user attempts to edit a primary key value when a foreign key in a relational table already points to that value.
• A programmer loses sight of the fact that he is working on a production system and begins inserting test data directly into the database.
• The programmer mistakenly inserts information into the incorrect table when migrating data between two databases.
• The network went down when the user was transmitting information between two databases.
• These are some situations where there might be a risk regarding the integrity and consistency of data in SQL.

Learn More

To learn more about SQL and Advanced Concepts of SQL, you can check out the SQL tutorial on Scaler Topics https://www.scaler.com/topics/sql/).