Как реализовать связь многие ко многим (М2М) в SQL?

Для реализации связи "многие ко многим" в SQL используется промежуточная (связующая) таблица. Эта таблица содержит два внешних ключа, которые ссылаются на первичные ключи двух таблиц, между которыми устанавливается связь.

Предположим, у нас есть таблицы authors (авторы) и books (книги), и мы хотим реализовать связь, где один автор может написать много книг, и одна книга может быть написана многими авторами.

1. Создание таблиц сущностей: Создаем основные таблицы authors и books.

   -- Таблица авторов
   CREATE TABLE authors (
       author_id INT PRIMARY KEY,
       name VARCHAR(255) NOT NULL
   );
   
   -- Таблица книг
   CREATE TABLE books (
       book_id INT PRIMARY KEY,
       title VARCHAR(255) NOT NULL
   );
   

2. Создание связующей таблицы: Создаем таблицу author_book, которая будет связывать авторов и книги. Эта таблица будет содержать внешние ключи, ссылающиеся на author_id и book_id.

   -- Связующая таблица для связи Many-to-Many
   CREATE TABLE author_book (
       author_id INT,
       book_id INT,
       PRIMARY KEY (author_id, book_id), -- Составной первичный ключ для уникальности пары
       FOREIGN KEY (author_id) REFERENCES authors(author_id),
       FOREIGN KEY (book_id) REFERENCES books(book_id)
   );
   

   • PRIMARY KEY (author_id, book_id) гарантирует, что каждая пара (автор, книга) уникальна в связующей таблице, предотвращая дублирование связей.
   • FOREIGN KEY ограничения обеспечивают ссылочную целостность, гарантируя, что author_id и book_id в таблице author_book соответствуют существующим записям в таблицах authors и books соответственно.

3. Вставка данных: Вставляем данные в таблицы.

   -- Вставка данных в таблицу авторов
   INSERT INTO authors (author_id, name) VALUES
   (1, 'Author A'),
   (2, 'Author B'),
   (3, 'Author C');
   
   -- Вставка данных в таблицу книг
   INSERT INTO books (book_id, title) VALUES
   (101, 'Book 1'),
   (102, 'Book 2'),
   (103, 'Book 3');
   
   -- Вставка данных в связующую таблицу (связи между авторами и книгами)
   INSERT INTO author_book (author_id, book_id) VALUES
   (1, 101), -- Author A написал Book 1
   (1, 102), -- Author A написал Book 2
   (2, 101), -- Author B написал Book 1
   (3, 103), -- Author C написал Book 3
   (1, 103); -- Author A написал Book 3
   

4. Запрос данных: Для получения связанных данных используются операции JOIN.

   • Получить книги, написанные определенным автором:

     SELECT b.title
     FROM books b
     JOIN author_book ab ON b.book_id = ab.book_id
     WHERE ab.author_id = 1; -- Книги, написанные автором с ID = 1
     

   • Получить авторов определенной книги:

     SELECT a.name
     FROM authors a
     JOIN author_book ab ON a.author_id = ab.author_id
     WHERE ab.book_id = 101; -- Авторы книги с ID = 101
     

   • Получить все книги с их авторами:

     SELECT b.title, a.name
     FROM author_book ab
     JOIN books b ON ab.book_id = b.book_id
     JOIN authors a ON ab.author_id = a.author_id;
     

Таблица author_book является ключом к реализации M2M, поскольку она хранит все пары связанных сущностей.