Поделиться через

Ограничения первичных и внешних ключей

  • Статья

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure

Первичные ключи и внешние ключи — это два типа ограничений, которые можно использовать для обеспечения целостности данных в таблицах SQL Server. Это важные объекты базы данных.

Ограничения первичного ключа

Обычно в таблице есть столбец или сочетание столбцов, содержащих значения, уникально определяющие каждую строку таблицы. Этот столбец, или столбцы, называются первичным ключом (PK) таблицы и обеспечивает целостность сущности таблицы. Так как ограничения первичного ключа гарантируют уникальные данные, они часто определяются в столбце удостоверений.

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

Как показано на следующем рисунке, столбцы ProductID и VendorID в таблице Purchasing.ProductVendor формируют составное ограничение первичного ключа для этой таблицы. При этом гарантируется, что каждая строка в таблице ProductVendor имеет уникальное сочетание значений ProductID и VendorID. Это предотвращает вставку повторяющихся строк.

Схема строк в таблице для составного ограничения PRIMARY KEY.

  • В таблице возможно наличие только одного ограничения по первичному ключу.
  • Первичный ключ не может превышать 16 столбцов и общую длину ключа 900 байт.
  • Индекс, созданный ограничением первичного ключа, не может привести к тому, что число индексов таблицы превышает 999 некластеризованных индексов и 1 кластеризованных индексов.
  • Если кластеризованный или некластеризованный не указан для ограничения первичного ключа, кластеризованный используется, если в таблице нет кластеризованного индекса.
  • Все столбцы с ограничением первичного ключа должны быть определены как не допускающие значения NULL. Если значение NULL не указано, все столбцы, участвующие в ограничении первичного ключа, имеют значение NULL.
  • Если первичный ключ определен на столбце определяемого пользователем типа данных CLR, реализация этого типа должна поддерживать двоичную сортировку.

Ограничения внешнего ключа

Внешний ключ (FK) — это столбец или сочетание столбцов, которое применяется для принудительного установления связи между данными в двух таблицах с целью контроля данных, которые могут храниться в таблице внешнего ключа. Если один или несколько столбцов, в которых находится первичный ключ для одной таблицы, упоминается в одном или нескольких столбцах другой таблицы, то в ссылке внешнего ключа создается связь между двумя таблицами. Этот столбец становится внешним ключом во второй таблице.

Например, таблица имеет ссылку на внешний ключ к Sales.SalesPerson таблице, Sales.SalesOrderHeader так как между заказами на продажу и продавцами существует логическая связь. Столбец SalesPersonID в таблице SalesOrderHeader соответствует столбцу первичного ключа таблицы SalesPerson. Столбец SalesPersonID в таблице SalesOrderHeader является внешним ключом таблицы SalesPerson. Создав эту связь внешнего ключа, невозможно вставить значение SalesPersonID в SalesOrderHeader таблицу, если она еще не существует в SalesPerson таблице.

Максимальное количество таблиц и столбцов, на которые может ссылаться таблица в качестве внешних ключей (исходящих ссылок), равно 253. SQL Server 2016 (13.x) увеличивает ограничение числа других таблиц и столбцов, которые могут ссылаться на столбцы в одной таблице (входящих ссылок), с 253 до 10 000. (Требуется уровень совместимости не менее 130.) Увеличение имеет следующие ограничения:

  • Более 253 ссылок на внешний ключ поддерживаются только для DELETE операций DML. UPDATE и MERGE операции не поддерживаются.

  • Таблица со ссылкой внешнего ключа на саму себя по-прежнему ограничена 253 ссылками на внешние ключи.

  • Более 253 ссылок на внешний ключ в настоящее время недоступны для индексов columnstore, оптимизированных для памяти таблиц, Stretch Database или секционированных таблиц внешнего ключа.

    Внимание

    Stretch Database устарел в SQL Server 2022 (16.x) и База данных SQL Azure. Эта функция будет удалена в будущей версии ядро СУБД. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

Индексы ограничений внешнего ключа

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

  • Столбцы внешнего ключа часто используются в критериях соединения при совместном применении в запросах данных из связанных таблиц. Это реализуется путем сопоставления столбца или столбцов в ограничении внешнего ключа в одной таблице с одним или несколькими столбцами первичного или уникального ключа в другой таблице. Индекс позволяет ядро СУБД быстро находить связанные данные в таблице внешнего ключа. Однако создание этого индекса не требуется. Данные из двух связанных таблиц можно комбинировать, даже если между таблицами не определены ограничения первичного ключа или внешнего ключа, но связь по внешнему ключу между двумя таблицами показывает, что эти две таблицы оптимизированы для совместного применения в запросе, где ключи используются в качестве критериев.

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

Ссылочная целостность

