Ограничения первичных и внешних ключейPrimary and Foreign Key Constraints

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure нетAzure Synapse Analytics (хранилище данных SQL) нетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Первичные и внешние ключи представляют собой два типа ограничений, которые могут использоваться для обеспечения целостности данных в таблицах SQL ServerSQL Server .Primary keys and foreign keys are two types of constraints that can be used to enforce data integrity in SQL ServerSQL Server tables. Это важные объекты базы данных.These are important database objects.

Эта тема описана в следующих разделах.This topic contains the following sections.

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

Foreign Key ConstraintsForeign Key Constraints

Связанные задачиRelated Tasks

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

Обычно в таблице есть столбец или сочетание столбцов, содержащих значения, уникально определяющие каждую строку таблицы.A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. Этот столбец, или столбцы, называются первичным ключом (PK) таблицы и обеспечивает целостность сущности таблицы.This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. Ограничения первичного ключа часто определяются в столбце идентификаторов, поскольку гарантируют уникальность данных.Because primary key constraints guarantee unique data, they are frequently defined on an identity column.

При задании ограничения первичного ключа для таблицы компонента Компонент Database EngineDatabase Engine гарантирует уникальность данных путем автоматического создания уникального индекса для первичных ключевых столбцов.When you specify a primary key constraint for a table, the Компонент Database EngineDatabase Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. Этот индекс также обеспечивает быстрый доступ к данным при использовании первичного ключа в запросах.This index also permits fast access to data when the primary key is used in queries. Если ограничение первичного ключа задано более чем для одного столбца, то значения могут дублироваться в пределах одного столбца, но каждое сочетание значений всех столбцов в определении ограничения первичного ключа должно быть уникальным.If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique.

Как показано на следующем рисунке, столбцы ProductID и VendorID в таблице Purchasing.ProductVendor формируют составное ограничение первичного ключа для данной таблицы.As shown in the following illustration, the ProductID and VendorID columns in the Purchasing.ProductVendor table form a composite primary key constraint for this table. При этом гарантируется, что каждая строка в таблице ProductVendor имеет уникальное сочетание значений ProductID и VendorID.This makes sure that every row in the ProductVendor table has a unique combination of ProductID and VendorID. Это предотвращает вставку повторяющихся строк.This prevents the insertion of duplicate rows.

Составное ограничение PRIMARY KEYComposite PRIMARY KEY constraint

  • В таблице возможно наличие только одного ограничения по первичному ключу.A table can contain only one primary key constraint.

  • Первичный ключ не может включать больше 16 столбцов, а общая длина ключа не может превышать 900 байт.A primary key cannot exceed 16 columns and a total key length of 900 bytes.

  • Индекс, формируемый ограничением первичного ключа, не может повлечь за собой выход количества индексов в таблице за пределы в 999 некластеризованных индексов и 1 кластеризованный.The index generated by a primary key constraint cannot cause the number of indexes on the table to exceed 999 nonclustered indexes and 1 clustered index.

  • Если для ограничения первичного ключа не указано, является ли индекс кластеризованным или некластеризованным, то создается кластеризованный индекс, если таковой отсутствует в таблице.If clustered or nonclustered is not specified for a primary key constraint, clustered is used if there no clustered index on the table.

  • Все столбцы с ограничением первичного ключа должны быть определены как не допускающие значения NULL.All columns defined within a primary key constraint must be defined as not null. Если допустимость значения NULL не указана, то все столбцы c ограничением первичного ключа устанавливаются как не допускающие значения NULL.If nullability is not specified, all columns participating in a primary key constraint have their nullability set to not null.

  • Если первичный ключ определен на столбце определяемого пользователем типа данных CLR, реализация этого типа должна поддерживать двоичную сортировку.If a primary key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering.

Foreign Key ConstraintsForeign Key Constraints

