What is Data Integrity in SQL?

If you have worked with relational databases then you will be familiar with data integrity in SQL. Data integrity is a critical part of database management systems that ensures the accuracy and consistency of data stored in a database. Data integrity is an essential part of maintaining the integrity of your data and ensuring that your database operates smoothly.

In this article, we will explain the concept of data integrity in SQL, what it means, why it’s important, different types of data integrity, and how to implement them in your database.

What is Data Integrity in SQL?

Hey guys, Data integrity in SQL means maintaining accurate, reliable, and consistent data during any operations, such as transforming, storing, and retrieving data. Data integrity enforces the business rule associated with the database and prevents invalid information entered into the database tables. Data integrity ensures that available data in the database is accurate and correct. It won’t allow storage of repeating or duplicate data, incorrect values, and broken relationships between two or more tables. We are taking here a sample scenario to understand the impact of relationships being broken.

Sample Scenarios of Data Integrity in SQL

To understand the broken relationships, we have taken two tables i.e. Employee table with columns – Employee_ID, Solution, First_Name, Department_ID, and Department table with columns Department_ID, Department_Name. Said tables are related to each other that if any employee is onboarded in any department then onboarding data will be stored in the employee table.

If any employee is onboarded into any department then that department of the employee should be present at the department table. So let’s assume a situation that employee tables say that an employee is onboarded whose employee_id is 20 but the department_id does not exist in the employee table. This is not possible that an employee to be onboarded without tagging into any department. In such a situation might it be data department (i.e. department_id) was removed by anyone from the employee table?

So, if the data of the department_id of an employee is removed from the employee table then the corresponding data should also be removed from the department table before that. To maintain the data integrity in the database, changes have to be updated consistently.

How to Implement Data Integrity in SQL?

SQL requires a combination of techniques and tools to implement data integrity. Some of the common strategies to ensure data integrity are as follows –

1. SQL constraints help to enforce data integrity rules at the table level such as NOT NULL, UNIQUE, and CHECK.
2. Stored procedures help to automate complex data integrity checks and ensure consistency across tables.
3. Triggers are being used to automatically perform actions based on changes made to data in the database.
4. Training users on how to enter data correctly and regularly auditing data can help catch errors early and prevent data integrity issues from occurring.

Types of Data Integrity in SQL

There are basically, two types of SQL Data integrity as follows –

1. Physical Integrity: Physical integrity ensures no physical power outage, storage attrition, human faults, network sustainability, protection of data from external factors, natural climates or from hackers, etc other physical issues to make the database available at all times.
2. Logical Integrity: Logical integrity ensures the available database at all times and it can be implemented using SQL key constraints.

Type of Logical Data Integrity in SQL

Logical data integrity in SQL consists of constraints. Constraints define rules as per the business requirement to maintain data integrity i.e. insertion, modifications, deletions, etc. There are 4 types of Data integrity as follows –

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

1. Entity Integrity

Entity integrity constraints ensure that a table has a primary key and columns defined as the primary key must be unique, not null. This rule ensures that duplicate rows are not inserted in a table. Entity integrity can be inter-connected and used in various ways, a critical feature of a relational database. Entity integrity can be implemented using Primary Key, Unique, Not null, etc in the table of a database.

Syntax:

CREATE TABLE Table_Name
(
Column1 DATATYPE PRIMARY KEY,
Column2 DATATYPE NOT NULL,
Column3 DATATYPE UNIQUE,
ColumnN DATATYPE UNIQUE NOT NULL
);

Example:

USE SQLPOST
GO
CREATE TABLE Vendor
(
Vendor_Id VARCHAR(10) PRIMARY KEY,
Vendor_Name VARCHAR(30) NOT NULL,
Vendor_Email VARCHAR(50) UNIQUE,
Vendor_Contact VARCHAR(10) UNIQUE NOT NULL
);
GO

Screenshot: Below screenshot for reference –

In the above query result, We have created a table name Vendor where we have implemented entity integrities i.e. Primary Key, Not NULL, and Unique.

2. Domain Integrity

Domain integrity ensures that valid values are stored in a column of a table. It restricts to enter the valid data in a column, with the data type, data format, and range of value. It also determines whether or not a column should have a NULL value. We can enforce domain integrity while declaring a table, or through stored procedures and triggers, by using key definitions, check constraints, defaults, and nullability. Domain integrity can be implemented using Check and Default constraints in the database table.

Syntax:

CREATE TABLE Table_Name
(
Column1 DATATYPE PRIMARY KEY,
Column2 DATATYPE NOT NULL,
Column3 DATATYPE UNIQUE,
Column4 DATATYPE CHECK(Column4 BETWEEN Value1 AND Value2),
Column5 DATATYPE UNIQUE NOT NULL,
ColumnN DATATYPE NOT NULL DEFAULT 'Any_Value'
);

Example:

USE SQLPOST
GO
CREATE TABLE Vendor
(
Vendor_Id VARCHAR(10) PRIMARY KEY,
Vendor_Name VARCHAR(30) NOT NULL,
Vendor_Email VARCHAR(50) UNIQUE,
Vendor_Age INT CHECK(Vendor_Age BETWEEN 18 AND 22),
Vendor_Contact VARCHAR(10) UNIQUE NOT NULL,
Vendor_Source VARCHAR(30) NOT NULL DEFAULT 'Online'
);
GO

Screenshot: Below screenshot for reference –

In the above query result, We have created a table name Vendor where we have implemented domain integrities i.e. Check and Default.

3. Referential Integrity

