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