Внешний ключ (FK) — это столбец или сочетание столбцов, которое применяется для принудительного установления связи между данными в двух таблицах с целью контроля данных, которые могут храниться в таблице внешнего ключа.A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. Если один или несколько столбцов, в которых находится первичный ключ для одной таблицы, упоминается в одном или нескольких столбцах другой таблицы, то в ссылке внешнего ключа создается связь между двумя таблицами.In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. Этот столбец становится внешним ключом во второй таблице.This column becomes a foreign key in the second table.

Например, таблица Sales.SalesOrderHeader связана с таблицей Sales.SalesPerson с помощью внешнего ключа, так как существует логическая связь между заказами на продажу и менеджерами по продажам.For example, the Sales.SalesOrderHeader table has a foreign key link to the Sales.SalesPerson table because there is a logical relationship between sales orders and salespeople. Столбец SalesPersonID в таблице Sales.SalesOrderHeader соответствует первичному ключевому столбцу в таблице SalesPerson .The SalesPersonID column in the SalesOrderHeader table matches the primary key column of the SalesPerson table. Столбец SalesPersonID в таблице Sales.SalesOrderHeader является внешним ключом для таблицы SalesPerson .The SalesPersonID column in the SalesOrderHeader table is the foreign key to the SalesPerson table. С помощью установления данной связи по внешнему ключу значение для SalesPersonID не может быть вставлено в таблицу SalesOrderHeader , если оно в настоящий момент не содержится в таблице SalesPerson .By creating this foreign key relationship, a value for SalesPersonID cannot be inserted into the SalesOrderHeader table if it does not already exist in the SalesPerson table.

Максимальное количество таблиц и столбцов, на которые может ссылаться таблица в качестве внешних ключей (исходящих ссылок), равно 253.A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x);SQL Server 2016 (13.x) увеличивает ограничение на количество других таблиц и столбцов, которые могут ссылаться на столбцы в одной таблице (входящие ссылки), с 253 до 10 000.increases the limit for the number of other table and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (Требуется уровень совместимости не менее 130.) Увеличение имеет следующие ограничения:(Requires at least 130 compatibility level.) The increase has the following restrictions:

  • Превышение 253 ссылок на внешние ключи поддерживается только для операций DML DELETE.Greater than 253 foreign key references are only supported for DELETE DML operations. Операции UPDATE и MERGE не поддерживаются.UPDATE and MERGE operations are not supported.

  • Таблица со ссылкой внешнего ключа на саму себя по-прежнему ограничена 253 ссылками на внешние ключи.A table with a foreign key reference to itself is still limited to 253 foreign key references.

  • Превышение 253 ссылок на внешние ключи в настоящее время недоступно для индексов columnstore, оптимизированных для памяти таблиц, базы данных Stretch или секционированных таблиц внешнего ключа.Greater than 253 foreign key references are not currently available for columnstore indexes, memory-optimized tables, Stretch Database, or partitioned foreign key tables.

Индексы в ограничениях внешнего ключаIndexes on Foreign Key Constraints

В отличие от ограничений первичного ключа, при создании ограничения внешнего ключа соответствующий индекс автоматически не создается.Unlike primary key constraints, creating a foreign key constraint does not automatically create a corresponding index. Тем не менее, часто возникает необходимость создания индекса для внешнего ключа вручную по следующим причинам:However, manually creating an index on a foreign key is often useful for the following reasons:

  • Столбцы внешнего ключа часто используются в критериях соединения при совместном применении в запросах данных из связанных таблиц. Это реализуется путем сопоставления столбца или столбцов в ограничении внешнего ключа в одной таблице с одним или несколькими столбцами первичного или уникального ключа в другой таблице.Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the foreign key constraint of one table with the primary or unique key column or columns in the other table. Индекс позволяет компоненту Компонент Database EngineDatabase Engine быстро находить связанные данные в таблице внешних ключей.An index enables the Компонент Database EngineDatabase Engine to quickly find related data in the foreign key table. Впрочем, создание индекса не является обязательным.However, creating this index is not required. Данные из двух связанных таблиц можно комбинировать, даже если между таблицами не определены ограничения первичного ключа или внешнего ключа, но связь по внешнему ключу между двумя таблицами показывает, что эти две таблицы оптимизированы для совместного применения в запросе, где ключи используются в качестве критериев.Data from two related tables can be combined even if no primary key or foreign key constraints are defined between the tables, but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria.

  • С помощью ограничений внешнего ключа в связанных таблицах проверяются изменения ограничений первичного ключа.Changes to primary key constraints are checked with foreign key constraints in related tables.

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

