Как удалить связь между таблицами sql
Надеюсь из прошлых, почти полностью теоретических, изложений стало немного яснее, что же все-таки есть реляционные данные и все, что с ними связано. Давайте сейчас попробуем просмотреть все это практически. Наша с вами учебная БД в схеме miller содержит, пять таблиц. Все они в принципе отвечают требованиям 3НФ. Но, когда я их создавал, я не связал столбцы этих таблиц между собой с помощью стандартных средств. А, вот сейчас давайте мы с вами это сделаем. Итак для примера организуем связь, которая чаше всего рекомендована к применению, типа один-ко-многим. Ярким примером для построения такой связи служит две из наших пяти таблиц это CUSTOMERS и SALESREPS. Оператор CREATE TABLE их DDL определений записан следующим образом:
Таблица CUSTOMERS:
Таблица SALESREPS:
При просмотре данных, этих таблиц почти сразу видно, что столбец таблицы SALESREPS — EMPL_NUM есть отношение один-ко-многим столбца CUST_REP для таблицы CUSTOMERS. Для определения связи между таблицами воспользуемся оператором ALTER TABLE и запишем вот такую конструкцию:
Все, связь между столбцами таблиц установлена! Все достаточно просто. Теперь действует ограничение ссылочной целостности и нарушить его нам с вами не позволят! Можно убедиться в этом. Столбец таблицы SALESREPS — EMPL_NUM содержит следующее множество значений 101 .. 110 и отдельно 120. Попробуйте что-нибудь вроде:
После ввода, получаем:
Естественно ошибка ORA-02291! А все потому, что множество 101 .. 110 и отдельно 120 не содержит числа 150! И по этому в данном случае не допустимо! Вот и получилось жесткое отношение один-ко-многим! Так же, можно и удалить связь, между столбцами таблиц применив оператор DROP. Но, нужно узнать имя ссылочной целостности в системе. Сейчас мы его знаем благодаря ошибке. А что если, в процессе работы нужно удалить ссылочную целостность, а потом снова восстановить ее! Для этого обратимся к представлению в вашей схеме USER_CONSTRAINTS. Оно содержит все имена ваших ограничений. Дадим такой запрос:
Там где поле CONSTRAINT_TYPE содержит значение R и есть наше ограничение (по моему от REFERENCES, точно не помню!) Получаем имя ограничения — SYS_C003548 (тоже номер был и в ошибке помните?). Вот теперь давайте от него избавимся:
Вот теперь ограничение снято. Повторим наш предыдущий запрос и посмотрим, что содержит USER_CONSTRAINTS сейчас:
Хорошо видно, что осталось только ограничение первичного ключа таблицы CUSTOMERS имеющее имя SYS_C003506. Кроме того, таблица может содержать ограничение на саму себя например все с той же таблицей SALESREPS можно проделать следующее:
Теперь таблица, как бы это лучше сказать — «самоограничилась», хотя это не всегда оправдано, но вполне применимо и может использоваться! Можете сами с этим всем поработать и определить приоритеты, при проектировании БД, оптимизации и определении ссылочных целостностей таблиц! Но, слишком не увлекайтесь, границы сознания не бесконечны и не стоит выходить за границы понимания, а уж во всяком случае выпускать за них свою БД. 🙂
Связи таблиц SQL
Когда речь заходит о реляционных базах данных, читатели представляют себе множество таблиц переплетенных связями и зависимостями. Так оно, по сути, и есть. База данных не может состоять из отдельных таблиц без какой либо связи между собой. Сегодня мы узнаем: какие существуют связи в мире реляционных баз данных, научимся из различать и программировать.
После того как мы научились создавать и работать с таблицами, необходимо понять: как связывать их между собой. Ведь по отдельности они почти не несут полезной информации.
Существуют такие типы связей между таблицами в SQL:
Мы продолжим работать с реляционной моделью магазин обуви, которую создали в рамках цикла статей по SQL. Для тех, кто тут впервые я напомню, что у нас для примера очень упрощенная и примитивная предметная область, по которой мы учим SQL. Вот ее модель:
Для полного понимания темы советую ознакомиться с темой ключи sql. Без знания ключей понять примеры будет сложно.
Связь один ко многим (one-to-many)
Когда мы говорим о связи один ко многим то это означает: что в одной записи в таблице может отвечать множество записей другой таблицы. Например у одного поставщика может быть много обуви. Когда мы создавали таблицу shoes (обувь), то указывали поставщика именно в связи один ко многим:
Чтобы еще раз продемонстрировать Вам связь один ко многим, предположим, что поле size подразумевает нечто больше чем просто число о размере обуви. Ведь если магазин будет работать по всему миру, то единого стандарта размеров не будет. Пусть size будет ссылкой на отдельную таблицу, которую так и назовем: shoes_size. В ней для примера будут поля: size_id, european_size, american_size. Модифицируем таблицу согласно обновленных условий:
Теперь, у нас таблица shoes ссылается на таблицы supplier и size по связи многие к одному: много обуви может иметь одного поставщика и один размер. И наоборот: один поставщик может иметь много обуви; один размер может быть у большого количества обуви.
Один к одному (one-to-one)
Связь один к одному это когда одной записи в таблице отвечает только одна запись из другой таблицы. Чтобы продемонстрировать связь один к одному возьмем таблицу supplier:
и вынесем колонку full_address в отдельную таблицу. Таким образом в таблице supplier будет ссылка на таблицу address, в которой будут такие поля: address_id, coutry, city, street.
Не теряя времени сделаем нужные изменения.
Так как поле full_address будет ссылкой на таблицу address сделаем его тип целочисленным.
Далее создадим саму таблицу address.
Теперь укажем, что поле full_address будет внешним ключом:
Связь один к одному означает, что в таблице supplier будет уникальный идентификатор записи с таблицы address. Поэтому, нужно сделать поле full_address уникальным:
Теперь, каждый адрес будет относиться только к одному поставщику и все попытки добавить поставщику адрес другого поставщика будет приводить к ошибке.
Многие ко многим (many-to-many)
Связь многие ко многим подразумевает, что записи в одной таблице могут иметь множество ссылок на другую таблицу и наоборот. Когда есть такой тип связи нужно создавать дополнительную таблицу, которая сведет связь многие ко многим до связи один ко многим.
Для примера предположим, обувь может быть нескольких типов (кроссовки-кеды или туфли-мокасины). Может пример и не самый удачный, однако для тренировочных целей будет то что нужно.
Для тех, кто все еще не понял: мы хотим чтобы поле type в таблице shoes было ссылкой на некоторую таблицу. Притом не просто ссылкой, а несколькими ссылками. На первый взгляд кажется что это не реально. Пример все пояснит.
Создаем таблицу type с полями: type_id, name:
Теперь удаляем поле type с таблицы shoes:
Далее создаем таблицу shoes_type с полями type_id, shoes_id, который будут ссылками на таблицы type и shoes соответственно.
Вот так просто можно смоделировать отношение многие ко многим.
Мы не плохо так оптимизировали нашу базу данных. Желательно делать это на стадии разработки реляционной модели, а не после ее создания. Наши оптимизации местами могут показаться не совсем логичными и нужными. В данном случаи мы их делали только для изучения отношений между таблицами SQL. На деле же оптимизацию нужно проводить более тщательно. Потому что база данных – костяк (фундамент) любого приложения.
На этом пока все. Следите за новыми уроками по SQL, не забывайте учить Java и комбинируйте все это в многослойных веб приложениях.
6 thoughts on “Связи таблиц SQL”
Спасибо за Ваш труд, очень классное описание пошаговое по SQL. Без воды и всякой липы.
Остановился после того, как увидел у Вас, что после текста:
“Связь один к одному означает, что в таблице supplier будет уникальный идентификатор записи с таблицы address. Поэтому, нужно сделать поле full_address уникальным”
Идет не та строка, которую нужно выполнить, чтобы сделать уникальным значение в таблице supplier.
Я сделал этот пункт следующим образом:
alter table supplier modify column full_adress int not null unique;
Спасибо за замечание. Вы все верно сделали. Можно было еще сделать поле первичным ключем. Это как вариант.
Спасибо за ответ. Очень много полезных статей, спасибо Вам за труд. Читаю и читаю ��
В нашем случае получается, что unique – самый подходящий вариант, потому как первичный ключ подразумевает, что в будущем эта колонка будет использоваться другой таблицей с помощью внешнего ключа – правильно ли я понимаю?
В принципе, unique можно здесь и не делать если представить например, что 2 фирмы имеют одинаковый адрес. Но все же Вы правы: unique будет здесь наиболее уместно. Поскольку первичный ключ может быть только один, то если делать address_id уникальным с помощью ключа – нужно составлять тогда композитный ключ. Так как у нас первичным ключом выступает айди. Поэтому да: unique здесь наиболее уместно
Совет, в конце статьи сделайте типа “нашли ошибку выделите их Ctrl+Shift (или как там)” а то у вас много орфографических ошибок.
mysql Связь "Многие ко Многим" — пример SQL кода таблиц с пояснениями. Таблица связи (ON DELETE CASCADE). Получение данных
Участники подают заявки на участие в номинациях в каком-то онлайн конкурсе — один участник может участвовать в любом числе номинаций (их много) и самих участников тоже может быть сколько угодно (тоже много) — а значит, здесь надо реализовать связь многие-ко-многим.
Далее будет использоваться синтаксис mysql.
Проектируем базу для связи Многие-ко-Многим — sql для создания таблиц
Нам потребуется создать три таблицы:
- Таблицу «Заявка»
- Таблицу «Номинация»
- и т.н. «таблицу связи»
Сделаем это (SQL):
Обратите внимание на:
- Свойство «ON DELETE CASCADE» —
это значит, что если будет удалена запись в другой таблице, на которую ссылается данный кортеж (из таблицы связи), то и этот кортеж будет удалён целиком. В данном случае связь удаляется из таблицы если удалено хотя быть что-то одно из двух:- или заявка, на которую он ссылается
- или номинация, на которую он ссылается
— таким образом мы переносим задачу удаления неактуальный связей с приложения на СУБД.
— это автоматически делает (накладывает ограничение) данную комбинацию двух внешний ключей уникальной в рамках таблицы связи (т.е. уже не получится в данную таблицу два раза написать что «Вася подал заявку в номинацию «Лучший повар»»), на самом деле, в ряде случаев (например, для оперирования удобным численным ключом) можно было бы просто добавить обычный численные первичный ключ, а на пару внешних ключей каждого кортежа таблицы связи наложить требование уникальности (т.н. «уникальный составной индекс») — т.е. сделать нашу таблицу связи немного другой:, итак — таблица связи (другой вариант):
Извлечение данных для связи «многие ко многим» (SELECT)
Возникает логичный вопрос — как же получать данные из базы, используя таблицу связи?
Есть разные варианты для разных ситуаций, которые мы сейчас рассмотрим, но прежде чем проиллюстрировать их, заполните созданные выше таблицы данными с помощью такого sql (чтобы вы тоже могли поэкспериментировать с запросами)
Рассмотрим задачу извлечения участников, связанных с данной номинацией — или короче «номинации, и всех, кто подал в неё заявки» (алгоритм извлечения данных в обратную сторону — т.е. «участик и все его номинации» абсолютно аналогичен).
На практике приходится сталкиваться с двумя базовыми ситуациями:
- Извлечение одной сущности номинации и связанных с ней участников
- Извлечение списка сущностей номинаций и связанных с каждой из номинаций участников (т.е. фактически список участников для каждого элемента из списка номинаций).
Извлечение связанных (многие-ко-многим) данных для одной сущности
Пусть у нас известен id () номинации и мы хотим получить сведения об этой номинации и всех участниках в ней.
Во-первых, сделать это можно двумя sql запросами:
- Сначала просто получим кортеж этой номинации:
- После, опять же зная id номинации (используем в WHERE), достаточно просто сделать LEFT JOIN между таблицей связи и таблицей участников:
— как видим, тут мы получили вообще все колонки (т.к. в запросе указали звездочку *) двух соединённых таблиц (связи и заявок).
Также видим что на номинации с номинировалось 4-ре участника, кроме их имен видны также и описания.
Все эти данные можно использовать в приложении, после выполнения запроса к БД — например записать, то что нужно в поле, хранящее массив объекта конкретной номинации.
Если вам требуется от массива связанных сущностей только одно поле (напр. имена участников), то решить задачу можно вообще одним sql запросом, используя группировку (GROUP BY) и применимую к группируемым значения колонки функцию конкатенации GROUP_CONCAT():
Получим единственный кортеж:
- провели сразу тройной JOIN, как бы поставив таблицу связи между таблицами номинаций и заявок.
- нас интересовали имена участников для 4 номинации — поэтому использовали WHERE Tickets_Nominations.nomination_id = 4
- Группировка (чтобы в итоге получить только одну строку-кортеж) проходила по id номинации (Nominations.nominationID)
- Сконкатенированному полю мы назначили псевдоним (participants_names)
Плюсом такого подхода является то, что в приложении можно использовать готовую строку participants_names, а минусом то, что с этим значением уже нельзя работать как с массивом, явно не преобразовав.