Data Integrity In SQL Server

Data Integrity is used to maintain accuracy and consistency of data in a table.

Classification of Data Integrity

  1. System/Pre Defined Integrity
  2. User-Defined Integrity
Classification of Data Integrity 

System/Pre Defined Integrity

We can implement this using constraints. This is divided into three categories.

System/Pre Defined Integrity 

Entity Integrity

Entity integrity ensures each row in a table is a uniquely identifiable entity. We can apply Entity integrity to the Table by specifying a primary key, unique key, and not null.

Referential Integrity

Referential integrity ensures the relationship between the Tables.

We can apply this using a Foreign Key constraint.

Domain Integrity

Domain integrity ensures the data values in a database follow defined rules for values, range, and format. A database can enforce these rules using Check and Default constraints.

Constraints

Constraints are used for enforcing, validating, or restricting data.

Constraints are used to restrict data in a Table.

Constraints in SQL Server

Default

Default Constraint is used to assign the default value to the particular column in the Table.

By using this constraint we can avoid the system-defined value from a column while the user inserts values in the Table.

A Table can contain any number of default constraints.

Default constraints can be applied on any datatypes.

Example

  1. Create table Demo(Id int,name varchar(50),Salary int default 15000)  

Unique

Unique constraints are used to avoid duplicate data in a column but accept null values in the column. 

It also applies to any datatypes.

A Table can contain any number of unique constraints.

  1. Create table demo1(id int unique,name varchar(50),price int unique)  

Not Null

It avoids null values from column-accepted duplicate values.

It can apply on any datatype.

A Table can contain any number of not null constraints.

Example

  1. Create table Demo2(id int not  null, age int)  

Important Points to Remember

Unique and Not Null constraints have their own disadvantage, that is accepting null and duplicate values into the Table. So to overcome the above drawbacks we write the combination of Unique and Not Null on a column.

Example

Create table demo 3

Check

It is used to verify or check the values with the user-defined conditions on a column.

It can apply on any datatypes.

A Table can contain any number of Not Null constraints.

Example

  1. Create table demo4(id int, Age int check(Age between 18 and 24))  

Primary key

Primary key adds features of unique and not null constraints.

By using primary key we can avoid duplicate and null values for the column.

It can apply on any datatype like int, char, etc.

A table can contain one primary key only.

Example

  1. Create table demo5(id int primary key, salary money)  

Composite primary key

If a primary key is created on multiple columns the composite key can apply to a maximum of 16 columns in a table.

Example

  1. create table demo6(id int,name varchar(50),primary key(id,name))  
Important points to remember,
  1. We can apply only a single primary key in a Table.
  2. We can apply the primary key constraint on multiple columns in a Table.
  3. The primary key is also called the composite key and candidate key.

Foreign Key

Most important part of the database is to create the relationship between the database Table.

The relationship provides a method for link data stored in two or more Tables so that we can retrieve data in an efficient way and verify the dependency of one table data in other Table.

Important Rules to Create Foreign Key constraint

In order to create a relation between multiple tables, we must specify a Foreign key in a Table that references a column in another Table which is the primary key column.

We require two tables for binding with each other and those two tables have a common column name and those columns should be the same datatype.

  • If a table contains a primary key then it can be called parent Table.
  • If a Table contains a foreign key reference then it can be called a Child Table.

We can apply the foreign key reference on any datatypes.

By default foreign key accepts duplicate and null values.

We can apply a maximum of 253 foreign keys on a single table.

Example

Step 1

  1. Create table employee(id int primary key,name varchar(50),age int)  

Step 2

  1. Create table company(email varchar(50),address varchar(50),id int primary key foreign references employee(id))  

Now, check the relation between the two tables. Click the database name, click database diagrams, click on new database diagrams, and select table employee and company and see the relation between these tables.

Relation Between Tables 

If we want to delete or update the record in the foreign key child table then we need to follow some rules.

For delete

It is used to delete key values in the parent table which is referenced by the foreign key in other tables. All rows that contain those foreign key in child table are deleted.

For Update

It is used to update a key value in the parent table which is referenced by the foreign key in another table. All rows that contain the foreign keys in the child table are also updated.

Using the Foreign key we can maintain three types of relationships,

  1. One to one 
  2. One to many 
  3. Many to many 

One to one

A row in a Table associated with a row in the other Table is known as one to one relationship.

One to many or many to one

A row in a Table associated with any number of rows in the other Table is known as a one-to-many relationship.

Many to many relationship

Many rows in a table are associated with many rows in the other Table. This is called a many to many relationship