Главная задача ограничения внешнего ключа состоит в управлении данными, которые могут быть сохранены в таблице внешнего ключа, но это ограничение контролирует также изменение данных в таблице первичного ключа.Although the main purpose of a foreign key constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. Например, при удалении строки для менеджера по продажам из таблицы Sales.SalesPerson , идентификатор которого используется в заказах на продажу в таблице Sales.SalesOrderHeader , ссылочная целостность двух таблиц будет нарушена. Заказы на продажу удаленного менеджера в таблице SalesOrderHeader станут недействительными без связи с данными в таблице SalesPerson .For example, if the row for a salesperson is deleted from the Sales.SalesPerson table, and the salesperson's ID is used for sales orders in the Sales.SalesOrderHeader table, the relational integrity between the two tables is broken; the deleted salesperson's sales orders are orphaned in the SalesOrderHeader table without a link to the data in the SalesPerson table.

Ограничение внешнего ключа предотвращает возникновение этой ситуации.A foreign key constraint prevents this situation. Ограничение обеспечивает целостность ссылок следующим образом: оно запрещает изменение данных в таблице первичного ключа, если такие изменения сделают недопустимой ссылку в таблице внешнего ключа.The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. Если при попытке удалить строку в таблице первичного ключа или изменить значение этого ключа будет обнаружено, что удаленному или измененному значению первичного ключа соответствует определенное значение в ограничении внешнего ключа в другой таблице, то действие выполнено не будет.If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the foreign key constraint of another table. Для успешного изменения или удаления строки с ограничением внешнего ключа необходимо сначала удалить данные внешнего ключа в таблице внешнего ключа либо изменить в таблице внешнего ключа данные, которые связывают внешний ключ с данными другого первичного ключа.To successfully change or delete a row in a foreign key constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.

Каскадная ссылочная целостностьCascading Referential Integrity

С помощью каскадных ограничений ссылочной целостности можно определять действия, которые компонент Компонент Database EngineDatabase Engine будет предпринимать, когда пользователь попытается удалить или обновить ключ, на который указывают еще существующие внешние ключи.By using cascading referential integrity constraints, you can define the actions that the Компонент Database EngineDatabase Engine takes when a user tries to delete or update a key to which existing foreign keys point. Могут быть определены следующие каскадные действия.The following cascading actions can be defined.

NO ACTIONNO ACTION
Компонент Компонент Database EngineDatabase Engine формирует ошибку, после чего выполняется откат операции удаления или обновления строки в родительской таблице.The Компонент Database EngineDatabase Engine raises an error and the delete or update action on the row in the parent table is rolled back.

CASCADECASCADE
Соответствующие строки обновляются или удаляются из ссылающейся таблицы, если данная строка обновляется или удаляется из родительской таблицы.Corresponding rows are updated or deleted in the referencing table when that row is updated or deleted in the parent table. Значение CASCADE не может быть указано, если столбец типа timestamp является частью внешнего или ссылочного ключа.CASCADE cannot be specified if a timestamp column is part of either the foreign key or the referenced key. Действие ON DELETE CASCADE не может быть указано в таблице, для которой определен триггер INSTEAD OF DELETE.ON DELETE CASCADE cannot be specified for a table that has an INSTEAD OF DELETE trigger. Предложение ON UPDATE CASCADE не может быть задано применительно к таблицам, для которых определены триггеры INSTEAD OF UPDATE.ON UPDATE CASCADE cannot be specified for tables that have INSTEAD OF UPDATE triggers.

