Types and examples of relationship in Sql Server

In SQL Server, there are several types of relationships that can be established between tables in a database. These relationships help to define the way data is stored and retrieved in a relational database. The main types of relationships are:

One-to-One (1:1) Relationship:

In this type of relationship, one record in a table is related to one record in another table. An example could be a database that stores employee information and each employee has only one office location.

CREATE TABLE Employee (
EmployeeID int PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
OfficeID int UNIQUE
)

CREATE TABLE Office (
OfficeID int PRIMARY KEY,
OfficeName varchar(50)
)

ALTER TABLE Employee
ADD CONSTRAINT FK_Employee_Office FOREIGN KEY (OfficeID) REFERENCES Office(OfficeID)

One-to-Many (1:N) Relationship:

In this type of relationship, one record in a table is related to many records in another table. An example could be a database that stores customers and their orders, where each customer can have many orders.

CREATE TABLE Customer (
CustomerID int PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
Email varchar(50)
)

CREATE TABLE Order (
OrderID int PRIMARY KEY,
OrderDate datetime,
Amount decimal(10,2),
CustomerID int,
CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
)

Many-to-One (N:1) Relationship:

This is the inverse of the One-to-Many relationship, where many records in a table are related to one record in another table. An example could be a database that stores orders and their customers, where many orders are associated with a single customer.

CREATE TABLE Order (
OrderID int PRIMARY KEY,
OrderDate datetime,
Amount decimal(10,2),
CustomerID int
)

CREATE TABLE Customer (
CustomerID int PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
Email varchar(50),
CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerID) REFERENCES Order(CustomerID)
)

Many-to-Many (N:N) Relationship:

In this type of relationship, many records in one table are related to many records in another table. An example could be a database that stores students and their classes, where each student can be enrolled in many classes and each class can have many students.

CREATE TABLE Student (
StudentID int PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50)
)

CREATE TABLE Class (
ClassID int PRIMARY KEY,
ClassName varchar(50)
)

CREATE TABLE StudentClass (
StudentID int,
ClassID int,
PRIMARY KEY (StudentID, ClassID),
CONSTRAINT FK_StudentClass_Student FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
CONSTRAINT FK_StudentClass_Class FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
)