Главная задача ограничения внешнего ключа состоит в управлении данными, которые могут быть сохранены в таблице внешнего ключа, но это ограничение контролирует также изменение данных в таблице первичного ключа. Например, при удалении строки для менеджера по продажам из таблицы Sales.SalesPerson, идентификатор которого используется в заказах на продажу в таблице Sales.SalesOrderHeader, ссылочная целостность двух таблиц будет нарушена. Заказы на продажу удаленного менеджера в таблице SalesOrderHeader станут недействительными без связи с данными в таблице SalesPerson.

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

Каскадная целостность ссылок

С помощью каскадных ограничений целостности можно определить действия, которые выполняет ядро СУБД, когда пользователь пытается удалить или обновить ключ, на который имеется точка внешних ключей. Могут быть определены следующие каскадные действия.

  • NO ACTION

    Ядро СУБД вызывает ошибку, а действие удаления или обновления строки в родительской таблице откатывается.

  • CASCADE

    Соответствующие строки обновляются или удаляются из ссылающейся таблицы, если данная строка обновляется или удаляется из родительской таблицы. CASCADE Невозможно указать, является ли столбец метки времени частью внешнего ключа или ключа, на который ссылается ссылка. ON DELETE CASCADE Не удается указать для таблицы с триггером INSTEAD OF DELETE . ON UPDATE CASCADE Нельзя указать для таблиц с INSTEAD OF UPDATE триггерами.

  • SET NULL

    Все значения, составляющие внешний ключ, задаются NULL при обновлении или удалении соответствующей строки в родительской таблице. Для выполнения этого ограничения внешние ключевые столбцы должны допускать значения NULL. Невозможно указать для таблиц с INSTEAD OF UPDATE триггерами.

  • SET DEFAULT

    Все значения, составляющие внешний ключ, при удалении или обновлении соответствующей строки родительской таблицы устанавливаются в значение по умолчанию. Для выполнения этого ограничения все внешние ключевые столбцы должны иметь определения по умолчанию. Если столбец имеет значение NULL, и нет явного набора значений по умолчанию, NULL становится неявным значением по умолчанию столбца. Невозможно указать для таблиц с INSTEAD OF UPDATE триггерами.

CASCADE, SET NULL, SET DEFAULTи NO ACTION может объединяться в таблицах, имеющих ссылочных связей друг с другом. Если ядро СУБД встречаетсяNO ACTION, он останавливается и откатывает связанные CASCADEдействия SET NULLи SET DEFAULT действия. DELETE Если оператор вызывает сочетание CASCADE, SET DEFAULTSET NULLили NO ACTION действия, все CASCADESET NULLдействия и SET DEFAULT действия применяются перед проверкой ядро СУБД для любогоNO ACTION.

Триггеры и каскадные действия ссылки

Каскадные ссылающиеся действия срабатывают AFTER UPDATE или AFTER DELETE активируются следующим образом:

  • Все каскадные действия, непосредственно вызванные исходным DELETE или UPDATE выполняются сначала.

  • Если в затронутых таблицах определены какие-либо AFTER триггеры, эти триггеры запускаются после выполнения всех каскадных действий. Эти триггеры запускаются в порядке, обратном каскадным действиям. Если в одной таблице существует несколько триггеров, они запускаются в случайном порядке, если для таблицы не существует выделенный первый или последний триггер. Этот порядок определяется процедурой sp_settriggerorder.

  • Если несколько каскадных цепочек исходят из таблицы, которая была прямым объектом UPDATE или DELETE действием, порядок, в котором эти цепочки запускают соответствующие триггеры, не указаны. Однако одна последовательность действий всегда запускает все свои триггеры до того, как это начнет делать следующая.

  • AFTER Триггер в таблице, которая является прямой целью UPDATE или DELETE действием, запускается независимо от того, влияют ли какие-либо строки. В этом случае ни на какие другие таблицы каскадирование не влияет.

  • Если любой из предыдущих триггеров выполняет UPDATE или DELETE выполняет операции с другими таблицами, эти действия могут запускать вторичные каскадные цепочки. Эти вторичные цепи обрабатываются для каждой UPDATE или DELETE операции за раз после всех триггеров во всех первичных цепях. Этот процесс может повторяться рекурсивно для последующих UPDATE или DELETE операций.

  • CREATEВыполнение операций , DELETEALTERили других операций языка определения данных (DDL) внутри триггеров может привести к срабатыванию триггеров DDL. Впоследствии это может выполнять операции DELETE или UPDATE, которые запускают дополнительные каскадные цепочки и триггеры.

  • Если ошибка создается внутри какой-либо каскадной цепочки действий, возникает ошибка, AFTER триггеры не запускаются в этой цепочке, а операция DELETE или UPDATE, создающая цепочку, откатывается.

  • Таблица с триггером INSTEAD OF не может также содержать REFERENCES предложение, указывающее каскадное действие. Однако триггер в таблице, предназначенной каскадным действием, AFTER может выполнять UPDATEINSERTинструкцию или инструкцию в другой таблице или DELETE представлении, которая запускает триггер, определенный INSTEAD OF для этого объекта.

Связанный контент