SET NULLSET NULL
Всем значениям, составляющим внешний ключ, присваивается значение NULL, когда обновляется или удаляется соответствующая строка в родительской таблице.All the values that make up the foreign key are set to NULL when the corresponding row in the parent table is updated or deleted. Для выполнения этого ограничения внешние ключевые столбцы должны допускать значения NULL.For this constraint to execute, the foreign key columns must be nullable. Не может быть задано применительно к таблицам, для которых определены триггеры INSTEAD OF UPDATE.Cannot be specified for tables that have INSTEAD OF UPDATE triggers.

SET DEFAULTSET DEFAULT
Все значения, составляющие внешний ключ, при удалении или обновлении соответствующей строки родительской таблицы устанавливаются в значение по умолчанию.All the values that make up the foreign key are set to their default values if the corresponding row in the parent table is updated or deleted. Для выполнения этого ограничения все внешние ключевые столбцы должны иметь определения по умолчанию.For this constraint to execute, all foreign key columns must have default definitions. Если столбец допускает значения NULL и значение по умолчанию явно не определено, значением столбца по умолчанию становится NULL.If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column. Не может быть задано применительно к таблицам, для которых определены триггеры INSTEAD OF UPDATE.Cannot be specified for tables that have INSTEAD OF UPDATE triggers.

Ключевые слова CASCADE, SET NULL, SET DEFAULT и NO ACTION можно сочетать в таблицах, имеющих взаимные ссылочные связи.CASCADE, SET NULL, SET DEFAULT and NO ACTION can be combined on tables that have referential relationships with each other. Если компонент Компонент Database EngineDatabase Engine обнаруживает ключевое слово NO ACTION, оно остановит и произведет откат связанных операций CASCADE, SET NULL и SET DEFAULT.If the Компонент Database EngineDatabase Engine encounters NO ACTION, it stops and rolls back related CASCADE, SET NULL and SET DEFAULT actions. Если инструкция DELETE содержит сочетание ключевых слов CASCADE, SET NULL, SET DEFAULT и NO ACTION, то все операции CASCADE, SET NULL и SET DEFAULT выполняются перед поиском компонентом Компонент Database EngineDatabase Engine операции NO ACTION.When a DELETE statement causes a combination of CASCADE, SET NULL, SET DEFAULT and NO ACTION actions, all the CASCADE, SET NULL and SET DEFAULT actions are applied before the Компонент Database EngineDatabase Engine checks for any NO ACTION.

Триггеры и каскадные ссылочные действияTriggers and Cascading Referential Actions

