Relationship in SQL Server

In this article, we learn about SQL relationship.

• SQL databases are relational databases and they store data and the relationships between the data.

Types of Relationship in SQL Server:

• One to One Relationship
• One to Many Relationship
• Many to Many Relationship
• Self Referencing Relationship

ONE TO ONE

• In a One to One relationship, one record in a table is associated with one and only one record in another table.
• This relationship can be created using Primary key-Unique foreign key constraints.
• A one-to-one relationship can be used for security purposes.
• Example: In a school database, each student has only one student ID, and each student ID is assigned to only one person.
• In the below diagram, we can visualize the relationship between the customer and the address.

EXAMPLE: ONE TO ONE

ONE TO MANY AND MANY TO ONE

• In a One to Many relationships, one record in a table can be associated with one or more records in another table.
• This relationship can be created using Primary key-Foreign key relationships.
• Example: Each Customer can have many Sales Orders.
• Each customer may have zero, one, or multiple orders. But order can belong to only one customer.

EXAMPLE: ONE TO MANY

MANY TO MANY

• A Many to Many Relationship occurs when multiple records in a table are associated with multiple records in another table.
• Example: Customers can purchase various Products, and Products can be purchased by many Customers.
• In the below diagram, we can create many to many relationships between the items and the orders.

EXAMPLE: MANY TO MANY

• If we want to include the items orders records in the graph, it may look like this:

EXAMPLE: MANY TO MANY

SELF REFERENCING

• Self-referencing is used when a table needs to have a relationship with itself.
• Example: Customers can refer other customers to your shopping website.
• This actually can also be a similar one to many relationships since one customer can refer to multiple customers.
• One customer might refer to zero, one, or multiple customers. Each customer can be referred by only one customer or none at all.

EXAMPLE: SELF REFERENCING

If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -