Создание связей на языке sql

Создание связей по внешнему ключу Create Foreign Key Relationships

Разрешения Permissions

Создание новой таблицы с внешним ключом требует разрешения CREATE TABLE в базе данных и разрешения ALTER на схему, в которой создается таблица. Creating a new table with a foreign key requires CREATE TABLE permission in the database, and ALTER permission on the schema in which the table is being created.

Создание внешнего ключа в существующей таблице требует разрешения ALTER на таблицу. Creating a foreign key in an existing table requires ALTER permission on the table.

Пределы и ограничения Limits and restrictions

Ограничение внешнего ключа не обязательно должно быть связано только с ограничением первичного ключа в другой таблице. A foreign key constraint doesn’t have to be linked only to a primary key constraint in another table. Внешние ключи также могут быть определены, чтобы ссылаться на столбцы ограничения UNIQUE в другой таблице. Foreign keys can also be defined to reference the columns of a UNIQUE constraint in another table.

Если столбцу, имеющему ограничение внешнего ключа, задается значение, отличное от NULL, такое же значение должно существовать и в указываемом столбце. When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column. В противном случае будет возвращено сообщение о нарушении внешнего ключа. Otherwise, a foreign key violation error message is returned. Для обеспечения проверки всех значений сложного ограничения внешнего ключа задайте параметр NOT NULL для всех столбцов, участвующих в индексе. To make sure that all values of a composite foreign key constraint are verified, specify NOT NULL on all the participating columns.

Ограничения FOREIGN KEY могут ссылаться только на таблицы в пределах той же базы данных на том же сервере. FOREIGN KEY constraints can reference only tables within the same database on the same server. Межбазовую ссылочную целостность необходимо реализовать посредством триггеров. Cross-database referential integrity must be implemented through triggers. Дополнительные сведения см. в статье об инструкции CREATE TRIGGER. For more information, see CREATE TRIGGER.

Ограничения FOREIGN KEY могут ссылаться на другие столбцы той же таблицы и считаются ссылками на себя. FOREIGN KEY constraints can reference another column in the same table, and is referred to as a self-reference.

Ограничение FOREIGN KEY, определенное на уровне столбцов, может содержать только один ссылочный столбец. A FOREIGN KEY constraint specified at the column level can list only one reference column. Этот столбец должен принадлежать к тому же типу данных, что и столбец, для которого определяется ограничение. This column must have the same data type as the column on which the constraint is defined.

Ограничение FOREIGN KEY, определенное на уровне таблицы, должно содержать такое же число ссылочных столбцов, какое содержится в списке столбцов в ограничении. A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. Тип данных каждого ссылочного столбца должен также совпадать с типом соответствующего столбца в списке столбцов. The data type of each reference column must also be the same as the corresponding column in the column list.

Компонент Database Engine Database Engine не имеет предопределенного ограничения на число ограничений FOREIGN KEY, которые могут содержаться в таблице, ссылающейся на другие таблицы. The Компонент Database Engine Database Engine doesn’t have a predefined limit on the number of FOREIGN KEY constraints a table can contain that reference other tables. Компонент Database Engine Database Engine также не ограничивает число ограничений FOREIGN KEY, принадлежащих другим таблицам, которые ссылаются на определенную таблицу. The Компонент Database Engine Database Engine also doesn’t limit the number of FOREIGN KEY constraints owned by other tables that reference a specific table. Но фактическое количество используемых ограничений FOREIGN KEY ограничивается конфигурацией оборудования, базы данных и приложения. However, the actual number of FOREIGN KEY constraints used is limited by the hardware configuration, and by the design of the database and application. Максимальное количество таблиц и столбцов, на которые может ссылаться таблица в качестве внешних ключей (исходящих ссылок), равно 253. A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x); SQL Server 2016 (13.x) и последующие версии увеличивает ограничение на количество других таблиц и столбцов, которые могут ссылаться на столбцы в одной таблице (входящие ссылки), с 253 до 10 000. and later increases the limit for the number of other tables and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (Требуется уровень совместимости не менее 130.) Увеличение имеет следующие ограничения: (Requires at least 130 compatibility level.) The increase has the following restrictions:

Ограничения FOREIGN KEY не применяются к временным таблицам. FOREIGN KEY constraints aren’t enforced on temporary tables.

Если внешний ключ определен на столбце определяемого пользователем типа данных CLR, реализация этого типа должна поддерживать двоичную сортировку. If a foreign key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering. Дополнительные сведения об определяемых пользователем типах данных CLR см. в разделе Определяемые пользователем типы данных CLR. For more information, see CLR User-Defined Types.

Создание связи по внешнему ключу в конструкторе таблиц Create a foreign key relationship in Table Designer

Использование среды SQL Server Management Studio Using SQL Server Management Studio

В обозревателе объектов щелкните правой кнопкой мыши таблицу, которая будет содержать внешний ключ для связи, и выберите пункт Конструктор. In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and click Design.

В меню конструктора таблиц выберите пункт Связи. From the Table Designer menu, click Relationships.

В диалоговом окне Связи внешнего ключа щелкните Добавить. In the Foreign-key Relationships dialog box, click Add.

Щелкните Спецификация таблиц и столбцов в сетке справа и нажмите кнопку с многоточием ( . ) справа от свойства. Click Tables and Columns Specification in the grid to the right and click the ellipses (. ) to the right of the property.

В диалоговом окне Таблицы и столбы в раскрывающемся списке Первичный ключ выберите таблицу, которая будет находиться на стороне первичного ключа связи. In the Tables and Columns dialog box, in the Primary Key drop-down list, choose the table that will be on the primary-key side of the relationship.

В сетке внизу выберите столбцы, составляющие первичный ключ таблицы. In the grid beneath, choose the columns contributing to the table’s primary key. В соседней ячейке сетки справа от каждого столбца выберите соответствующий столбец внешнего ключа таблицы внешнего ключа. In the adjacent grid cell to the right of each column, choose the corresponding foreign-key column of the foreign-key table.

Закройте окно конструктора таблиц и сохраните внесенные изменения, чтобы изменения связи внешнего ключа вступили в силу. Close the table designer window and save your changes for the foreign key relationship change to take effect.

Создание внешнего ключа в новой таблице Create a foreign key in a new table

Использование Transact-SQL Using Transact-SQL

Создание внешнего ключа в существующей таблице Create a foreign key in an existing table

Использование Transact-SQL Using Transact-SQL

Дальнейшие действия Next steps

Дополнительные сведения см. в разделе: For more information, see:

Источник

BestProg

Создание связи (отношения) типа «один ко многим» между таблицами базы данных Microsoft SQL Server

В данной теме показано, как создавать связь (отношение) между таблицами по некоторому полю. Данная тема базируется на знаниях предыдущих тем:

Содержание

Поиск на других ресурсах:

Условие задачи

В базе данных заданы две таблицы с именами Source и Emission. Таблица Source определяет источник загрязненных выбросов. Таблица Emission определяет время выбросов и число загрязненных выбросов, которое было сформировано источником.

Структура таблиц следующая.

Используя средства системы Microsoft Visual Studio необходимо реализовать связь (отношение) «один ко многим» между таблицами Source и Emission по полю ID_Source.

Выполнение

1. Запуск Microsoft Visual Studio

2. Создание/подключение базы данных

После подключения окно Server Explorer будет иметь вид, как показано на рисунке 1.

Рис. 1. Окно Server Explorer после подключения базы данных «MyDataBase.mdf»

3. Поля ID_Source и ID_Emission

Следует отметить, что поля ID_Source и ID_Emission есть уникальными счетчиками. Такие поля используются в базах данных для обеспечения уникальности каждой записи таблицы.

Поле ID_Source таблицы Source есть первичным ключом.

Подробный пример создания уникального поля, которое есть счетчиком, описывается в статье:

4. Установление связи между таблицами

4.1. Снятие опции «Prevent saving changes that require table re-creation»

По умолчанию, система MS Visual Studio запрещает сохранение изменений, которые требуют повторного создания таблиц. Чтобы разрешить вносить изменения в таблицы нужно настроить (снять выделение) опцию

