Создание связи между таблицами на диаграмме (визуальные инструменты для баз данных)
Можно создавать связи между столбцами разных таблиц в конструкторе диаграмм, перетаскивая столбцы между таблицами.
Графическое создание связи
- В конструкторе баз данных щелкните селектор строк для одного или более столбцов базы данных, которые необходимо связать со столбцом в другой таблице.
- Перетащите выбранные столбцы в связанную таблицу.
- Отображаются два диалоговых окна: Связь по внешнему ключу и Таблицы и столбцы, второе отображается на переднем плане.
- Имя связи устанавливается системой в формате FK_локальная_таблица_таблица_внешнего_ключа. Можно изменить это значение.
- Убедитесь, что Таблица первичного ключа правильно задает таблицу.
- Сетка содержит локальные столбцы и соответствующие им внешние столбцы. Можно добавить или удалить столбцы таблицы, либо изменить сопоставления.
- Нажмите кнопку ОК. Открывается диалоговое окно Связь по внешнему ключу . Выбранная связь отображает созданную связь.
- Измените свойства связи в сетке.
- Нажмите кнопку OК , чтобы создать связь. Конструктор баз данных отображает связь между выбранными столбцами.
Создание связей по внешнему ключу
В этой статье описывается создание связей внешнего ключа в 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, определенное на уровне таблицы, должно содержать такое же число ссылочных столбцов, какое содержится в списке столбцов в ограничении. Тип данных каждого ссылочного столбца должен также совпадать с типом соответствующего столбца в списке столбцов.
- Ядро СУБД не имеет предопределенного ограничения на количество ограничений FOREIGN KEY, которые могут содержать ссылки на другие таблицы. Ядро СУБД также не ограничивает количество ограничений FOREIGN KEY, принадлежащих другим таблицам, ссылающимся на определенную таблицу. Но фактическое количество используемых ограничений FOREIGN KEY ограничивается конфигурацией оборудования, базы данных и приложения. Максимальное количество таблиц и столбцов, на которые может ссылаться таблица в качестве внешних ключей (исходящих ссылок), равно 253. SQL Server 2016 (13.x) и более поздних версий увеличивает ограничение числа других таблиц и столбцов, которые могут ссылаться на столбцы в одной таблице (входящей ссылки) с 253 до 10 000. (Требуется уровень совместимости не менее 130.) Увеличение имеет следующие ограничения:
- Превышение 253 ссылок на внешние ключи поддерживается только для операций DELETE и UPDATE DML. Операции MERGE не поддерживаются.
- Таблица со ссылкой внешнего ключа на саму себя по-прежнему ограничена 253 ссылками на внешние ключи.
- Превышение числа в 253 ссылки на внешние ключи в настоящее время недоступно для индексов columnstore, оптимизированных для памяти таблиц или Stretch Database.
Stretch Database устарел в SQL Server 2022 (16.x) и База данных SQL Azure. Эта функция будет удалена в будущей версии ядро СУБД. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.
Создание связи по внешнему ключу в конструкторе таблиц
Использование SQL Server Management Studio
- В обозревателе объектов щелкните правой кнопкой мыши таблицу, которая будет содержать внешний ключ для связи, и выберите пункт Конструктор. Таблица откроется в окне Конструктор таблиц.
- В меню конструктора таблиц выберите Связи. (См. меню Конструктор таблиц в заголовке или щелкните правой кнопкой мыши пустое место определения таблицы и выберите Связи.)
- В диалоговом окне Связи внешнего ключа нажмите кнопку Добавить. Связь отображается в списке выбранных связей с именем, предоставленным системой, в формате FK_tablename_ >, где имя первой таблицы — имя внешней таблицы ключей, а второе имя таблицы — имя таблицы первичного ключа. Это просто принятое по умолчанию и распространенное соглашение об именах для поля (Name) объекта внешнего ключа.
- Выберите нужную связь в списке Выбранные связи.
- Выберите Спецификация таблиц и столбцов в сетке справа и нажмите кнопку с многоточием (…) справа от свойства.
- В диалоговом окне Таблицы и столбы в раскрывающемся списке Первичный ключ выберите таблицу, которая будет находиться на стороне первичного ключа связи.
- В сетке внизу выберите столбцы, составляющие первичный ключ таблицы. В соседней ячейке сетки справа от каждого столбца выберите соответствующий столбец внешнего ключа таблицы внешнего ключа. Конструктор таблиц автоматически предлагает имя для связи. Чтобы его изменить, отредактируйте содержимое текстового поля Имя связи .
- Нажмите кнопку OК , чтобы создать связь.
- Закройте окно конструктора таблиц и сохраните внесенные изменения, чтобы изменения связи внешнего ключа вступили в силу.
Создание внешнего ключа в новой таблице
Использование Transact-SQL
В следующем примере создается таблица и определяется ограничение внешнего ключа для столбца TempID , ссылающегося на столбец SalesReasonID в таблице Sales.SalesReason базы данных AdventureWorks . Предложения ON DELETE CASCADE и ON UPDATE CASCADE используются для обеспечения распространения изменений, вносимых в таблицу Sales.SalesReason на таблицу Sales.TempSalesReason .
CREATE TABLE Sales.TempSalesReason ( TempID int NOT NULL, Name nvarchar(50) , CONSTRAINT PK_TempSales PRIMARY KEY NONCLUSTERED (TempID) , CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID) REFERENCES Sales.SalesReason (SalesReasonID) ON DELETE CASCADE ON UPDATE CASCADE ) ;Создание внешнего ключа в существующей таблице
Использование Transact-SQL
В следующем примере создается внешний ключ для столбца TempID , ссылающегося на столбец SalesReasonID в таблице Sales.SalesReason базы данных AdventureWorks .
ALTER TABLE Sales.TempSalesReason ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID) REFERENCES Sales.SalesReason (SalesReasonID) ON DELETE CASCADE ON UPDATE CASCADE ;Следующие шаги
- Ограничения первичных и внешних ключей
- GRANT (разрешения на базу данных)
- ALTER TABLE
- CREATE TABLE
- ALTER TABLE table_constraint.
Как создать связь между таблицами в sql
Для создания связи между таблицами в SQL используется внешний ключ FOREIGN KEY. Внешний ключ определяет связь между двумя таблицами на основе значения определенного столбца.
Предположим, у вас есть две таблицы: orders и customers. Каждый заказ относится к определенному клиенту, поэтому необходимо установить связь между таблицами. Для этого нужно определить внешний ключ в таблице orders:
CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(50) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );В этом примере мы создаем таблицы customers и orders. Затем мы определяем столбец customer_id как внешний ключ в таблице orders с помощью ключевого слова FOREIGN KEY. Мы указываем, что этот внешний ключ связывает столбец customer_id в таблице orders со столбцом customer_id в таблице customers.
После создания связи между таблицами вы можете использовать операторы JOIN для объединения этих таблиц и выполнения запросов, используя данные из обеих таблиц.
SQL Соединение таблиц
Одна из наиболее важных черт запросов SQL состоит в их способности определять связи между множеством таблиц и отображать содержащуюся в них информацию в терминах этих связей в рамках единственной команды. Операция такого рода называется соединением (соединение таблиц). Фактически, наличие операции соединения является едва ли не самым главным, что отличает реляционные системы от систем других типов. При операции соединения таблицы перечисляются в предложении запроса FROM; имена таблиц разделяются запятыми. Предикат запроса может ссылаться на любой столбец любой из соединяемых таблиц и, следовательно, может использоваться для установления связей между ними. Обычно предикат сравнивает значения в столбцах различных таблиц для того, чтобы определить, удовлетворяется ли условие WHERE.
1. Предположим, нужно установить связь между продавцами (Salespeople) и покупателями (Customers) в соответствии с местом их проживания, чтобы получить все возможные комбинации продавцов и покупателей из одного города. Для этого необходимо взять продавца из таблицы Salespeople и выполнить по таблице Customers поиск всех покупателей, имеющих то же значение в столбце city:
SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Salespeople, Customers WHERE Salespeople.city = Customers.city;
Во избежание двусмысленности в выборке ссылки на поля во фразе WHERE должны уточняться именами содержащих их таблиц. Операция соединения таблиц посредством ссылочной целостности применяется для использования связей, встроенных в базу данных.
2. Например, чтобы показать соответствие имен покупателей именам продавцов, обслуживающих этих покупателей, используется следующий запрос:
SELECT Customers.cname, Salespeople.sname FROM Customers, Salespeople WHERE Salespeople.snum = Customers.snum;
Соединение, использующее предикаты, основанные на равенствах, называется эквисоединением. Рассмотренные выше примеры относятся именно к этой категории, поскольку все условия в предложении WHERE базируются на математических выражениях, использующих символ равенства. Эквисоединение является наиболее распространенным типом соединения., но существуют и другие. Фактически в соединении можно использовать любой оператор сравнения.
3. Например, следующее соединение генерирует все комбинации имен продавцов и покупателей так, что первые предшествуют последним в алфавитном порядке, а последние имеют рейтинг меньше 200:
SELECT sname, cname FROM Salespeople, Customers WHERE sname < cname AND rating < 200;
4. Можно конструировать запросы путем соединения более чем двух таблиц. Например, нам нужно найти все заявки покупателей, не находящихся в том же городе, что и их продавец. Для потребуется связать все три рассматриваемые таблицы:
SELECT onum, cname, Orders.cnum, Orders.snum FROM Salespeople, Customers, Orders WHERE Customers.city <> Salespeople.city AND Orders.cnum = Customers.cnum AND Orders.snum = Salespeople.snum;
Хотя команда выглядит достаточно сложно, следуя ее логике, легко убедиться, что в выходных данных будут перечислены покупатели и продавцы, расположенные в разных городах (они сравниваются по полю snum), и что указанные заказы сделаны именно этими покупателями (подбор заказов устанавливается в соответствие с полями cnum и snum таблицы Orders).
В некоторых, довольно часто встречающихся на практике случаях, необходимо выбрать данные из таблицы, основываясь на результатах дополнительных выборок из этой же таблицы. Такие выборки называются коррелированными. Для их выполнения используются псевдонимы таблиц (алиасные имена), которые следуют непосредственно за именем таблицы в выборке. В приведенном ниже примере используются псевдонимы таблицы CUSTOMERS: first и second.
5. Выбрать все пары продавцов, имеющих одинаковый рейтинг, можно следующей командой:
SELECT first.cname, second.cname, first.rating FROM Customers first, Customers second WHERE first.rating = second.rating AND first.cname < second.cname;
В приведенном примере команда SQL ведет себя так, как будто в операции соединения участвуют две таблицы, называемые «first» и «second». Обе они в действительности являются таблицей Customers, но алиасы позволяют рассматривать ее как две независимые таблицы. Алиасы first и second были определены в предложении запроса FROM непосредственно за именем таблицы. Алиасы применяются также в предложении SELECT, несмотря на то, что они не определены вплоть до предложения FROM. Это совершенно оправдано. SQL сначала примет какой-либо из таких алиасов на веру, но затем отвергнет команду, если в предложении FROM запроса алиасы не определены. Время жизни алиаса зависит от времени выполнения команды. После выполнения запроса используемые в нем алиасы теряют свои значения. Получив две копии таблицы Customers для работы, SQL выполняет операцию JOIN, как для двух разных таблиц: выбирает очередную строку из одного алиаса и соединяет ее с каждой строкой другого алиаса. Для исключения повторений необходимо задать порядок для двух значений так, чтобы одно значение было меньше, чем другое, или предшествовало в алфавитном порядке.
Чтобы соединение возвращало данные одной таблицы даже при отсутствии соответствующей записи в другой таблице, можно создать внешнее соединение с помощью строки символов (+) в сравнении, формирующем соединение таблиц.
6. В данном примере выбираются строки, содержащие имена и должности сотрудников с указанием названий отделов, в которых они работают. При этом в результирующую выборку попадает также отдел OPERATIONS, в котором не работает ни одного служащего
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno;
Результат выполнения данного запроса: