Microsoft SQL Server
Иностранные ключи
Поиск…
Внешние отношения / ограничение
Внешние ключи позволяют определить взаимосвязь между двумя таблицами. Одна (родительская) таблица должна иметь первичный ключ, который однозначно идентифицирует строки в таблице. Другая (дочерняя) таблица может иметь значение первичного ключа от родителя в одном из столбцов. Ограничение FOREIGN KEY REFERENCES гарантирует, что значения в дочерней таблице должны существовать в качестве значения первичного ключа в родительской таблице.
В этом примере у нас есть родительская таблица компании с основным ключом CompanyId и таблица Child Employee, в которой есть идентификатор компании, в которой работает этот сотрудник.
create table Company (
CompanyId int primary key,
Name nvarchar(200)
)
create table Employee (
EmployeeId int,
Name nvarchar(200),
CompanyId int
foreign key references Company(companyId)
)
ссылки на внешние ключи гарантируют, что значения, вставленные в столбец Employee.CompanyId, также должны существовать в столбце Company.CompanyId. Кроме того, никто не может удалять компанию в таблице компании, если есть хотя бы один сотрудник с подходящим companyId в дочерней таблице.
Отношение FOREIGN KEY гарантирует, что строки в двух таблицах не могут быть «несвязаны».
Поддержание отношений между родительскими / дочерними строками
Предположим, что у нас есть одна строка в таблице Company with companyId 1. Мы можем вставить строку в таблицу employee, в которой есть companyId 1:
insert into Employee values (17, 'John', 1)
Однако мы не можем вставить сотрудника, у которого есть несуществующий CompanyId:
insert into Employee values (17, 'John', 111111)
Msg 547, уровень 16, состояние 0, строка 12 Оператор INSERT противоречил ограничению FOREIGN KEY «FK__Employee__Compan__1EE485AA». Конфликт произошел в базе данных «MyDb», в таблице «dbo.Company», в столбце «CompanyId». Заявление было прекращено.
Кроме того, мы не можем удалить родительскую строку в таблице компании, если в таблице employee есть хотя бы одна дочерняя строка, которая ссылается на нее.
delete from company where CompanyId = 1
Msg 547, уровень 16, состояние 0, строка 14 Оператор DELETE противоречил ограничению REFERENCE «FK__Employee__Compan__1EE485AA». Конфликт произошел в базе данных «MyDb», таблице «dbo.Employee», в столбце «CompanyId». Заявление было прекращено.
Внешнее ключевое отношение гарантирует, что строки компании и сотрудника не будут «несвязаны».
Добавление отношения внешнего ключа к существующей таблице
Ограничение FOREIGN KEY может быть добавлено в существующие таблицы, которые все еще не связаны. Представьте, что у нас есть таблицы Company and Employee, где столбец Employee table CompanyId, но не имеет отношения с внешним ключом. Оператор ALTER TABLE позволяет добавить ограничение внешнего ключа в существующий столбец, который ссылается на другую таблицу и первичный ключ в этой таблице:
alter table Employee
add foreign key (CompanyId) references Company(CompanyId)
Добавить внешний ключ в существующую таблицу
Столбцы FOREIGN KEY с ограничением могут быть добавлены в существующие таблицы, которые все еще не находятся в отношениях. Представьте, что у нас есть таблицы компаний и сотрудников, в которых таблица Employee не имеет столбца CompanyId. Оператор ALTER TABLE позволяет добавить новый столбец с ограничениями внешнего ключа, который ссылается на другую таблицу и первичный ключ в этой таблице:
alter table Employee
add CompanyId int foreign key references Company(CompanyId)
Получение информации о внешних ограничениях ключа
Системный вид sys.foreignkeys возвращает информацию обо всех отношениях внешнего ключа в базе данных:
select name,
OBJECT_NAME(referenced_object_id) as [parent table],
OBJECT_NAME(parent_object_id) as [child table],
delete_referential_action_desc,
update_referential_action_desc
from sys.foreign_keys