Referential integrity ensures that all the values in the foreign key match the values in the primary key. It ensures that the data in the database remains uniformly consistent, accurate, and usable even after making changes to it. Referential integrity can be implemented using Foreign Key etc in the table of a database.

Syntax:

CREATE TABLE Table_Name1
(
Column1 DATATYPE PRIMARY KEY,
Column2 DATATYPE NOT NULL,
Column3 DATATYPE UNIQUE,
Column4 DATATYPE CHECK(Column4 BETWEEN Value1 AND Value2),
Column5 DATATYPE UNIQUE NOT NULL,
ColumnN DATATYPE NOT NULL DEFAULT 'Any_Value'
);

CREATE TABLE Table_Name2
(
Column1 DATATYPE PRIMARY KEY FOREIGN KEY REFERENCES Table_Name1(Column1)
);

Example:

USE SQLPOST
GO
CREATE TABLE Vendor
(
Vendor_Id VARCHAR(10) PRIMARY KEY,
Vendor_Name VARCHAR(30) NOT NULL,
Vendor_Email VARCHAR(50) UNIQUE,
Vendor_Age INT CHECK(Vendor_Age BETWEEN 18 AND 22),
Vendor_Contact VARCHAR(10) UNIQUE NOT NULL,
Vendor_Source VARCHAR(30) NOT NULL DEFAULT 'Online'
);
GO

USE SQLPOST
GO
CREATE TABLE Vendor_Master
(
Vendor_Id VARCHAR(10) PRIMARY KEY FOREIGN KEY REFERENCES Vendor(Vendor_Id)
);
GO

Screenshot: Below screenshot for reference –

In the above query results, We have created a table name Vendor and another table name Vendor_Master where we have implemented referential integrities i.e. Foreign Key.

4. User-defined Integrity

User-defined integrity refers to a customized set of business rules defined by a user, which do not belong to any standard category of integrities, such as Domain or referential. All the integrity types support user-defined integrity. User-defined integrity can be implemented using stored procedures, triggers, constraints, etc on column and table labels of a database. We will cover stored procedures, triggers, and constraints in their separate article posts.

How to Ensure Data Integrity in SQL?

Here are a few best practices to ensure data integrity in SQL that can minimize the risks of a data breach are as follows –

1. Take a regular backup of data and database periodically.
2. Ensure provide appropriate permissions to specific users and limit the access of data.
3. Ensure monitoring of the logs of inserted, altered, or deleted data by users.
4. Ensure the accuracy of data and focus on data validation while doing any collection or integration.
5. To ensure the information or data is up to date conduct a systematic periodic internal audit of the database.

Importance of Data Integrity

Data integrity is a critical part of SQL databases as follows –

1. Data integrity ensures that the data stored in the database is accurate and error-free, It prevents any incorrect data is being entered.
2. It ensures that the data in the database is consistent and follows predefined rules of constraints.
3. It ensures that the data stored in the database is reliable and can be used for making critical business decisions.
4. Data integrity ensures that the database is secure and prevents unauthorized access or modification of data.

Advantages of Data Integrity in SQL

Here are a few advantages of data integrity in SQL as follows –

1. Data integrity constraints can be created, modified, and removed using SQL statements.
2. No additional programming is required to create, modify and remove data integrity.
3. SQL statements to create, modify and remove data integrity are so easy to avoid programming errors.
4. Data integrity constraints can be disabled to avoid overhead performance and the same can be re-enabled once the data load is completed.
5. Data integrity constraints are defined on tables but stored in the data dictionary and due to this, all applications must adhere to data entered with the same integrity constraints.
6. Data integrity can be changed at the table level and hence at the application level, no changes are required.
7. Application will use the metadata of the data dictionary and immediately can inform users of violations if the integrity constraints are implemented at the table level.
8. Business rules can be implemented into the code of the application.
9. To control access to data completely using SQL stored procedures.
10. Business rules can be implemented using database triggers and stored procedures.

Difference between Entity and Referential Integrity Constraints

Here are a few differences between an entity and referential integrity constraints as follows –

1. A table must have a primary key and columns defined to be the primary key must be unique and not null in Entity integrity constraints but in Referential integrity constraints all values of the primary key match with all the values in the foreign key.
2. The Entity integrity constraints are – primary key, unique key, and NULL but the referential integrity constraint is a foreign key.
3. Entity integrity constraints enforce within a table but referential integrity constraints are enforced with one table or more than one table.
4. Entity integrity constraints ensure non-duplicates in a database but referential integrity constraints ensure the consistency of a database.
5. Two vendors can not be onboarded with the same vendor_id using entity integrity constraints but in referential integrity constraint, the vendor_id of the vendor table is referred to the vendor_master table.

FAQs

Conclusion

Data integrity is an important part of SQL that helps ensure the accuracy and consistency of data in the database. By implementing the different types of data integrity and strategies mentioned above, you can maintain the quality of your data and prevent data breaches or other security issues.

Remember, data integrity is an ongoing process that requires regular monitoring and maintenance. you can ensure the integrity of your data and keep your database running smoothly. We hope you would have liked this tutorial very well.

Please share this on your social media network so that it can be available to even more needy persons which may be beneficial for them. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response asap.

Recommended Article Posts

What is SQL Full Form and Its Key Feature?	What is RDBMS Full Form and Its Key Feature?
DDL Full Form and How is it used in SQL?	DML Full Form and How is it used in SQL?
DCL Full Form and How is it used in SQL?	TCL Full Form and How is it used in SQL?
What is DQL in SQL?	What is Database Language?
Key Constraints in DBMS With Fundamentals	Primary Key Constraint in SQL