Data Integrity in SQL Server

Enforcing data integrity guarantees the quality of data in the database.

For Example,
1. if an employee is entered with an employee_id value of 123, the database should not permit another employee to have
an ID with the same value.
2. If you have an employee_rating column intended to have values ranging from 1 to 5, the database should not accept a
value of 6.
3. If the table has a dept_id column that stores the department number for the employee, the database should permit
only values that are valid for the department numbers in the company.

Types of Data Integrity

There are two types of Data Integrity in SQL Server:

  1. Declarative Data Integrity.
  2. Procedural Data Integrity.

 

Declarative Data Integrity
Declaring constraints to the columns (like PK,UNIQUE,FK,DEFAULT, CHECK) of a table is said to be Declarative Data Integrity.

Procedural Data Integrity
Enforcing the same rule by using Script, Procedure and Triggers is said to be Procedural Data Integrity.

Categories of Data Integrity

The following are the categories of the Data Integrity.

1. Entity Integrity.
2. Domain Integrity.
3. Referential Integrity.
4. User – Defined Integrity.

1. Entity Integrity:
It defines a row as a unique entity for a particular table. Enforces the integrity of the identifier columns or the primary
key of a table through indexes, UNIQUE constraints, PRIMARY constraints or IDENTITY constraints.

2. Domain Integrity:
It is the validity of entries for a specific column. We can restrict the type by using the data types. Restrict the format
by using the CHECK constraints. Restrict the range of possible values by using FOREIGN KEY constraints, CHECK
constraints, DEFAULT definitions, NOT NULL definitions

3. Referential Integrity:
It preserves the defined relationships between the tables when records are entered or deleted. The relationship can be
between foreign keys and primary keys or between foreign keys or unique keys through FOREIGN and CHECK constraints.
When you enforce Referential Integrity, SQL server prevents users from doing the following:

==> Adding or changing records to a related table if there is no associated record in the primary table.
==> Cannot change the values in the primary table for which the values already refer in the child table.

4. User-Defined Integrity:
It is used to define specific business rules that do not fall into one of the other integrity constraints. All the above integrity constraints support user defined constraints. We can create column level and table level constraints in CREATE table, Stored Procedures and Triggers.

Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: