Как сделать связь многие ко многим в sql

MS SQL Server — связь «многие-ко-многим».

Здравствуйте! Имеем 2 таблицы: «Книга», «Автор». Подразумевается, что один автор может быть авторами многих кних и 1 книга может быть написана несколькими авторами. Я предполагаю тут связь многие-ко-многим. Как учит МСДН, сделал промежуточную таблицу.. а дальше методом тыка т.к. начиная с пункта пятого (Построение связи «многие ко многим») я его не понял. В итоге у меня получается что 1 книга может быть написана несколькими авторами, но 1 автор по прежнему может написать только 1 книгу. Прилагаю код создания таблиц. MS Server 2005 Standart.

CREATE TABLE Author ( AuthorID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, AuthorFamilyName VARCHAR(100), AuthorName VARCHAR(50), AuthorPatronymicName VARCHAR(100), AuthorFIO VARCHAR(100) ) --список издательств -- 1 книга - 1 издательство CREATE TABLE Publisher ( PublisherID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, PublisherName VARCHAR(100) ) -- Обеспечение связи многие-ко-многим (авторы и книги) CREATE TABLE AuthorsBooks ( --AuthorsBooksID INT NOT NULL PRIMARY KEY, AuthorID INT NOT NULL PRIMARY KEY, BookID INT ) --Информация о книге CREATE TABLE Book ( BookID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, BookTitle VARCHAR(100) NOT NULL, BookAuthor INT NOT NULL FOREIGN KEY REFERENCES AuthorsBooks, --ссылка на таблицу авторов BookYear INT, BookQuantityPages INT, BookPublisher INT NOT NULL FOREIGN KEY REFERENCES Publisher-- ссылка на таблицу издательства )

Т.е. в промежуточнок таблицу 2 колонки — книгаИДН и АвторИДН, первичный ключ здесь Автор у меня. Подскажите, в чем я не прав?

Пример связи многие-ко-многим (PostgreSQL)

Связь многие-ко-многим – это связь, при которой множественным записям из таблицы (A), могут соответствовать множественные записи из таблицы (B).

Пример такой связи, люди и их счета в банках.

Например, есть таблица людей (person) и есть таблица с банками (bank). У человека может быть счет в одном банке, или нескольких, а может вообще не быть, как на ER-диаграмме, указанной на рисунке ниже.

Таблицы person, bank, person_bank

Связь многие-ко-многим, между таблицами person и bank осуществляется при помощи третьей таблицы person_bank, у которой 2 внешних ключа на таблицы person и bank соответственно. Чтобы связь пользователь-банк не повторялась дважды, на поля person_id и bank_id создается уникальное ограничение или проще ключ.

CREATE TABLE person ( id SERIAL PRIMARY KEY, full_name VARCHAR ); CREATE TABLE bank ( id SERIAL PRIMARY KEY, name VARCHAR ); CREATE TABLE person_bank ( id BIGSERIAL PRIMARY KEY, person_id INTEGER NOT NULL REFERENCES person, bank_id INTEGER NOT NULL REFERENCES bank, UNIQUE (person_id, bank_id) );

Данные

Люди и их связь с банками.

INSERT INTO bank (id, name) VALUES (1, ‘Сбер’), (2, ‘Тинькофф’), (3, ‘Райффайзен’), (4, ‘ВТБ’); INSERT INTO person (id, full_name) VALUES (1, ‘Иванов Сидор Петрович’), (2, ‘Сидоров Петр Иванович’), (3, ‘Петров Иван Сидорович’), (4, ‘Наличный Артем Андреевич’); INSERT INTO person_bank (person_id, bank_id) VALUES (1, 1), (1, 3), (2, 2), (2, 3), (2, 4), (3, 1), (3, 4);

Связь многие-ко-многим

Выберем всех людей и их банки.

SELECT p.full_name AS person_full_name, b.name AS bank_name FROM person p LEFT JOIN person_bank pb ON pb.person_id = p.id LEFT JOIN bank b ON b.id = pb.bank_id;

В результате будут возвращены следующие данные:

person_full_name	bank_name
Иванов Сидор Петрович	Сбер
Иванов Сидор Петрович	Райффайзен
Сидоров Петр Иванович	Тинькофф
Сидоров Петр Иванович	Райффайзен
Сидоров Петр Иванович	ВТБ
Петров Иван Сидорович	Сбер
Петров Иван Сидорович	ВТБ
Наличный Артем Андреевич

Как видно в таблице выше, у Иванова и Петрова по 2 банка, у Сидорова 3 у Наличного нет связи с банками вообще.

✖ ❤ Мне помогла статья нет оценок
11364 просмотра 6 комментариев Артём Фёдоров 22 января 2022

mysql Связь «Многие ко Многим» — пример SQL кода таблиц с пояснениями. Таблица связи (ON DELETE CASCADE). Получение данных

Участники подают заявки на участие в номинациях в каком-то онлайн конкурсе — один участник может участвовать в любом числе номинаций (их много) и самих участников тоже может быть сколько угодно (тоже много) — а значит, здесь надо реализовать связь многие-ко-многим.

Далее будет использоваться синтаксис mysql.

Проектируем базу для связи Многие-ко-Многим — sql для создания таблиц

Нам потребуется создать три таблицы:

Таблицу «Заявка»
Таблицу «Номинация»
и т.н. «таблицу связи»

Сделаем это (SQL):

CREATE TABLE `Tickets` ( `ticketID` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Имя участника/название организации', `info` VARCHAR(255) NULL DEFAULT '' COMMENT 'Информация о номинанте', PRIMARY KEY (`ticketID`) ) COMMENT='Заявки учасников конкурса' ENGINE=InnoDB ; CREATE TABLE `Nominations` ( `nominationID` INT(11) NOT NULL AUTO_INCREMENT, `title` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Название номинации', PRIMARY KEY (`nominationID`) ) COMMENT='Номинации конкурса' ENGINE=InnoDB ; CREATE TABLE `Tickets_Nominations` ( `ticket_id` INT(11) NOT NULL, `nomination_id` INT(11) NOT NULL, PRIMARY KEY (`ticket_id`, `nomination_id`), INDEX `ticket_id` (`ticket_id`), INDEX `nomination_id` (`nomination_id`), CONSTRAINT `FK_Nominations` FOREIGN KEY (`nomination_id`) REFERENCES `Nominations` (`nominationID`) ON DELETE CASCADE, CONSTRAINT `FK_Ticket` FOREIGN KEY (`ticket_id`) REFERENCES `Tickets` (`ticketID`) ON DELETE CASCADE ) COMMENT='Таблица связи заявок участников и номинаций конкурса' ENGINE=InnoDB ;

Обратите внимание на:

Свойство «ON DELETE CASCADE» —
это значит, что если будет удалена запись в другой таблице, на которую ссылается данный кортеж (из таблицы связи), то и этот кортеж будет удалён целиком. В данном случае связь удаляется из таблицы если удалено хотя быть что-то одно из двух:
- или заявка, на которую он ссылается
- или номинация, на которую он ссылается

— таким образом мы переносим задачу удаления неактуальный связей с приложения на СУБД.

PRIMARY KEY (`ticket_id`, `nomination_id`),

— это автоматически делает (накладывает ограничение) данную комбинацию двух внешний ключей уникальной в рамках таблицы связи (т.е. уже не получится в данную таблицу два раза написать что «Вася подал заявку в номинацию «Лучший повар»»), на самом деле, в ряде случаев (например, для оперирования удобным численным ключом) можно было бы просто добавить обычный численные первичный ключ, а на пару внешних ключей каждого кортежа таблицы связи наложить требование уникальности (т.н. «уникальный составной индекс») — т.е. сделать нашу таблицу связи немного другой:, итак — таблица связи (другой вариант):

CREATE TABLE `Tickets_Nominations` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `ticket_id` INT(11) NOT NULL, `nomination_id` INT(11) NOT NULL, INDEX `ticket_id` (`ticket_id`), INDEX `nomination_id` (`nomination_id`), CONSTRAINT `FK_Nominations` FOREIGN KEY (`nomination_id`) REFERENCES `Nominations` (`nominationID`) ON DELETE CASCADE, CONSTRAINT `FK_Ticket` FOREIGN KEY (`ticket_id`) REFERENCES `Tickets` (`ticketID`) ON DELETE CASCADE, PRIMARY KEY (`id`), UNIQUE KEY `relation_row_unique` (`ticket_id`,`nomination_id`) ) COMMENT='Таблица связи заявок участников и номинаций конкурса' ENGINE=InnoDB ;

Извлечение данных для связи «многие ко многим» (SELECT)

Возникает логичный вопрос — как же получать данные из базы, используя таблицу связи?
Есть разные варианты для разных ситуаций, которые мы сейчас рассмотрим, но прежде чем проиллюстрировать их, заполните созданные выше таблицы данными с помощью такого sql (чтобы вы тоже могли поэкспериментировать с запросами)

Рассмотрим задачу извлечения участников, связанных с данной номинацией — или короче «номинации, и всех, кто подал в неё заявки» (алгоритм извлечения данных в обратную сторону — т.е. «участик и все его номинации» абсолютно аналогичен).
На практике приходится сталкиваться с двумя базовыми ситуациями:

Извлечение одной сущности номинации и связанных с ней участников
Извлечение списка сущностей номинаций и связанных с каждой из номинаций участников (т.е. фактически список участников для каждого элемента из списка номинаций).

Извлечение связанных (многие-ко-многим) данных для одной сущности

Пусть у нас известен id () номинации и мы хотим получить сведения об этой номинации и всех участниках в ней.
Во-первых, сделать это можно двумя sql запросами:

mysql> SELECT * FROM Nominations WHERE nominationID=4; +--------------+-----------------------------+ | nominationID | title | +--------------+-----------------------------+ | 4 | Лучшее пособие | +--------------+-----------------------------+

SELECT * FROM Tickets_Nominations LEFT JOIN Tickets ON ticket_id = ticketID WHERE Tickets_Nominations.nomination_id = 4;

