1. Обмеження первинного ключа Primary Key Constraints
2. Foreign Key Constraints Foreign Key Constraints
3. Індекси в обмеженнях зовнішнього ключа Indexes on Foreign Key Constraints
4. Посилальна цілісність Referential Integrity
5. Каскадна посилальна цілісність Cascading Referential Integrity
6. Тригери і каскадні посилальні дії Triggers and Cascading Referential Actions
7. Зв'язані задачі Related Tasks

ОБЛАСТЬ ЗАСТОСУВАННЯ: SQL Server (починаючи з 2016) База даних SQL Azure Сховище даних SQL Azure Parallel Data Warehouse APPLIES TO: SQL Server (starting with 2016) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

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

Ця тема описана в наступних розділах. This topic contains the following sections.

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

Foreign Key Constraints Foreign 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 Engine Database Engine гарантує унікальність даних шляхом автоматичного створення унікального індексу для первинних ключових стовпців. When you specify a primary key constraint for a table, the Компонент Database Engine Database 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.

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

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

• Індекс, що формується обмеженням первинного ключа, не може спричинити за собою вихід кількості індексів в таблиці за межі в 999 некластерізованних індексів і 1 кластерізованний. The index generated by a primary key constraint can not 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 Constraints Foreign 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 can not 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 Engine Database Engine швидко знаходити пов'язані дані в таблиці зовнішніх ключів. An index enables the Компонент Database Engine Database 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 can not 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 Engine Database Engine робитиме, коли користувач спробує видалити або оновити ключ, на який вказують ще існуючі зовнішні ключі. By using cascading referential integrity constraints, you can define the actions that the Компонент Database Engine Database 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 ACTION NO ACTION
Компонент Компонент Database Engine Database Engine формує помилку, після чого виконується відкат операції видалення або оновлення рядка в батьківській таблиці. The Компонент Database Engine Database Engine raises an error and the delete or update action on the row in the parent table is rolled back.

CASCADE CASCADE
Відповідні рядки оновлюються або видаляються з посилається таблиці, якщо даний рядок оновлюється або видаляється з батьківської таблиці. Corresponding rows are updated or deleted in the referencing table when that row is updated or deleted in the parent table. Значення CASCADE не може бути зазначено, якщо стовпець типу timestamp є частиною зовнішнього або посилального ключа. CASCADE can not 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 can not be specified for a table that has an INSTEAD OF DELETE trigger. Пропозиція ON UPDATE CASCADE не може бути задано стосовно до таблиць, для яких визначені тригери INSTEAD OF UPDATE. ON UPDATE CASCADE can not be specified for tables that have INSTEAD OF UPDATE triggers.

SET NULL SET 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. Can not be specified for tables that have INSTEAD OF UPDATE triggers.

SET DEFAULT SET 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. Can not 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 Engine Database Engine виявляє ключове слово NO ACTION, воно зупинить і зробить відкат пов'язаних операцій CASCADE, SET NULL і SET DEFAULT. If the Компонент Database Engine Database 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 Engine Database 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 Engine Database 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 can not 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.