SQL Server interview questions – Explian Referential Integrity in SQL Server?

This is one the typical SQL interview questions and also the favorable question of the interviewers, which has been asked in most of the .NET interviews.

Referential Integrity: – Referential Integrity is a DataBase
concept that ensures the relationship between tables remains
consistent, where one table has a foreign key reference to the other table which is declared as primary key.

In simply words when a relation is maintained between two table’s using primary key and foreign key reference is called as
Referential Integrity.

Let’s see a simple demonstration to understand the concept of Referential Integrity.

In order see it practically you just need to follow the following steps.

Step1: – Let’s first create Two Tables like below diagram with respective relationship.

1. Creating a Customer Table.

Query: -

create table Customer
(
CustID int primary key,
CustomerName varchar(50)
);

In the above table of customer you can see that I have created two columns with one as primary key.

2. Similarly, let’s create second table name as CustomerDetails.

Query: -

create table CustomerDetails 
(CustID int Foreign key references Customer(CustID),
CustDetailsID int primary key,
CustOrders varchar(50)
);

In the above table of CustomerDetails you can see that I have declared CustID as foreign key references to the Customer (CustId) table.

Step2: – Now, let’s Insert some data to both the table’s.

Query: – Inserting into Customer Table.

insert into dbo.Customer(CustID,CustomerName)values(1,'Kalim')
insert into dbo.Customer(CustID,CustomerName)values(2,'Wasim')
insert into dbo.Customer(CustID,CustomerName)values(3,'Salim')
insert into dbo.Customer(CustID,CustomerName)values(4,'Nadeem')

Query: – Inerting into CustomerDetails Table.

insert into dbo.CustomerDetails(CustID ,CustDetailsID ,CustOrders ) values(1,1,'Pizza')
insert into dbo.CustomerDetails(CustID ,CustDetailsID ,CustOrders ) values(1,2,'Pepsi')
insert into dbo.CustomerDetails(CustID ,CustDetailsID ,CustOrders ) values(3,3,'Veg-Roll')
insert into dbo.CustomerDetails(CustID ,CustDetailsID ,CustOrders ) values(2,4,'Chicken-Pizza')

Note: – When there is Referential Integrity between two table’s then
you cannot delete record from the respective table.

Step3: – Let’s see a example to prove the above mentioned note.

So, let’s try to delete record from the Customer table and see
what is the output.

Query: -

delete from Customer where CustID = 1

As soon as you click on execute you will output result like
below diagram.

In the above output result diagram you can clearly see that the compiler does not allow deleting record from the table. Which means that, when there is relation maintains between two table’s using Referential  Integrity you cannot delete records from the respective tables.

See the following video on the differences between unique key and primary key as follows: -


Get more on SQL Server interview questions

Regards,

See for author’s other blog on SQL Server interview questions

About these ads

About c# and .NET Interview questions

This blog is for developers who want to crack .NET and C# interviews. It has all tips and tricks needed to crack .NET interviews , C# interview , SQL Server interview , Java interview , WCF Interview , Silverlight interview , WPF interview , LINQ interview , Entity framework Interview.
This entry was posted in Uncategorized and tagged , , , , , , , , . Bookmark the permalink.

One Response to SQL Server interview questions – Explian Referential Integrity in SQL Server?

  1. Pingback: SQL Server 2012 interview questions: – What are Sequence objects? | One stop place for c# ,.NET , SQL Server interview questions

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