Для этого нужно выполнить следующую последовательность шагов:

После выполненных действий можно создавать связь между таблицами.

Рис. 2. Опция «Prevent saving changes that require table re-creation»

4.2. Установление первичного ключа ( Primary Key ) в таблице Source

Как видно из структуры таблиц (рисунок 1) общим для таблиц есть поле ID_Source. Связь между таблицами будет осуществляться по этому полю.

В таблице Source нужно установить поле ID_Source как «Первичный ключ» ( Primary Key ).

Чтобы установить первичный ключ нужно выполнить такие действия:

Рис. 3. Команда «Open Table Definition»

Рис. 4. Установление первичного ключа в таблице Source

Рис. 5. Поле ID_Source в таблице Source после установления первичного ключа

В таблице Emission не обязательно устанавливать первичный ключ.

4.3. Создание связи между таблицами по полю ID_Source

Рис. 6. Команда добавления новой диаграммы

Рис. 7. Окно «Add Table» добавления таблиц в диаграмму

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

Рис. 8. Таблицы Source и Emission

В окне «Tables and Columns» есть такие поля (рисунок 9):

Рис. 9. Окно настройки параметров связи (отношения) между таблицами

В окне «Foreign Key Relationship» настраиваются свойства соединения. Можно оставить все как есть.

Рис. 10. Настройка свойств соединения FK_Emission_Source

4.4. Диаграмма связи

После создания связи окно диаграммы будет иметь вид, как показано на рисунке 11.

Рис. 11. Вид диаграммы после установки отношения (связи) между таблицами Source и Emission

Это означает, что в таблице Source числовое значение в поле ID_Source может встречаться только один раз. А в таблице Emission числовое значение ID_Source может повторяться (бесконечное количество раз). Таким образом можно представить любое множество уникальных объектов, которые имеют свойство повторяться в некоторой предметной области.

После закрытия диаграммы ее нужно сохранить под некоторым именем, например Diagram1 (рисунок 12). Система выдаст соответствующее окно уточнения.

Рис. 12. Задание имени для диаграммы

Рис. 13. Окно сохранения таблиц в базе данных в связи с изменениями

После выполненных действий, диаграмма Diagram1 отобразится в окне Server Explorer (рис. 14). С помощью команд контекстного меню есть возможность управлять диаграммой. Так, например, команда «Design Database Diagram» переводит диаграмму в режим редактирования, в котором можно изменять связи между таблицами базы данных.

Рис. 14. Команда редактирования связей (отношений) между таблицами

5. Программное управление данными

После создания связи (отношения) между таблицами можно создавать проект, который будет управлять данными в таблицах. Но это уже совсем другая тема.

Источник

Russian (Pусский) translation by Yuri Yuriev (you can also view the original English article)

Сегодня мы продолжаем наше путешествие в мир SQL и связанных баз данных. В третьей части этой серии мы узнаем, как работать с несколькими таблицами, которые имеют отношения друг с другом. Во-первых, мы рассмотрим некоторые базовые концепции, а затем начнем работать с JOIN queries в SQL.

Вы также можете увидеть базы данных SQL в действии, просмотрев SQL scripts, apps and add-ons на рынке Envato.

Напоминание

Введение

При создании базы данных здравый смысл подсказывает, что мы используем отдельные таблицы для разных типов сущностей. Например: клиенты, заказы, предметы, сообщения. Но нам также нужно иметь отношения между этими таблицами. Например, клиенты делают заказы, а заказы содержат предметы. Эти отношения должны быть представлены в базе данных. Кроме того, при получении данных с помощью SQL нам нужно использовать определённые типы запросов JOIN, чтобы получить то, что нам нужно.

Существует несколько типов отношений базы данных. Сегодня мы рассмотрим следующее:

При выборе данных из нескольких таблиц с отношениями мы будем использовать запрос JOIN. Существует несколько типов JOIN, и мы собираемся узнать следующее:

Мы также узнаем об оговорках ON и USING.

