Data Integrity in Microsoft Sql Server

These  are more important concept of  Microsoft Sql Server. Please Read it very Carefully.

Data Integrity:- The Consistency of data is known as data integrity. When we have large amount of data then we could not easily find the data and table from database. Because data were not consistence form.To remove this type of problem we use three integrity constraints. which are given below.

(1)- Entity Integrity:- Entity Integrity insure that each record within a table should be uniquely identify. It means, each entity (data) in table should be unique. each row in table should be unique. To insure the integrity we can use 'Primary key' and 'Unique key constraint'. I will discuss different between Primary key and Unique key constraint later.

Example:- Here i am  going to create and insert data in studentdetails table with primary key constraint.See it:-
Create table studentdetails:-
create table studentdetails(sid int constraint pk_sid primary key,sname varchar(40),sage int)

Insert data in studentdetails:-
insert into studentdetails values(101,'ramesh',22)
insert into studentdetails values(102,'raj singh',20)
insert into studentdetails values(103,'neha',22)

Fetch the studentdetails data:-
select*from studentdetails

See sql database  studentdetails table:-

To know about schema of studentdetails  table -
sp_help studentdetails

See it:-

To know about Primary key constraint details on studentdetails table:-

sp_help constraint studentdetails

see it:-

Note:- Here we have Added Primary key constraint when table is creating.

Description:- Here each record in the table  is unique ,so it follow Entity integrity .

(2)-  Referential Integrity:-  If a table column refer to another table column, then both column(parent, child) have same value as specify within parent column. It also insure that if child records exist then we can not delete the records from parent column.

Example:- We have already created studentdetails table above.Now i am going to create table scource.
See it:-
create table scource(sid int, course varchar(30))

Create foreign key constraint on scource table column:-

alter table scource add constraint fk_sid foreign key(sid) references studentdetails(sid)

Insert the data in scource table:-

insert into scource values(101,'.net')
insert into scource values(102,'php')
insert into scource values(103,'java')

Fetch the scource table data:-

select*from scource

See it:-

To know about  constraint on scource  table -

sp_helpconstraint scource

To show the both table data(studentdetails,scource):-

select *from studentdetails,scource

see it:-

To delete the constraints(primary key or  foreign key) of scource table:-  

alter table scource drop constraint fk_sid 

See it:-

Note:- This is more important concept please read it carefully, if any problem write the comment on my page.

(3)-Domain Integrity:- It insures that values within the column should be according o the "Business logic" (within specify range).To implement this integrity we can specify the 'Check constraint' and 'Default constraint'.

• Constraint:- Constraints can be applied either at the time of table creation or after the table creation.

By creating the table we can applied the constraints at column level and table level.

Example of column level constraints:-

create table studentdetails(sid int primary key,sname varchar(40),sage int).
                                       OR
create table studentdetails(sid int primary key,sname varchar(40),sage int).

Example of table level constraints:-

create table student (sid int, sname varchar(40),sage int,constraint pksid primary key( sid,sname,sage))

check constraint on student table:-

• If  table is already created and we want to create primary key constraints on the column. if column is nullable, we will not able to create primary key constraint. so we need to make it not null first. 

Create  not Null-able  to the student table column:-

alter table student alter column sid int not null

Now apply the constraint on student column:-

alter table student add constraint pk_sid primary key(sid)

For More:-

1. Microsoft Sql Server
2. Create Table Without Command
3. Check constraints
4. Default Constraints
5. Add Identity Column
6. Copy Data from one table to another table
7. Stored Procedure
8.  Setup File

I hope this helpful for you. 
Download Whole Attached file
          DOWNLOAD