How to implement a many-to-many (M2M) relationship in…

To implement a "many-to-many" relationship in SQL, an intermediate (linking) table is used. This table contains two foreign keys referencing the primary keys of the two tables between which the relationship is established.

Suppose we have tables authors and books, and we want to implement a relationship where one author can write many books, and one book can be written by many authors.

Creating entity tables: Create the main tables authors and books.

-- Authors table
CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Books table
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
);

Creating the linking table: Create the author_book table, which will link authors and books. This table will contain foreign keys referencing author_id and book_id.
```
-- Linking table for Many-to-Many relationship
CREATE TABLE author_book (
    author_id INT,
    book_id INT,
    PRIMARY KEY (author_id, book_id), -- Composite primary key to ensure unique pairs
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);
```
- PRIMARY KEY (author_id, book_id) ensures that each pair (author, book) is unique in the linking table, preventing duplicate relationships.
- FOREIGN KEY constraints ensure referential integrity, guaranteeing that author_id and book_id in the author_book table correspond to existing records in the authors and books tables respectively.

Inserting data: Insert data into the tables.

-- Insert data into authors table
INSERT INTO authors (author_id, name) VALUES
(1, 'Author A'),
(2, 'Author B'),
(3, 'Author C');

-- Insert data into books table
INSERT INTO books (book_id, title) VALUES
(101, 'Book 1'),
(102, 'Book 2'),
(103, 'Book 3');

-- Insert data into linking table (relationships between authors and books)
INSERT INTO author_book (author_id, book_id) VALUES
(1, 101), -- Author A wrote Book 1
(1, 102), -- Author A wrote Book 2
(2, 101), -- Author B wrote Book 1
(3, 103), -- Author C wrote Book 3
(1, 103); -- Author A wrote Book 3

Querying data: To retrieve related data, JOIN operations are used.

Get books written by a specific author:

SELECT b.title
FROM books b
JOIN author_book ab ON b.book_id = ab.book_id
WHERE ab.author_id = 1; -- Books written by author with ID = 1

Get authors of a specific book:

SELECT a.name
FROM authors a
JOIN author_book ab ON a.author_id = ab.author_id
WHERE ab.book_id = 101; -- Authors of the book with ID = 101

Get all books with their authors:

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;

The author_book table is key to implementing M2M, as it stores all pairs of related entities.

How to implement a many-to-many (M2M) relationship in SQL?