Как сделать связи в sql
jtest.ru
HTML, CSS, JavaScript, JQuery, PHP, MySQL
SQL для начинающих. Часть 3
Представляю Вашему вниманию вольный перевод статьи SQL for Beginners Part 3
Сегодня мы продолжаем наше путешествие в мире SQL и реляционных систем управления базами данных. В этой части мы научимся работать с несколькими таблицами связанными между собой. Сначала мы познакомимся с базовыми концепциями, а потом начнем работать с запросами JOIN в SQL.
Предыдущие статьи
Вступление
При проектировании базы данных, здравый смысл подсказывает нам, что мы должны использовать различные таблицы для разных данных. Пример: клиенты, заказы, записи, сообщения и т.д. Так же мы должны иметь взаимосвязи между этими таблицами. Например, клиент имеет заказы, а у заказа есть позиции (товары). Эти взаимосвязи должны быть отражены в базе данных. А также, когда мы получаем данные с помощью SQL, мы должны использовать определенные типы запросов JOIN, чтобы получить нужный результат.
Вот несколько типов отношений в базе данных. Сегодня мы рассмотрим следующие:
Когда данные выбираются из нескольких связанных таблиц, мы будем использовать запрос JOIN. Есть несколько типов присоединения, мы познакомимся с этими:
Также мы изучим предложения ON и USING.
Связь один к одному
Допустим есть таблица покупателей (customers):
Мы можем расположить информацию о адресе покупателя в другой таблице:
Теперь у нас есть связь между таблицами покупателей (Customers) и адресами (Addresses). Если каждый адрес может принадлежать только одному покупателю, то такая связь называется «Один к одному». Имейте ввиду, что такой тип отношений не очень распространен. Наша первоначальная таблица, в которой информация о покупателе и его адресе хранилась вместе, в большинстве случаев работает нормально.
Обратите внимание, что теперь поле с названием «address_id», в таблице покупателей, ссылается на соответствующую запись в таблице адресов. Оно называется внешним ключом (Foreign Key) и используется во всех видах связей в базе. Мы рассмотрим этот вопрос позже в этой статье.
Вот так можно отобразить отношения между покупателями и адресами:
Обратите внимание, что существование данных отношений не обязательно, например, может существовать запись о покупателе без связанной записи о его адресе.
Связь один ко многим и многие к одному
Этот тип отношений наиболее часто встречающийся. Рассмотрим такой сайт интернет магазина:
В этих случаях нам потребуется создать связь «Один ко многим». Пример:
Каждый покупатель может иметь 0 или более заказов. Но каждый заказ может принадлежать только одному покупателю.
Связь многие ко многим
В некоторых случаях требуется многочисленные связи по обе стороны отношений. Например, каждый заказ может содержать множество товаров. И каждый товар может присутствовать во многих заказах.
Для такой связи нам потребуется создать дополнительную таблицу:
Так можно представить этот тип отношений:
Если добавить записи items_orders к диаграмме, то она будет выглядеть так:
Связь с собой
Такой тип используется когда у таблицы должны быть связь с собой. Допустим у Вас есть реферальная программа. Покупатели могут ссылаться на других покупателей на вашем сайте интернет магазина. Таблица может выглядеть так:
Покупатели 102 и 103 ссылаются на покупателя 101.
Этот тип похож на связь «Один ко многим», поскольку один покупатель может ссылаться на несколько покупателей. Это можно представить как древовидную структуру:
Один покупатель может ссылаться на одного покупателя, на нескольких покупателей, или вообще не ссылаться ни на одного.
Если Вы хотите создать связь внутри таблицы «многие ко многим», то потребуется создать дополнительную таблицу, такую же как и в предыдущей части.
Внешние ключи
Пока что мы говорили только о базовых вещах. Пришло время применить полученные знания на практике, используя SQL. В данной часть нам нужно понять, что из себя представляют внешние ключи.
В отношениях, обсуждаемых выше, у нас всегда было поле вида «****_id», которое ссылалось столбец в другой таблице. В нашем примере столбец customer_id, в таблице Orders, является внешним ключом:
В таких базах как MySQL есть два способа создания внешних ключей:
Задать внешний ключ явно
Создадим простую таблицу с покупателями:
Теперь создадим таблицу заказов, которая будет содержать вторичный ключ:
Оба столбца (customers.customer_id и orders.customer_id) должны быть одного типа. Если у первого тип INT, то второй не должен быть типа BIGINT, например.
Пожалуйста, помните, что в MySQL полностью поддерживает внешние ключи только подсистема InnoDB. Другие подсистемы хранения данных позволяют определять внешние ключи без каких либо ошибок. Столбцы с внешними ключами индексируются автоматически, если явно не задать другой индекс.
Без явного объявления
Некоторые таблицы заказов могут быть созданы без явного определения внешнего ключа:
Когда данные получают запросом JOIN, Вы можете использовать столбец как внешний ключ, хотя база данных не знает о этих связях.
Мы подошли к изучению запросов JOIN, которые обсудим далее в статье.
Отображение связей
В данный момент, моей любимой программой для проектирования баз данных и отображения связей является MySQL Workbench.
После того как Вы спроектировали базу данных, ее можно экспортировать в SQL и выполнить на сервере. Это очень удобно при создании больших и сложных баз данных.
Запросы JOIN
Чтобы получить связанные данные из базы данных следует использовать запросы JOIN.
Прежде чем мы начнем, давайте создадим для работы тестовые таблицы и данные.
У нас есть 4 покупателя. У одного из них два заказа, у двоих по одному заказу, и у одного вообще нет заказов. Теперь давайте посмотрим какие виды запросов JOIN мы можем выполнять с этими таблицами.
Cross Join (Перекрестное объединение)
Это вид JOIN запроса по-умолчанию, если не определено условие.
Результатом будет, так называемое, «Декартово объединение» таблиц. Это означает, что каждая строка из первой таблицы сопоставляется с каждой строкой второй таблицы. Т.к. в каждой таблице по 4 строки, мы получили в результате 16 строк.
Ключевое слово JOIN можно заменить на запятую, в этом случае.
Конечно такой результат почти бесполезен. Давайте взглянем на другие виды объединений.
Natural Join (Естественное объединение)
При таком виде запроса JOIN таблицы должны иметь совпадающие, по имени, столбцы. В нашем случае в обеих таблицах должен присутствовать столбец customer_id. MySQL объединит записи только в случае совпадения значений в этих столбцах.
Как Вы можете видеть, в этот раз столбец customer_id отображаются только один раз, потому что движок базы рассматривает этот столбец как общий. Мы видим два заказа Adam’а, и другие два заказа Joe и Sandy. Наконец мы получили некоторую полезную информацию.
Inner Join (Внутреннее объединение)
Если условие объединения не указан, то выполняется внутреннее объединение. В этом случае хорошей идеей будет наличие совпадений по полю customer_id в обеих таблицах. Результат должен быть аналогичен естественному объединению.
Результат почти такой же. Столбец customer_id повторяется два раза, по разу для каждой таблицы. Объясняется это тем, что мы попросили базу сравнить значение по двум столбцам. При этом не знаю, что возвращают одну и туже информацию.
Добавим побольше условий к запросу.
Предложение ON
Прежде чем перейти к другим видам объединяющих запросов, нам нужно рассмотреть предложение ON. Оно служит для вставки условий JOIN в отдельные предложения.
Теперь мы можем различать условия, относящиеся к JOIN и условия в части WHERE. Но еще есть небольшая разница в функционировании. Мы увидим это, когда перейдем к примерам с LEFT JOIN.
Предложение USING
Предложение USING немного похоже на конструкцию ON. Если столбцы в таблицах называется одинаково, можно указать их здесь.
На самом деле это очень похоже на NATURAL JOIN, т.е. объединяющий столбец (customer_id) не повторяется дважды.
Left (Outer) Join (Левое внешнее соединение)
LEFT JOIN это вид внешнего соединения. В следующем запросе, если не найдены совпадения во второй таблице, записи из первой таблице все равно отобразятся.
Хотя у Andy и нет заказов, эта запись все равно отображается. Значение из второй таблицы равно NULL.
Это полезно, когда нужно найти записи, у которых нет связей. Например, мы можем найти всех покупателей, которые ничего не заказывали.
Отметим, что ключевое слово OUTER не обязательно. Вы можете использовать просто LEFT JOIN вместо LEFT OUTER JOIN.
Условия
Теперь давайте посмотрим на запросы с условиями.
Так, что случилось с Andy и Sandy? LEFT JOIN подразумевает, что мы должны получить покупателей, у которых нет заказов. Проблема в том, что условие WHERE скрывает эти результаты. Чтобы получить их, мы можем попытаться включить условие с NULL.
Появился Andy, но нет Sandy. Выглядит неправильно. Для того чтобы получить то, что мы хотим, нужно использовать предложение ON.
Right (Outer) Join (Правое внешнее соединение)
Объединение RIGHT OUTER JOIN работает также, только порядок таблиц меняется на обратный.
На этот раз мы не получили результатов с NULL, потому что каждый заказ имеет сопоставление с записью покупателя. Мы можем поменять порядок таблиц и получим тот же результат, что и с LEFT OUTER JOIN.
Теперь у нас появились значения NULL, потому что таблица покупателей с правой стороны от объединения.
Заключение
Спасибо за чтение статьи. Надеюсь Вам понравилось!
Создание связей по внешнему ключу
В этой статье описывается, как создать связи внешнего ключа в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL. Связь создается между двумя таблицами, чтобы связать строки одной таблицы со строками другой.
Разрешения
Создание новой таблицы с внешним ключом требует разрешения CREATE TABLE в базе данных и разрешения ALTER на схему, в которой создается таблица.
Создание внешнего ключа в существующей таблице требует разрешения ALTER на таблицу.
Пределы и ограничения
Ограничение внешнего ключа не обязательно должно быть связано только с ограничением первичного ключа в другой таблице. Внешние ключи также могут быть определены, чтобы ссылаться на столбцы ограничения UNIQUE в другой таблице.
Если столбцу, имеющему ограничение внешнего ключа, задается значение, отличное от NULL, такое же значение должно существовать и в указываемом столбце. В противном случае будет возвращено сообщение о нарушении внешнего ключа. Для обеспечения проверки всех значений сложного ограничения внешнего ключа задайте параметр NOT NULL для всех столбцов, участвующих в индексе.
Ограничения FOREIGN KEY могут ссылаться только на таблицы в пределах той же базы данных на том же сервере. Межбазовую ссылочную целостность необходимо реализовать посредством триггеров. Дополнительные сведения см. в статье об инструкции CREATE TRIGGER.
Ограничения FOREIGN KEY могут ссылаться на другие столбцы той же таблицы и считаются ссылками на себя.
Ограничение FOREIGN KEY, определенное на уровне столбцов, может содержать только один ссылочный столбец. Этот столбец должен принадлежать к тому же типу данных, что и столбец, для которого определяется ограничение.
Ограничение FOREIGN KEY, определенное на уровне таблицы, должно содержать такое же число ссылочных столбцов, какое содержится в списке столбцов в ограничении. Тип данных каждого ссылочного столбца должен также совпадать с типом соответствующего столбца в списке столбцов.
Компонент Database Engine не имеет предопределенного ограничения на число ограничений FOREIGN KEY, которые могут содержаться в таблице, ссылающейся на другие таблицы. Компонент Database Engine также не ограничивает число ограничений FOREIGN KEY, принадлежащих другим таблицам, которые ссылаются на определенную таблицу. Но фактическое количество используемых ограничений FOREIGN KEY ограничивается конфигурацией оборудования, базы данных и приложения. Максимальное количество таблиц и столбцов, на которые может ссылаться таблица в качестве внешних ключей (исходящих ссылок), равно 253. SQL Server 2016 (13.x); и последующие версии увеличивает ограничение на количество других таблиц и столбцов, которые могут ссылаться на столбцы в одной таблице (входящие ссылки), с 253 до 10 000. (Требуется уровень совместимости не менее 130.) Увеличение имеет следующие ограничения:
Ограничения FOREIGN KEY не применяются к временным таблицам.
Если внешний ключ определен на столбце определяемого пользователем типа данных CLR, реализация этого типа должна поддерживать двоичную сортировку. Дополнительные сведения об определяемых пользователем типах данных CLR см. в разделе Определяемые пользователем типы данных CLR.
Создание связи по внешнему ключу в конструкторе таблиц
Использование SQL Server Management Studio
В обозревателе объектов щелкните правой кнопкой мыши таблицу, которая будет содержать внешний ключ для связи, и выберите пункт Конструктор.
В меню конструктора таблиц выберите Связи.
В диалоговом окне Связи внешнего ключа нажмите кнопку Добавить.
Выберите нужную связь в списке Выбранные связи.
Выберите Спецификация таблиц и столбцов в сетке справа и нажмите кнопку с многоточием ( … ) справа от свойства.
В диалоговом окне Таблицы и столбы в раскрывающемся списке Первичный ключ выберите таблицу, которая будет находиться на стороне первичного ключа связи.
В сетке внизу выберите столбцы, составляющие первичный ключ таблицы. В соседней ячейке сетки справа от каждого столбца выберите соответствующий столбец внешнего ключа таблицы внешнего ключа.
Закройте окно конструктора таблиц и сохраните внесенные изменения, чтобы изменения связи внешнего ключа вступили в силу.
Определение связей между таблицами с помощью access SQL
Отношения — это установленные связи между двумя или более таблицами. Отношения основаны на общих полях из более чем одной таблицы, часто связанных с первичными и иностранными ключами.
Основным ключом является поле (или поля), которое используется для уникальной идентификации каждой записи в таблице. Для основного ключа существует три требования: он не может быть null, он должен быть уникальным, и в таблице может быть только одно. Основной ключ можно определить либо путем создания индекса основных ключевых элементов после создания таблицы, либо с помощью оговорки CONSTRAINT в декларации таблицы, как показано в примерах, ниже в этом разделе. Ограничение ограничивает (или ограничивает) значения, вступающие в поле.
Иностранный ключ — это поле (или поля) в одной таблице, которое ссылается на основной ключ в другой таблице. Данные в полях из обеих таблиц абсолютно одинаковы, а в таблице с основной записью ключей (основная таблица) должны быть существующие записи перед таблицей с записью иностранного ключа (иностранной таблицей) с соответствующими или связанными записями. Как и основные клавиши, в декларации таблицы можно определить внешние клавиши с помощью оговорки CONSTRAINT.
Существует по сути три типа отношений:
Один к одному Для каждой записи в основной таблице в иностранной таблице имеется только одна запись.
Один к многим Для каждой записи в основной таблице в иностранной таблице имеется одна или несколько связанных записей.
Много-много Для каждой записи в основной таблице имеется много связанных записей в иностранной таблице, а для каждой записи в иностранной таблице имеется много связанных записей в основной таблице.
При определении связей между таблицами необходимо сделать объявления CONSTRAINT на уровне поля. Это означает, что ограничения определяются в заявлении CREATE TABLE. Чтобы применить ограничения, используйте ключевое слово CONSTRAINT после объявления поля, назови ограничение, назови таблицу, на которую ссылается, и назови поле или поля в этой таблице, которые будут соответствовать иностранному ключу.
В следующем заявлении предполагается, что таблица tblCustomers уже построена и что она имеет основной ключ, определенный в поле CustomerID. Теперь в заявлении создается таблица tblInvoices, определяющая ее основной ключ в поле InvoiceID. Кроме того, создается связь между таблицами tblCustomers и tblInvoices, определяя другое поле CustomerID в таблице tblInvoices. Это поле определяется как иностранный ключ, который ссылается на поле CustomerID в таблице клиентов. Обратите внимание, что имя каждого ограничения следует ключевому слову CONSTRAINT.
Обратите внимание, что основной индекс ключа (PK_InvoiceID) для таблицы счетов объявляется в заявлении CREATE TABLE. Чтобы повысить производительность основного ключа, для него автоматически создается индекс, поэтому нет необходимости использовать отдельное заявление CREATE INDEX. Теперь создайте таблицу доставки, которая будет содержать адрес доставки каждого клиента. Предположим, что для каждой записи клиента будет иметься только одна запись доставки, поэтому будет устанавливаться отношение один к одному.
Обратите внимание, что поле CustomerID является как основным ключом для таблицы доставки, так и ссылкой на иностранный ключ в таблице клиентов.
Ограничения
Ограничения можно использовать для создания основных ключей и целостности ссылок, а также для ограничения значений, которые можно вставить в поле. В общем, ограничения можно использовать для сохранения целостности и согласованности данных в базе данных.
Существует два типа ограничений: ограничение на уровне одного поля или поля и ограничение на уровне нескольких полей или таблиц. Оба типа ограничений можно использовать в заявлении CREATE TABLE или ALTER TABLE.
Ограничение на одно поле, также известное как ограничение на уровне столбцов, объявляется с самим полем после того, как было объявлено поле и тип данных. Используйте таблицу клиентов и создайте основной ключ с одним полем в поле CustomerID. Чтобы добавить ограничение, используйте ключевое слово CONSTRAINT с именем поля.
Обратите внимание, что имя ограничения дано. Можно использовать ярлык для объявления основного ключа, полностью отметаемого в пункте CONSTRAINT.
Однако использование метода ярлыка приведет к случайному сгенерировать имя ограничения, что затруднит ссылку в коде. Всегда стоит назвать ограничения.
Чтобы снять ограничение, используйте пункт DROP CONSTRAINT с заявлением ALTER TABLE и укай имя ограничения.
Ограничения также могут использоваться для ограничения допустимых значений для поля. Можно ограничить значения NOT NULL или UNIQUE или определить ограничение проверки, которое является типом бизнес-правила, которое можно применить к полю. Предположим, что необходимо ограничить (или ограничить) значения полей имени и фамилии уникальными, что означает, что никогда не должно быть сочетания имени и фамилии, одинаковой для любых двух записей в таблице. Поскольку это ограничение с несколькими полями, оно объявляется на уровне таблицы, а не на уровне поля. Используйте пункт ADD CONSTRAINT и определите список с несколькими полями.
Выражение, используемое для определения ограничения проверки, также может относиться к более чем одному полю в одной таблице или к полям в других таблицах и может использовать любые операции, допустимые в access SQL, такие как операторы SELECT, математические операторы и агрегированные функции. Выражение, определяющие ограничение проверки, может быть не более 64 символов.
Предположим, что необходимо проверить кредитный лимит каждого клиента, прежде чем он будет добавлен в таблицу клиентов. Используя заявление ALTER TABLE с оговорками ADD COLUMN и CONSTRAINT, создайте ограничение, которое будет проверять значение в таблице CreditLimit для проверки кредитного лимита клиента. Используйте следующие SQL для создания таблицы tblCreditLimit, добавьте поле CustomerLimit в таблицу tblCustomers, добавьте ограничение проверки в таблицу tblCustomers и проверьте ограничение проверки.
Обратите внимание, что при выполнении заявления UPDATE TABLE вы получаете сообщение о том, что обновление не удалось, так как оно нарушило ограничение проверки. Если вы обновим поле CustomerLimit до значения, равного или менее 100, обновление будет успешным.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Как сделать связи в sql
Базы данных могут содержать таблицы, которые связаны между собой различными связями. Связь (relationship) представляет ассоциацию между сущностями разных типов.
При выделении связи выделяют главную или родительскую таблицу (primary key table / master table) и зависимую, дочернюю таблицу (foreign key table / child table). Дочерняя таблица зависит от родительской.
Для организации связи используются внешние ключи. Внешний ключ представляет один или несколько столбцов из одной таблицы, который одновременно является потенциальным ключом из другой таблицы. Внешний ключ необязательно должен соответствовать первичному ключу из главной таблицы. Хотя, как правило, внешний ключ из зависимой таблицы указывает на первичный ключ из главной таблицы.
Связи между таблицами бывают следующих типов:
Один к одному (One to one)
Один к многим (One to many)
Многие ко многим (Many to many)
Связь один к одному
Нередко этот тип связей предполагает разбиение одной большой таблицы на несколько маленьких. Основная родительская таблица в этом случае продолжает содержать часто используемые данные, а дочерняя зависимая таблица обычно хранит данные, которые используются реже.
В этом отношении первичный ключ зависимой таблицы в то же время является внешним ключом, который ссылается на первичный ключ из главной таблицы.
Например, таблица Users представляет пользователей и имеет следующие столбцы:
UserId (идентификатор, первичный ключ)
Name (имя пользователя)
И таблица Blogs представляет блоги пользователей и имеет следующие столбцы:
BlogId (идентификатор, первичный и внешний ключ)
Name (название блога)
В этом случае столбец BlogId будет хранить значение из столбца UserId из таблицы пользователей. То есть столбец BlogId будет выступать одновременно первичным и внешним ключом.
Связь один ко многим
К примеру, пусть будет таблица Articles, которая представляет статьи блога и которая имеет следующие столбцы:
ArticleId (идентификатор, первичный ключ)
BlogId (внешний ключ)
Title (название статьи)
В этом случае столбец BlogId из таблицы статей будет хранить значение из столбца BlogId из таблицы блогов.
Связь многие ко многим
Но в SQL Server на уровне базы данных мы не можем установить прямую связь многие ко многим между двумя таблицами. Это делается посредством вспомогательной промежуточной таблицы. Иногда данные из этой промежуточной таблицы представляют отдельную сущность.
Например, в случае со статьями и тегами пусть будет таблица Tags, которая имеет два столбца:
TagId (идентификатор, первичный ключ)
Также пусть будет промежуточная таблица ArticleTags со следующими полями:
TagId (идентификатор, первичный и внешний ключ)
ArticleIdId (идентификатор, первичный и внешний ключ)
Технически мы получим две связи один-ко-многим. Столбец TagId из таблицы ArticleTags будет ссылаться на столбец TagId из таблицы Tags. А столбец ArticleId из таблицы ArticleTags будет ссылаться на столбец ArticleId из таблицы Articles. То есть столбцы TagId и ArticleId в таблице ArticleTags представляют составной первичный ключ и одновременно являются внешними ключами для связи с таблицами Articles и Tags.
Ссылочная целостность данных
При изменении первичных и внешних ключей следует соблюдать такой аспект как ссылочная целостность данных (referential integrity). Ее основная идея состоит в том, чтобы две таблице в базе данных, которые хранят одни и те же данные, поддерживали их согласованность. Целостность данных представляет правильно выстроенные отношения между таблицами с корректной установкой ссылок между ними. В каких случаях целостность данных может нарушаться:
Аномалия удаления (deletion anomaly). Возникает при удалении строки из главной таблицы. В этом случае внешний ключ из зависимой таблицы продолжает ссылаться на удаленную строку из главной таблицы
Аномалия вставки (insertion anomaly). Возникает при вставке строки в зависимую таблицу. В этом случае внешний ключ из зависимой таблицы не соответствует первичному ключу ни одной из строк из главной таблицы.
Аномалии обновления (update anomaly). При подобной аномалии несколько строк одной таблицы могут содержать данные, которые принадлежат одному и тому же объекту. При изменении данных в одной строке они могу прийти в противоречие с данными из другой строки.
Аномалия удаления
Для решения аномалии удаления для внешнего ключа следует устанавливать одно из двух ограничений:
Если строка из зависимой таблицы обязательно требует наличия строки из главной таблицы, то для внешнего ключа устанавливается каскадное удаление. То есть при удалении строки из главной таблицы происходит удаление связанной строки (строк) из зависимой таблицы.
Если строка из зависимой таблицы допускает отсутствие связи со строкой из главной таблицы (то есть такая связь необязательна), то для внешнего ключа при удалении связанной строки из главной таблицы задается установка значения NULL. При этом столбец внешнего ключа должен допускать значение NULL.
Аномалия вставки
Для решения аномалии вставки при добавлении в зависимую таблицу данных столбец, который представляет внешний ключ, должен допускать значение NULL. И таким образом, если добавляемый объект не имеет связи с главной таблицей, то в столбце внешнего ключа будет стоять значение NULL.
Аномалии обновления
Для решения проблемы аномалии обновления применяется нормализация, которая будет рассмотрена далее.