Отношения один к одному

Предположим, у вас есть таблица для клиентов:

Мы можем поместить информацию об адресе клиента в отдельную таблицу:

Теперь мы имеем отношение между таблицей Customers и таблицей Addresses. Если каждый адрес может принадлежать только одному клиенту, это отношение «Один к одному». Имейте в виду, что такого рода отношения не очень распространены. Наша начальная таблица, которая включала адрес вместе с клиентом, в большинстве случаев могла работать нормально.

Обратите внимание: теперь в таблице Customers есть поле с именем «address_id», которое ссылается на запись соответствия в таблице Address. Это называется «Foreign Key» и используется для всех видов отношений баз данных. Мы рассмотрим этот вопрос позже.

Мы можем показать отношения между клиентскими и адресными записями следующим образом:

Обратите внимание, что существование отношений может быть необязательным, например, есть запись клиента, у которой нет связанной записи адреса.

Отношения «один ко многим» и «многие к одному»

Это наиболее часто используемый тип отношений. Рассмотрим веб-сайт e-commerce со следующим:

В этих случаях нам необходимо создать отношения «один ко многим». Вот пример:

У каждого клиента может быть ноль, один или несколько заказов. Но заказ может принадлежать только одному клиенту.

Отношения «многие ко многим»

В некоторых случаях вам может потребоваться несколько экземпляров с обеих сторон. Например, каждый заказ может содержать несколько элементов. И каждый элемент также может быть в нескольких заказах.

Для этих отношений нам нужно создать дополнительную таблицу:

Таблица Items_Orders имеет только одну цель, а именно, чтобы создать отношение «многие ко многим» между элементами и заказами.

Вот картинка таких отношений:

Если вы хотите включить записи items_orders в график, это может выглядеть так:

Самостоятельные ссылки

Это используется, когда таблица должна иметь отношения с самой собой. Например, у вас есть реферальная программа. Клиенты могут направлять других клиентов на ваш веб-сайт. Таблица может выглядеть так:

Клиенты 102 и 103 были переданы клиентом 101.

На самом деле это может быть похоже на отношение «один ко многим», поскольку один клиент может ссылаться на нескольких клиентов. Также он может выглядеть, как древовидная структура:

Один клиент может ссылаться на ноль, одного или несколько клиентов. К каждому клиенту может обращаться только один клиент, или вообще никто.

Если вы хотите создать самостоятельную ссылку «многие ко многим», вам понадобится дополнительная таблица, вроде той, что мы говорили в предыдущем разделе.

Foreign Keys

До сих пор мы узнали только о некоторых концепциях. Теперь пришло время воплотить их в жизнь с помощью SQL. Для этой части нам нужно понять, что такое Foreign Keys.

В приведённых выше примерах отношений мы всегда имели эти поля «**** _ id», которые ссылались на столбец в другой таблице. В этом примере столбец customer_id в таблице Orders является столбцом Foreign Key:

В базе данных типа MySQL есть два способа создания столбцов внешних ключей:

Чёткое определение Foreign Key

Давайте создадим простую таблицу клиентов:

Теперь таблицу заказов, в которой будет Foreign Key:

Оба столбца (customers.customer_id и orders.customer_id) должны иметь одинаковую структуру данных. Если один является INT, другой не должен быть BIGINT, например.

Обратите внимание, что в MySQL только механизм InnoDB имеет полную поддержку Foreign Keys. Но другие механизмы хранения данных по-прежнему позволят вам указывать их без каких-либо ошибок. Кроме того, столбец Foreign Key индексируется автоматически, если не указать для него другой индекс.

Без явной декларации

Та же таблица заказов может быть создана без явного объявления столбца customer_id как Foreign Key:

Далее мы собираемся узнать о JOIN-запросах.

Визуализация отношений

Моим любимым программным обеспечением для проектирования баз данных и визуализации отношений Foreign Key является MySQL Workbench.

После разработки базы данных вы можете экспортировать SQL и запустить его на своем сервере. Это очень удобно для больших и сложных баз данных.

JOIN Queries

