Краткое содержание: В этом руководстве вы узнаете, как использовать ограничение внешнего ключа SQL Server для обеспечения связи между данными в двух таблицах.
Введение в ограничение внешнего ключа SQL Server
Рассмотрим следующие таблицы vendor_groups и vendors:
CREATE TABLE procurement.vendor_groups(
group_id INT IDENTITY PRIMARY KEY,
group_name VARCHAR(100) NOT NULL
);
CREATE TABLE procurement.vendors(
vendor_id INT IDENTITY PRIMARY KEY,
vendor_name VARCHAR(100) NOT NULL,
group_id INT NOT NULL,
);
Каждый поставщик принадлежит к группе поставщиков, и каждая группа поставщиков может иметь ноль или более поставщиков. Связь между таблицами vendor_groups и vendors — один ко многим.
Для каждой строки в таблице vendors вы всегда можете найти соответствующую строку в таблице vendor_groups.
Однако с текущей настройкой таблиц вы можете вставить строку в таблицу vendors без соответствующей строки в таблице vendor_groups. Аналогично вы также можете удалить строку в таблице vendor_groups без обновления или удаления соответствующих строк в таблице vendors, что приведет к появлению потерянных строк в таблице vendors.
Чтобы обеспечить связь между данными в таблицах vendor_groups и vendors, необходимо создать внешний ключ в таблице vendors.
Внешний ключ — это столбец или группа столбцов в одной таблице, которые уникально идентифицируют строку другой таблицы(или той же таблицы в случае ссылки на себя).
Для создания внешнего ключа используется ограничение FOREIGN KEY.
Следующие операторы удаляют таблицу поставщиков и воссоздают ее с ограничением FOREIGN KEY:
DROP TABLE vendors;
CREATE TABLE procurement.vendors(
vendor_id INT IDENTITY PRIMARY KEY,
vendor_name VARCHAR(100) NOT NULL,
group_id INT NOT NULL,
CONSTRAINT fk_group FOREIGN KEY(group_id)
REFERENCES procurement.vendor_groups(group_id)
);
Таблица vendor_groups теперь называется родительской таблицей, то есть таблицей, на которую ссылается ограничение внешнего ключа. Таблица vendors называется дочерней таблицей, то есть таблицей, на которую применяется ограничение внешнего ключа.
В приведенном выше операторе следующее предложение создает ограничение FOREIGN KEY с именем fk_group, которое связывает group_id в таблице vendors с group_id в таблице vendor_groups:
CONSTRAINT fk_group FOREIGN KEY(group_id) REFERENCES procurement.vendor_groups(group_id)
Синтаксис ограничения внешнего ключа SQL Server
Общий синтаксис для создания ограничения FOREIGN KEY следующий:
CONSTRAINT fk_constraint_name FOREIGN KEY(column_1, column2,...) REFERENCES parent_table_name(column1,column2,..)
Давайте рассмотрим этот синтаксис подробнее.
Сначала укажите имя ограничения FOREIGN KEY после ключевого слова CONSTRAINT. Имя ограничения необязательно, поэтому можно определить ограничение FOREIGN KEY следующим образом:
FOREIGN KEY(column_1, column2,...) REFERENCES parent_table_name(column1,column2,..)
В этом случае SQL Server автоматически сгенерирует имя для ограничения FOREIGN KEY.
Во-вторых, укажите список столбцов внешнего ключа, разделенных запятыми, заключенных в скобки после ключевого слова FOREIGN KEY.
В-третьих, укажите имя родительской таблицы, на которую ссылается внешний ключ, и список столбцов, разделенных запятыми, которые связаны со столбцом в дочерней таблице.
Пример ограничения внешнего ключа SQL Server
Сначала вставьте несколько строк в таблицу vendor_groups:
INSERT INTO procurement.vendor_groups(group_name)
VALUES('Third-Party Vendors'),
('Interco Vendors'),
('One-time Vendors');
Во-вторых, добавьте нового поставщика с группой поставщиков в таблицу поставщиков:
INSERT INTO procurement.vendors(vendor_name, group_id)
VALUES('ABC Corp',1);
Заявление сработало так, как и ожидалось.
В-третьих, попробуйте вставить нового поставщика, группа поставщиков которого не существует в таблице vendor_groups:
INSERT INTO procurement.vendors(vendor_name, group_id)
VALUES('XYZ Corp',4);
SQL Server выдал следующую ошибку:
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_group". The conflict occurred in database "BikeStores", table "procurement.vendor_groups", column 'group_id'.
В этом примере из-за ограничения FOREIGN KEY SQL Server отклонил вставку и выдал ошибку.
Ссылочные действия
Ограничение внешнего ключа обеспечивает ссылочную целостность. Это означает, что вы можете вставить строку в дочернюю таблицу только в том случае, если в родительской таблице есть соответствующая строка.
Кроме того, ограничение внешнего ключа позволяет определить ссылочные действия при обновлении или удалении строки в родительской таблице следующим образом:
FOREIGN KEY(foreign_key_columns)
REFERENCES parent_table(parent_key_columns)
ON UPDATE action
ON DELETE action;
ON UPDATE и ON DELETE определяют, какое действие будет выполнено при обновлении и удалении строки в родительской таблице. Разрешены следующие действия: NO ACTION, CASCADE, SET NULL и SET DEFAULT
Действия по удалению строк в родительской таблице
Если вы удалите одну или несколько строк в родительской таблице, вы можете задать одно из следующих действий:
- ПРИ УДАЛЕНИИ НЕТ ДЕЙСТВИЯ: SQL Server выдает ошибку и откатывает действие удаления для строки в родительской таблице.
- ПРИ КАСКАДЕ УДАЛЕНИЯ: SQL Server удаляет строки в дочерней таблице, соответствующие строке, удаленной из родительской таблицы.
- ON DELETE SET NULL: SQL Server устанавливает строки в дочерней таблице в NULL, если соответствующие строки в родительской таблице удалены. Для выполнения этого действия столбцы внешнего ключа должны быть допускающими значение NULL.
- ON DELETE SET DEFAULT SQL Server устанавливает строки в дочерней таблице в их значения по умолчанию, если соответствующие строки в родительской таблице удалены. Для выполнения этого действия столбцы внешнего ключа должны иметь определения по умолчанию. Обратите внимание, что столбец, допускающий значение NULL, имеет значение по умолчанию NULL, если значение по умолчанию не указано.
По умолчанию SQL Server применяет ON DELETE NO ACTION, если явно не указано какое-либо действие.
Действие обновления строк в родительской таблице
При обновлении одной или нескольких строк в родительской таблице можно задать одно из следующих действий:
- ПРИ ОБНОВЛЕНИИ НЕТ ДЕЙСТВИЯ: SQL Server выдает ошибку и откатывает действие обновления для строки в родительской таблице.
- ON UPDATE CASCADE: SQL Server обновляет соответствующие строки в дочерней таблице при обновлении строк в родительской таблице.
- ON UPDATE SET NULL: SQL Server устанавливает строки в дочерней таблице в NULL при обновлении соответствующей строки в родительской таблице. Обратите внимание, что столбцы внешнего ключа должны быть допускающими значение NULL для выполнения этого действия.
- ON UPDATE SET DEFAULT: SQL Server устанавливает значения по умолчанию для строк в дочерней таблице, у которых обновлены соответствующие строки в родительской таблице.
В этом руководстве вы узнали, как использовать ограничение внешнего ключа SQL Server для обеспечения ссылочной целостности между таблицами.