+-----------+---------------+----------+-------------------------- +----------------------------------------------+ | ticket_id | nomination_id | ticketID | name | info | +-----------+---------------+----------+---------------------------+----------------------------------------------+ | 3 | 4 | 3 | Программирование для всех | Некоммерческая образовательная организация | 4 | 4 | 4 | Юный программист | Кружок для детей в д. Простоквашино | 5 | 4 | 5 | IT FOR FREE | Русскоязычное IT-сообщество с уклоном в web | 6 | 4 | 6 | Саша Петров | Студент 2 курса, автор пособия по SQL

Если вам требуется от массива связанных сущностей только одно поле (напр. имена участников), то решить задачу можно вообще одним sql запросом, используя группировку (GROUP BY) и применимую к группируемым значения колонки функцию конкатенации GROUP_CONCAT():

SELECT Nominations.*, GROUP_CONCAT(Tickets.name SEPARATOR ', ') as participants_names FROM Nominations LEFT JOIN Tickets_Nominations ON Nominations.nominationID = Tickets_Nominations.nomination_id LEFT JOIN Tickets ON Tickets.ticketID = Tickets_Nominations.ticket_id WHERE Tickets_Nominations.nomination_id = 4 GROUP BY Nominations.nominationID;

Получим единственный кортеж:

+--------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+ | nominationID | title | participants_names | +--------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+ | 4 | Лучшее пособие | Программирование для всех, Юный программист, IT FOR FREE, Саша Петров | +--------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+

провели сразу тройной JOIN, как бы поставив таблицу связи между таблицами номинаций и заявок.
нас интересовали имена участников для 4 номинации — поэтому использовали WHERE Tickets_Nominations.nomination_id = 4
Группировка (чтобы в итоге получить только одну строку-кортеж) проходила по id номинации (Nominations.nominationID)
Сконкатенированному полю мы назначили псевдоним (participants_names)

Плюсом такого подхода является то, что в приложении можно использовать готовую строку participants_names, а минусом то, что с этим значением уже нельзя работать как с массивом, явно не преобразовав.

Извлечение списка сущностей со связанными данными

Прежде всего можно:

Cначала извлечь (SELECT) необходимые номинации (или вообще все),
а потом уровне приложения в цикле извлечь связанные данные для каждой номинации отдельно (как это показано выше) — это не оптимальный способ так как он порождает много запросов к БД (так что если список номинаций — — или иных сущностей велик, то и запросы сильно скажутся на суммарном времени выполнении скрипта и нагрузке на процессор)

Key Words for FKN + antitotal forum (CS VSU):

ON DELETE CASCADE
многие ко многим
составной уникальный индекс
пример
каскадное удаление
таблица связи
mySQL
mysql многие ко многим пример
связь многие ко многим sql пример
многие ко многим пример
связь многие ко многим подробное объяснение
как извлекать данные
select многие ко многим

Построение связи «многие ко многим» (визуальные инструменты для баз данных)

Отношения «многие ко многим» позволяют связать каждую строку одной таблицы с несколькими строками другой таблицы и наоборот. Например, отношение «многие ко многим» можно создать для таблиц authors и titles , чтобы связать каждого автора с его книгами и сопоставить каждой книге ее автора. Если создать связь «один ко многим» в любой таблице, получится, что каждая книга сможет иметь только одного автора или каждый автор — только одну книгу.

Для осуществления связей «многие ко многим» между таблицами базы данных применяются связующие таблицы. Они содержат первичные ключевые столбцы двух таблиц, которые необходимо связать. После этого можно создать связи между первичными ключевыми столбцами каждой таблицы и соответствующими столбцами в связующей таблице. В базе данных pubs связующей является таблица titleauthor .

Для создания связи «многие ко многим» между таблицами

Добавьте таблицы, которые должны обладать связью «многие ко многим» в диаграмму базы данных.
Создайте третью таблицу, щелкнув диаграмму правой кнопкой мыши и выбрав Создать таблицу . Эта таблица станет связующей.
В диалоговом окне Выбор имени измените имя, назначенное системой. Например, связующую таблицу для таблиц titles и authors можно назвать titleauthors .
Скопируйте первичные ключевые столбцы обеих таблиц в связующую таблицу. В эту таблицу можно добавить другие столбцы, как в любую другую таблицу.
Создайте первичный ключ в связующей таблице так, чтобы он содержал все первичные ключевые столбцы исходных таблиц. Дополнительные сведения см. в разделе Как Создайте первичные ключи.
Определите отношение «один ко многим» между каждой из первоначальных таблиц и связующей таблицей. Связующая таблица должна находиться на стороне «многих» обоих отношений. Дополнительные сведения см. в разделе Как Создайте связи между таблицами.

Примечание Создание связующей таблицы в диаграмме базы данных не подразумевает ее заполнение данными из связанных таблиц. Дополнительные сведения о вставке данных в эту таблицу см. в разделе Создание запросов вставки результатов (визуальные инструменты для баз данных).

Читать:

Как изменить учетную запись в windows 10 администратора