Каскадные ссылочные действия запускают триггеры AFTER UPDATE или AFTER DELETE следующим образом:Cascading referential actions fire the AFTER UPDATE or AFTER DELETE triggers in the following manner:

  • Все каскадные ссылочные действия, прямо вызванные исходными инструкциями DELETE или UPDATE, выполняются первыми.All the cascading referential actions directly caused by the original DELETE or UPDATE are performed first.

  • Если есть какие-либо триггеры AFTER, определенные для измененных таблиц, эти триггеры запускаются после выполнения всех каскадных действий.If there are any AFTER triggers defined on the affected tables, these triggers fire after all cascading actions are performed. Эти триггеры запускаются в порядке, обратном каскадным действиям.These triggers fire in opposite order of the cascading action. Если для одной таблицы определены несколько триггеров, они запускаются в случайном порядке, если только не указаны выделенные первый и последний триггеры таблицы.If there are multiple triggers on a single table, they fire in random order, unless there is a dedicated first or last trigger for the table. Этот порядок определяется процедурой sp_settriggerorder.This order is as specified by using sp_settriggerorder.

  • Если последовательности каскадных действий происходят из таблицы, которая была непосредственной целью действий DELETE или UPDATE, порядок запуска триггеров этими последовательностями действий не определен.If multiple cascading chains originate from the table that was the direct target of an UPDATE or DELETE action, the order in which these chains fire their respective triggers is unspecified. Однако одна последовательность действий всегда запускает все свои триггеры до того, как это начнет делать следующая.However, one chain always fires all its triggers before another chain starts firing.

  • Триггер AFTER таблицы, являвшейся непосредственной целью действий DELETE или UPDATE, запускается вне зависимости от того, были ли изменены хоть какие-нибудь строки.An AFTER trigger on the table that is the direct target of an UPDATE or DELETE action fires regardless of whether any rows are affected. В этом случае ни на какие другие таблицы каскадирование не влияет.There are no other tables affected by cascading in this case.

  • Если один из предыдущих триггеров выполняет операции DELETE или UPDATE над другими таблицами, эти операции могут вызывать собственные последовательности каскадных действий.If any one of the previous triggers perform UPDATE or DELETE operations on other tables, these actions can start secondary cascading chains. Эти вторичные последовательности действий обрабатываются для каждой операции DELETE или UPDATE после выполнения всех триггеров первичных последовательностей действий.These secondary chains are processed for each UPDATE or DELETE operation at a time after all triggers on all primary chains fire. Этот процесс может рекурсивно повторяться для последующих операций DELETE или UPDATE.This process may be recursively repeated for subsequent UPDATE or DELETE operations.

  • Выполнение операций CREATE, ALTER, DELETE или других операций языка DDL внутри триггеров может привести к запуску триггеров DDL.Performing CREATE, ALTER, DELETE, or other data definition language (DDL) operations inside the triggers may cause DDL triggers to fire. Это может привести к дальнейшим операциям DELETE или UPDATE, которые начнут дополнительные последовательности каскадных действий и запустят свои триггеры.This may subsequently perform DELETE or UPDATE operations that start additional cascading chains and triggers.

  • Если в любой конкретной последовательности каскадных ссылочных действий произойдет ошибка, в этой последовательности не будут запущены никакие триггеры AFTER, а для операций DELETE или UPDATE, создаваемых этой последовательностью, будет выполнен откат.If an error is generated inside any particular cascading referential action chain, an error is raised, no AFTER triggers are fired in that chain, and the DELETE or UPDATE operation that created the chain is rolled back.

  • У таблицы, для которой определен триггер INSTEAD OF, может также быть предложение REFERENCES, указывающее конкретное каскадное действие.A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause that specifies a cascading action. Однако триггер AFTER целевой таблицы каскадного действия может выполнить инструкцию INSERT, UPDATE или DELETE для другой таблицы или представления, которое запустит триггер INSTEAD OF для этого объекта.However, an AFTER trigger on a table targeted by a cascading action can execute an INSERT, UPDATE, or DELETE statement on another table or view that fires an INSTEAD OF trigger defined on that object.

Связанные задачиRelated Tasks

В следующей таблице перечислены общие задачи, связанные с ограничениями первичного ключа и внешнего ключа.The following table lists the common tasks associated with primary key and foreign key constraints.

ЗадачаTask РазделTopic
Описывает, как создать первичный ключ.Describes how to create a primary key. Создание первичных ключейCreate Primary Keys
Описывает, как удалить первичный ключ.Describes how to delete a primary key. Удаление первичных ключейDelete Primary Keys
Описывает, как изменить первичный ключ.Describes how to modify a primary key. Изменение первичных ключейModify Primary Keys
Описывается создание связей внешнего ключаDescribes how to create foreign key relationships Создание связей по внешнему ключуCreate Foreign Key Relationships
Описывает, как изменить связи внешнего ключа.Describes how to modify foreign key relationships. Изменение связей по внешнему ключуModify Foreign Key Relationships
Описывает, как удалить связи внешнего ключа.Describes how to delete foreign key relationships. Удаление связей по внешнему ключуDelete Foreign Key Relationships
Описывает, как просматривать свойства внешнего ключа.Describes how to view foreign key properties. Просмотр свойств внешнего ключаView Foreign Key Properties
Описывает, как отключить ограничения внешнего ключа для репликации.Describes how to disable foreign key constraints for replication. Отключение ограничений внешнего ключа для репликацииDisable Foreign Key Constraints for Replication
Описывает, как отключить ограничения внешнего ключа на время выполнения инструкций INSERT и UPDATE.Describes how to disable foreign key constraints during an INSERT or UPDATE statement. Отключение ограничений внешнего ключа для инструкций INSERT и UPDATEDisable Foreign Key Constraints with INSERT and UPDATE Statements