Black Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook  - SCHEDULE CALL

sddsfsf

What is Data Integrity in SQL Server?

 

Data storage is the fundamental function of a database. This data is used for reporting and analysis. Different tables are used to store the database's data. To the greatest extent possible, error reduction is critical. Data errors and duplicates must also be avoided. We refer to this as maintaining the integrity of the data. By imposing a variety of constraints, we safeguard the database's integrity.

In the following paragraphs, we will discuss various SQL data integrity features. Understanding the Data integrity in SQL begin with the understanding of SQL Server; you can get an insight about the same through our online SQL server training.

 What is Data Integrity in SQL Server?

The overall accuracy, completeness, and dependability of data are referred to as data integrity. Error-free data can be demonstrated by the absence of variation between two instances or consecutive updates to a record.

Different Types of Data Integrity in SQL

Different types of data integrity are as follows

1) Entity Integrity

2) Domain Integrity

3) Referential Integrity

4) User-Defined Integrity

Data Integrity in SQL Server

Entity Integrity: For a fixed table, entity integrity defines a row like a unique entity. The modifier column's attribute or a table's primary key is applied by entity integrity.

Domain Integrity: The authority of the data for a particular column is known as domain integrity. The size of the possible values, the data type, or the pattern can all be used to achieve domain integrity.

Referential Integrity: This constraint is used to regulate the flexibility of the rows and is described between the two tables. If a record contains related records, you are unable to delete or change a value from the table. A value that does not exist in the primary key of the primary table cannot be entered into the foreign key field of the table. However, you can define that the data are distinct by inserting a null value into the foreign key.

User-Defined Integrity: Business laws may require prompting for subsequent actions whenever a particular action occurs.

When designing a database, constraints and normalization techniques are used to ensure the integrity of the data. In the following section, we will learn in-depth how to insert these features into the database.

What is Constraints in SQL Server

  • SQL constraints are used to define rules for a table's data.
  • A constraint is a method for limiting the types of data that can be included in a table. The reliability and accuracy of the data in the table are guaranteed by this. The action is aborted if there is a violation between the constraint and the data action.
  • Constraints can be placed at the table or column level. Constraints at the table level apply to the entire table, whereas those at the column level apply to a single column.

Next, we'll learn how to use constraints in SQL integrity check  databases.

Different Types of SQL Server Database Constraints

  • NOT NULL - Ensures that a column cannot have a NULL value

Syntax for implementing null constraint in a table using T-SQL is as follows

CREATE TABLE Persons (

ID int NOT NULL,

    LastName varchar(255) NOT NULL,

FirstName varchar(255) NOT NULL,

Age int

             );

The Output Looks Like Below

  • UNIQUE - Ensures that all values in a column are different

Syntax for the unique constraint is as follows

CREATE TABLE Persons1 (

ID int NOT NULL UNIQUE,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int

);

The Output Looks Like Below

  • PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

Syntax for declaring primary key is as below

CREATE TABLE Persons2 (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

CONSTRAINT PK_Person PRIMARY KEY (ID, LastName)

);

The Output Looks Like Below

  • FOREIGN KEY - Prevents actions that would destroy links between tables

Syntax for creating the foreign key is as below

CREATE TABLE Orders (

OrderID int NOT NULL PRIMARY KEY,

OrderNumber int NOT NULL,

PersonID int FOREIGN KEY REFERENCES Persons(PersonID)

);

  • CHECK- Ensures that the values in a column satisfy a specific condition

The syntax for SQL integrity check constraint is as below

CREATE TABLE Persons5 (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int CHECK (Age>=18)

);

The Output is as Below

  • DEFAULT - Sets a default value for a column if no value is specified

The syntax for default constraint is as below and know more about how to add a default constraint when creating a table in SQL server.

CREATE TABLE Persons6 (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

City varchar(255) DEFAULT 'Sandnes'

);

The Output is as Below

  • CREATE INDEX - Used to create and retrieve data from the database very quickly

The syntax for create index is as below

CREATE INDEX index_name

ON table_name (column1, column2, ...);

Advantage of SQL Server Database Constraints

The following are some of the benefits of database constraints:

  • They prevent incorrect data from being entered into relevant tables.
  • Put business logic into action at the database level.
  • Important database rules are documented.
  • Integrity in the relationship between any number of tables can be enforced.
  • Improve the performance of the database.
  • Instill a sense of individuality.

Disadvantage of SQL Server Database Constraints

  • Constraints can't handle varying data

Normalisation in SQL Server

What is data normalization? The process of removing redundant data from the table and improving its integrity is known as normalization. Additionally, normalization aids in the database's data organization. Sets the data into tabular form and removes duplicate data from relational tables in multiple steps.

Types of Normalisation

First Normal Form

  • If the atomicity of a table is 1, it is considered to be in its First Normal Form.
  • In this case, atomicity stipulates that a single cell cannot contain multiple values. It can only contain one attribute with one value.
  • The composite attribute, multi-valued attribute, and their combinations are not supported by the First normal form.

Second Normal Form

  • The table must be in First Normal Form as the first requirement for it to be in Second Normal Form.
  • The table shouldn't have any partial dependencies. Because of this partial dependency, the appropriate subset of the candidate key ought to provide a non-prime attribute.

Third Normal Form

  • The table must first be in the Second Normal Form in order for the table to be in the Third Normal Form.
  • Non-prime attributes, which are not part of the candidate key, should not be dependent on other non-prime attributes in a table. The second condition is that there should be no transitive dependency for non-prime attributes. Because of A  B and B  C (where it is not the case that B  A), a transitive dependency is a functional dependency in which A  C (A determines C) indirectly.
  • The third Normal Form ensures that data duplication is reduced. Additionally, it is utilized for data integrity.

Boyce Codd Normal Form

Another name for Boyce Codd Normal Form is 3.5 NF. Raymond F. Boyce and Edgar F. Codd created this superior version of 3NF to address specific anomalies that could not be resolved by 3NF.

Conclusion

In the above writeup, we have discussed SQL Server check database integrity. We have also discussed about different methods on how to implement SQL Server integrity check in the database. We learned about different types of constraints and also about normalization. Hope this can be an introductory text for readers who wants to study about SQL database integrity check and encourages them to study further on the topic. Additionally, we will talk about the various constraints that can be applied to a SQL Server database and why WITH CHECK CHECK CONSTRAINT is used.

cta14 icon

SQL Testing Training

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Trending Courses

Cyber Security icon

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security icon1

Upcoming Class

12 days 14 Dec 2024

QA icon

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA icon1

Upcoming Class

0 day 02 Dec 2024

Salesforce icon

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce icon1

Upcoming Class

8 days 10 Dec 2024

Business Analyst icon

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst icon1

Upcoming Class

11 days 13 Dec 2024

MS SQL Server icon

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server icon1

Upcoming Class

11 days 13 Dec 2024

Data Science icon

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science icon1

Upcoming Class

4 days 06 Dec 2024

DevOps icon

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps icon1

Upcoming Class

4 days 06 Dec 2024

Hadoop icon

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop icon1

Upcoming Class

4 days 06 Dec 2024

Python icon

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python icon1

Upcoming Class

19 days 21 Dec 2024

Artificial Intelligence icon

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence icon1

Upcoming Class

12 days 14 Dec 2024

Machine Learning icon

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning icon1

Upcoming Class

25 days 27 Dec 2024

 Tableau icon

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau icon1

Upcoming Class

4 days 06 Dec 2024