Как правильно составить запрос на создание промежуточной таблицы многие-ко-многим?
Здравствуйте!
Есть две таблицы "магазин" и "товар". У них есть свои неключевые поля.
CREATE TABLE STORE ( store_ID INT NOT NULL AUTO_INCREMENT, name varchar(100) NOT NULL, PRIMARY KEY (store_ID ) ); CREATE TABLE ITEM ( item_ID INT NOT NULL AUTO_INCREMENT, name varchar(100) NOT NULL, PRIMARY KEY (item_ID) ); |
CREATE TABLE STORE ( store_ID INT NOT NULL AUTO_INCREMENT, name varchar(100) NOT NULL, PRIMARY KEY (store_ID ) ); CREATE TABLE ITEM ( item_ID INT NOT NULL AUTO_INCREMENT, name varchar(100) NOT NULL, PRIMARY KEY (item_ID) );
Много разных магазинов и много разных товаров.
Это дает третью таблицу для реализации связи "многие-ко-многим".
НО! Не ясно, как именно должен выглядеть запрос при создании такой таблицы.
У меня написано два варианта:
CREATE TABLE STORE_ITEM ( store_ID INT NOT NULL, item_ID INT NOT NULL, PRIMARY KEY (store_ID, item_ID) FOREIGN KEY (store_ID) REFERENCES STORE(store_ID) FOREIGN KEY (item_ID) REFERENCES ITEM(item_ID) ); |
CREATE TABLE STORE_ITEM ( store_ID INT NOT NULL, item_ID INT NOT NULL, PRIMARY KEY (store_ID, item_ID) FOREIGN KEY (store_ID) REFERENCES STORE(store_ID) FOREIGN KEY (item_ID) REFERENCES ITEM(item_ID) );
или
CREATE TABLE STORE_ITEM ( store_item_ID INT NOT NULL AUTO_INCREMENT, store_ID INT NOT NULL, item_ID INT NOT NULL, PRIMARY KEY (store_item_ID, store_ID , item_ID) FOREIGN KEY (store_ID) REFERENCES STORE(store_ID) FOREIGN KEY (item_ID) REFERENCES ITEM(item_ID) ); |
CREATE TABLE STORE_ITEM ( store_item_ID INT NOT NULL AUTO_INCREMENT, store_ID INT NOT NULL, item_ID INT NOT NULL, PRIMARY KEY (store_item_ID, store_ID , item_ID) FOREIGN KEY (store_ID) REFERENCES STORE(store_ID) FOREIGN KEY (item_ID) REFERENCES ITEM(item_ID) );
ВОПРОСЫ:
1. Какой вариант и в каком случае должен применяться?
2. Первый вариант подходит для промежуточной таблицы, а второй для самостоятельной сущности где могут быть свои неключевые поля?
3. Приемлемо ли для сущности-потомка все поля foreign keys указывать точно так, как они указаны у родителя или лучше добавлять префикс? Например, для третьей таблицы из примеров: store_ID или store_item_store_ID?
Дополнительно:
Ответы:
Если сочетание (store_ID, item_ID) уникально, то достаточно первого варианта
Если нет, то второй, только вот без этого извращения PRIMARY KEY (store_item_ID, store_ID , item_ID), зачем тут оно? достаточно просто PRIMARY KEY(store_item_ID)
Ну и по названиям, достаточно во всех таблицах первичный ключ назвать просто ID, а не store_ID, item_ID и извращений уровня store_item_ID
-
только вот без этого извращения PRIMARY KEY (store_item_ID, store_ID , item_ID), зачем тут оно?
ну как зачем? составной первичный ключ из первичных ключей родительских сущностей
- Filarru, у тебе и так уже store_item_ID уникален, зачем еще что-то добавлять?
- Everything_is_bad, о том и вопрос.
Где и как тогда должен применяться составной ключ?
В каких именно случаях в промежуточную таблицу добавляется её собственный ключ, помимо ключей родителей?
Опять же, если это взаимозаменяемые понятия, тогда не ясна суть миграции родительских ключей в составной ключ потомка. - Filarru, я тебе вот прям про это и написал в ответе.
- Everything_is_bad, просто я с трудом тогда понимаю суть логической структуры
Таблица МАРКА или МАРКА_В_КОЛЛЕКЦИИ
Тут что, тоже получается все обходится одним лишь уникальным ключом?
Но зачем тогда родительские ключи (FK) в зоне ключевых атрибутов у потомка, если потомок итак имеет свой уникальный ключ? - Filarru, без понятия, отвык я от таких схем описания, давай реальный DDL, да и в реальной жизни, такие конструкции без нормально ТЗ, можно не рассматривать. По секрету, академически задачи для SQL, очень сильно оторваны от реальной жизни.
-
достаточно во всех таблицах первичный ключ назвать просто ID, а не store_ID, item_ID
Вот это сомнительная рекомендация.
Во-первых, при таком именовании сразу очень хорошо видно поля-ссылки на другие таблицы.
Во-вторых, при связывании гораздо проще (и опять же нагляднее) написать USING (table1_id), чем строгать ON table1.id = table2.table1_id. Особенно актуально для комплексных запросов, где и без того достаточно чего в голове держать.
В третьих, не возникает интерференции имён полей.и извращений уровня store_item_ID
А вот для non-entity таблиц - согласен.
- Akina, а вот кстати это вытекает от того как генерируют SQL, у меня уже давно 98% через ORM, поэтому нет проблем с table1.id = table2.table1_id, там даже лучше чтобы названия были проще, поэтому и ID.
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Для создания промежуточной таблицы многие-ко-многим в базе данных следует выполнить несколько шагов. Допустим, у нас есть две таблицы: таблица "users" и таблица "roles", и нам нужно создать промежуточную таблицу для установления связи между пользователями и их ролями.
1. Создайте таблицу для промежуточных данных. Для этого используйте SQL-запрос CREATE TABLE, указав необходимые поля, например, "user_id" и "role_id". Пример запроса:
CREATE TABLE user_roles ( user_id INT, role_id INT );
2. Добавьте в промежуточную таблицу внешние ключи, чтобы связать ее с таблицами "users" и "roles". Это позволит обеспечить целостность данных и соблюдение ограничений ссылочной целостности. Пример запроса:
ALTER TABLE user_roles ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id), ADD CONSTRAINT fk_role_id FOREIGN KEY (role_id) REFERENCES roles(id);
3. Теперь можно вставить данные в промежуточную таблицу, указывая соответствующие значения для полей "user_id" и "role_id". Например:
INSERT INTO user_roles (user_id, role_id) VALUES (1, 1), (1, 2), (2, 2);
Таким образом, вы создали промежуточную таблицу многие-ко-многим для связи пользователей и их ролей. Не забудьте проверить корректность запросов и обеспечить правильные соответствия между данными в таблицах.