RelationShips in SQL Server

SQL Server supports 3 types of relationships.

1.       One - One Relationship.

2.       One - Many Relationship.

3.       Many - Many Relationship.

One - One Relationship:

In one to One relationship, one entity in one table can contain only one related entity in another table vice versa.

Let us suppose we have two tables. States information and related capital cities information.

CREATE TABLE dbo.States

(

Pk_State_Id INT IDENTITY PRIMARY KEY,

StateName VARCHAR(255)

);

CREATE TABLE dbo.Capitalcityinfo

(

Pk_CC_Id INT PRIMARY KEY,

CC_Name VARCHAR(255),

Fk_State_Id INT UNIQUE FOREIGN KEY REFERENCES dbo. States(Pk_State_Id)

);

INSERT INTO States VALUES('AP')

INSERT INTO States VALUES('TS')

INSERT INTO States VALUES('Kerala')

INSERT INTO States VALUES('Tamilnadu')

INSERT INTO Capitalcityinfo VALUES (1,'Amaravati',1)

INSERT INTO Capitalcityinfo VALUES (2,'Hyderabad',2)

INSERT INTO Capitalcityinfo VALUES (3,'Tiruvanantapuram',3)

INSERT INTO Capitalcityinfo VALUES (4,'Chennai',4)

SELECT * FROM States

SELECT * FROM Capitalcityinfo

Here we declared Fk_State_Id column in Capitalcityinfo table as Unique foreign key. So, it doesn’t let you to insert more than one value in that column. So, States table can contain only one related row information in Capitalcityinfo table. And in the same way Capitalcityinfo table contains only one related state information in States table.

This is called One-to-One relation.

 If you run Query as below, you can find the relation between the tables.

SELECT S.StateName,CP.CC_Name FROM States S LEFT JOIN Capitalcityinfo CP ON S.Pk_State_Id=CP.Fk_State_Id

Diagrammatic representation.

One – To – Many Information:

In One-to-many relationship, one entity in a table can contains relation with more than one row in another table.

Let us suppose we have one more table, CityInfo.

CREATE TABLE dbo.CityInfo

(

Pk_City_Id INT PRIMARY KEY,

City_Name VARCHAR(255),

Fk_State_Id INT FOREIGN KEY REFERENCES dbo. States(Pk_State_Id)

);

This table contains list of city names of all states which are registered in states table. So, one state can contain more than one City information in Cityinfo table. But, if you take any city that contains only one related state information in states table.

INSERT INTO CityInfo VALUES(1,'Vjw',1)

INSERT INTO CityInfo VALUES(2,'Vizag',1)

INSERT INTO CityInfo VALUES(3,'Narasapuram',1)

INSERT INTO CityInfo VALUES(4,'Hyd',2)

INSERT INTO CityInfo VALUES(5,'Sec',2)

INSERT INTO CityInfo VALUES(6,'Warangal',2)

INSERT INTO CityInfo VALUES(7,'Kocchi',3)

INSERT INTO CityInfo VALUES(8,'Tirucchi',3)

INSERT INTO CityInfo VALUES(9,'Aleppi',3)

INSERT INTO CityInfo VALUES(10,'Madhurai',4)

INSERT INTO CityInfo VALUES(11,'Maadras',4)

INSERT INTO CityInfo VALUES(12,'Tiruvayyuru',4)

If you observe in the above list of cities and states, every state is having more than one related city information in Cityinfo table. But if you take any cityname from CityInfo table, that contains only one related State name in States table.

So here the two tables are in One-to-Many relation ship.

 If you run Query as below, you can find One-To-Many relation between the tables.

SELECT S.StateName,CI.City_Name FROM States S LEFT JOIN CityInfo CI ON S.Pk_State_Id=CI.Fk_State_Id

Diagrammatic representation.

Many-to-Many relationship:

   In Many-to-Many relationship, one entity in a table can contain more than one related row information in second table and second table can contain more than one related rows in first table.

  Let us suppose we have 3 tables. Student and Course and one intermediate table Course_Student. Student table contains the information of all students. In the same way Courses table can contain information of All courses and “Course_Student” acts like a mediator and shows the relation between Student and Course tables.

CREATE TABLE Student(

StudentID INT PRIMARY KEY,

StuentName VARCHAR(100)

)

CREATE TABLE Course(

CourseID INT PRIMARY KEY,

CourseName VARCHAR(100)

)

CREATE TABLE Course_Student(

CourseID INT,

StudentID INT

)

INSERT INTO Student VALUES(1,'Sekhar')

INSERT INTO Student VALUES(2,'Bhanu')

INSERT INTO Student VALUES(3,'Naga')

INSERT INTO Course VALUES(1,'Dotnet')

INSERT INTO Course VALUES(2,'SQLServer')

INSERT INTO Course VALUES(3,'MVC')

INSERT INTO Course_Student VALUES(1,1)

INSERT INTO Course_Student VALUES(1,2)

INSERT INTO Course_Student VALUES(1,3)

INSERT INTO Course_Student VALUES(2,1)

INSERT INTO Course_Student VALUES(2,3)

INSERT INTO Course_Student VALUES(3,2)

INSERT INTO Course_Student VALUES(3,1)

Here if you observe the relations, we can find each student learning more than one course. In the same way, each course is having more than one student.

Many-to-Many relationship it is having.

If you run the below query, you can find that, each course is having more than one student and each student is learning more than one course

SELECT S.StuentName,C.CourseName FROM Student S LEFT JOIN Course_Student CS ON S.StudentID=CS.StudentID

LEFT JOIN Course C ON CS.CourseID=C.CourseID.