SQL Server Interview Question #170

What is a many-to-many relationship?

Database Design, Normalization & Advanced SQL Senior Advanced

Detailed Explanation

A many-to-many relationship exists when rows on both sides can relate to multiple rows on the other side. In a relational database, it is normally modeled through a junction table.

For example, a student can enroll in many courses and each course can contain many students. The junction table can use a composite primary key to prevent duplicate relationships.

Code Example

CREATE TABLE dbo.StudentCourses
(
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,
    EnrolledAt DATETIME2 NOT NULL DEFAULT SYSDATETIME(),

    CONSTRAINT PK_StudentCourses
        PRIMARY KEY (StudentId, CourseId)
);