Back To Blog

SQL Server interview questions - Can you explain Referential Integrity in SQL Server?

By Shiv Prasad Koirala in SQL
Nov 6th, 2011
7076
0

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 remainsconsistent, where one table has a foreign key reference to the other table which is declared asprimary 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 declaredCustID 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: -