SQL many-to-many relationship

A many-to-many relationship in the context of a relational database, such as those managed by SQL (Structured Query Language) databases, refers to a scenario where each record in one table can be associated with multiple records in another table, and vice versa. This type of relationship is common when modeling complex relationships between entities.

To implement a many-to-many relationship, an intermediate table, often called a junction or associative table, is introduced. This table serves as a bridge between the two related tables, facilitating the association between their records. The junction table typically contains foreign keys that reference the primary keys of the two tables involved in the relationship.

Example

Here’s a simple example to illustrate the concept. Consider a scenario where you have a database for a library, and you want to model the relationship between books and authors. A book can have multiple authors, and an author can write multiple books. This is a classic many-to-many relationship.

-- Table to store information about books
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
);

-- Table to store information about authors
CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Junction table to represent the many-to-many relationship
CREATE TABLE book_authors (
    book_id INT,
    author_id INT,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

In this example:

The books table stores information about each book, with a unique book_id as the primary key.
The authors table stores information about each author, with a unique author_id as the primary key.
The book_authors table is the junction table that links books to authors. It contains foreign keys (book_id and author_id) referencing the primary keys of the books and authors tables, respectively. The combination of these foreign keys forms the primary key of the junction table.

To associate a book with one or more authors, you would insert records into the book_authors table, specifying the corresponding book_id and author_id values.

-- Inserting data into the junction table to represent relationships
INSERT INTO book_authors (book_id, author_id) VALUES
(1, 101), -- Book 1 is written by Author 101
(1, 102), -- Book 1 is also written by Author 102
(2, 103); -- Book 2 is written by Author 103

This way, you can efficiently manage and query the relationships between books and authors in a many-to-many scenario. The junction table acts as a link between the two entities, allowing for flexibility and scalability in modeling complex relationships within a relational database.

Example

SQL Tutorial