Для извлечения данных из базы, имеющей отношения, нам часто приходится использовать JOIN queries.

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

У нас 4 клиента. У одного клиента два заказа, у двух клиентов по одному заказу, а у одного клиента нет заказа. Теперь давайте посмотрим различные виды JOIN queries, которые мы можем запустить в этих таблицах.

Перекрестное соединение

Это тип JOIN query по умолчанию, если условие не указано.

Результатом является так называемый «Cartesian product» таблиц. Это означает, что каждая строка из первой таблицы сопоставляется с каждой строкой второй таблицы. Так как каждая таблица имела 4 строки, мы получили результат из 16 строк.

Ключевое слово JOIN может быть опционально заменено запятой.

Конечно, такой результат не очень полезен. Давайте посмотрим на другие типы соединений.

Обычное соединение

При таком типе JOIN query таблицы должны иметь имя соответствующего столбца. В нашем случае обе таблицы имеют столбец customer_id. Таким образом, MySQL будет присоединяться к записям только тогда, когда значение этого столбца соответствует двум записям.

Внутреннее соединение

Когда указано условие соединения, выполняется Inner Join. В этом случае было бы неплохо иметь поле customer_id в обеих таблицах. Результаты должны быть похожими на Natural Join.

Результаты те же, за исключением небольшой разницы. Столбец customer_id повторяется дважды, один раз для каждой таблицы. Причина в том, что мы просто попросили базу данных соответствовать значениям этих двух столбцов. Но сами они не знают, что представляют одну и ту же информацию.

Давайте добавим еще несколько условий в запрос.

ON Clause

Прежде чем перейти к другим типам соединений, нам нужно посмотреть ON clause. Это полезно для помещения условий JOIN в отдельное предложение.

Теперь мы можем отличить условие JOIN от условий WHERE. Но есть и небольшая разница в функциональности. Мы увидим это в примерах LEFT JOIN.

USING Clause

USING clause похоже на предложение ON, но оно короче. Если столбец имеет одинаковое имя в обеих таблицах, мы можем указать его здесь.

На самом деле это похоже на NATURAL JOIN, поэтому столбец join (customer_id) не повторяется дважды в результатах.

Левое (внешнее) соединение

Хотя у Энди нет заказов, его запись все ещё отображается. Значения под столбцами второй таблицы имеют значение NULL.

Это полезно для поиска записей, которые не имеют отношений. Например, мы можем искать клиентов, которые не разместили какие-либо заказы.

Всё, что мы сделали, это нашли NULL для order_id.

Также обратите внимание, что ключевое слово OUTER является необязательным. Вы можете просто использовать LEFT JOIN вместо LEFT OUTER JOIN.

Условия

Теперь давайте рассмотрим запрос с условием.

Так что случилось с Энди и Сэнди? LEFT JOIN должен был вернуть клиентов без соответствующих заказов. Проблема в том, что предложение WHERE блокирует эти результаты. Чтобы их получить, мы можем попытаться включить условие NULL.

У нас Энди, но нет Сэнди. Тем не менее это выглядит не так. Чтобы получить то, что мы хотим, нам нужно использовать ON clause.

Правое (внешнее) соединение

RIGHT OUTER JOIN работает точно так же, но порядок таблиц обратный.

На этот раз у нас нет результатов NULL, потому что каждый заказ имеет соответствующую запись клиента. Мы можем изменить порядок таблиц и получить те же результаты, что и в LEFT OUTER JOIN.

Теперь у нас есть эти значения NULL, потому что таблица Customers находится на правой стороне соединения.

Заключение

Спасибо, что прочитали статью. Надеюсь, вам понравилось! Пожалуйста, оставляйте свои комментарии и вопросы, и хорошего дня!

Не забудьте проверить SQL scripts, apps and add-ons на рынке Envato. Вы получите представление о возможностях баз данных SQL, и сможете найти идеальное решение, которое поможет вам в текущем проекте разработки.

Следуйте за нами на Twitter или подпишитесь на Nettuts + RSS Feed для получения лучших обучающих материалов по веб-разработке в Интернете.

Источник