Ограничение foreign key sql ошибка

Finding out why Foreign key creation fail

When MySQL is unable to create a Foreign Key, it throws out this generic error message:

ERROR 1215 (HY000): Cannot add foreign key constraint

– The most useful error message ever.

Fortunately, MySQL has this useful command that can give the actual reason about why it could not create the Foreign Key.

mysql> SHOW ENGINE INNODB STATUS;

That will print out lots of output but the part we are interested in is under the heading ‘LATEST FOREIGN KEY ERROR’:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2020-08-29 13:40:56 0x7f3cb452e700 Error in foreign key constraint of table test_database/my_table:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
CONSTRAINT idx_name FOREIGN KEY (employee_id) REFERENCES employees (id)
The index in the foreign key in table is idx_name
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

This output could give you some clue about the actual reason why MySQL could not create your Foreign Key

Reason #1 – Missing unique index on the referenced table

This is probably the most common reason why MySQL won’t create your Foreign Key constraint. Let’s look at an example with a new database and new tables:

In the all below examples, we’ll use a simple ‘Employee to Department” relationship:

mysql> CREATE DATABASE foreign_key_1;
Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;
Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (0.07 sec)

As you may have noticed, we have not created the table with PRIMARY KEY or unique indexes. Now let’s try to create Foreign Key constraint between employees.department_id column and departments.id column:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

Let’s look at the detailed error:

mysql> SHOW ENGINE INNODB STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2020-08-31 09:25:13 0x7fddc805f700 Error in foreign key constraint of table foreign_key_1/#sql-5ed_49b:
FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

This is because we don’t have any unique index on the referenced table i.e. departments. We have two ways of fixing this:

Option 1: Primary Keys

Let’s fix this by adding a primary key departments.id

mysql> ALTER TABLE departments ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

Option 2: Unique Index

mysql> CREATE UNIQUE INDEX idx_department_id ON departments(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

Reason #2 – Different data types on the columns

MySQL requires the columns involved in the foreign key to be of the same data types.

mysql> CREATE DATABASE foreign_key_1;
Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;
Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id char(20),
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

You may have noticed that employees.department_id is int while departments.id is char(20). Let’s try to create a foreign key now:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

Let’s fix the type of departments.id and try to create the foreign key again:

mysql> ALTER TABLE departments MODIFY id INT;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

It works now!

Reason #3 – Different collation/charset type on the table

This is a surprising reason and hard to find out. Let’s create two tables with different collation (or also called charset):

Let’s start from scratch to explain this scenario:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB CHARACTER SET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB CHARACTER SET=latin1;
Query OK, 0 rows affected (0.08 sec)

You may notice that we are using a different character set (utf8 and latin1` for both these tables. Let’s try to create the foreign key:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

It failed because of different character sets. Let’s fix that.

mysql> SET foreign_key_checks = 0; ALTER TABLE departments CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

If you have many tables with a different collation/character set, use this script to generate a list of commands to fix all tables at once:

mysql --database=your_database -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}'

Reason #4 – Different collation types on the columns

This is a rare reason, similar to reason #3 above but at a column level.

Let’s try to reproduce this from scratch:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id char(26) CHARACTER SET utf8,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE departments(
    ->     id char(26) CHARACTER SET latin1,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

We are using a different character set for employees.department_id and departments.id (utf8 and latin1). Let’s check if the Foreign Key can be created:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

Nope, as expected. Let’s fix that by changing the character set of departments.id to match with employees.department_id:

mysql> ALTER TABLE departments MODIFY id CHAR(26) CHARACTER SET utf8;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

It works now!

Reason #5 -Inconsistent data

This would be the most obvious reason. A foreign key is to ensure that your data remains consistent between the parent and the child table. So when you are creating the foreign key, the existing data is expected to be already consistent.

Let’s setup some inconsistent data to reproduce this problem:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

Let’s insert a department_id in employees table that will not exist in departments.id:

mysql> INSERT INTO employees VALUES (1, 'Amber', 145);
Query OK, 1 row affected (0.01 sec)

Let’s create a foreign key now and see if it works:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`foreign_key_1`.`#sql-5ed_49b`, CONSTRAINT `fk_department_id` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`))

This error message is atleast more useful. We can fix this in two ways. Either by adding the missing department in departments table or by deleting all the employees with the missing department. We’ll do the first option now:

mysql> INSERT INTO departments VALUES (145, 'HR');
Query OK, 1 row affected (0.00 sec)

Let’s try to create the Foreign Key again:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 1 row affected (0.24 sec)
Records: 1  Duplicates: 0  Warnings: 0

It worked this time.

So we have seen 5 different ways a Foreign Key creation can fail and possible solutions of how we can fix them. If you have encountered a reason not listed above, add them in the comments.

If you are using MySQL 8.x, the error message will be a little different:

SQLSTATE[HY000]: General error: 3780 Referencing column 'column' and referenced column 'id' in foreign key constraint 'idx_column_id' are incompatible. 

Although the other answers are quite helpful, just wanted to share my experience as well.

I faced the issue when I had deleted a table whose id was already being referenced as foreign key in other tables (with data) and tried to recreate/import the table with some additional columns.

The query for recreation (generated in phpMyAdmin) looked like the following:

CREATE TABLE `the_table` (
  `id` int(11) NOT NULL,            /* No PRIMARY KEY index */  
  `name` varchar(255) NOT NULL,
  `name_fa` varchar(255) NOT NULL,
  `name_pa` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

... /* SOME DATA DUMP OPERATION */

ALTER TABLE `the_table`
  ADD PRIMARY KEY (`id`), /* PRIMARY KEY INDEX */
  ADD UNIQUE KEY `uk_acu_donor_name` (`name`);

As you may notice, the PRIMARY KEY index was set after the creation (and insertion of data) which was causing the problem.

Solution

The solution was to add the PRIMARY KEY index on table definition query for the id which was being referenced as foreign key, while also removing it from the ALTER TABLE part where indexes were being set:

CREATE TABLE `the_table` (
  `id` int(11) NOT NULL PRIMARY KEY,            /* <<== PRIMARY KEY INDEX ON CREATION */  
  `name` varchar(255) NOT NULL,
  `name_fa` varchar(255) NOT NULL,
  `name_pa` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Although the other answers are quite helpful, just wanted to share my experience as well.

I faced the issue when I had deleted a table whose id was already being referenced as foreign key in other tables (with data) and tried to recreate/import the table with some additional columns.

The query for recreation (generated in phpMyAdmin) looked like the following:

CREATE TABLE `the_table` (
  `id` int(11) NOT NULL,            /* No PRIMARY KEY index */  
  `name` varchar(255) NOT NULL,
  `name_fa` varchar(255) NOT NULL,
  `name_pa` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

... /* SOME DATA DUMP OPERATION */

ALTER TABLE `the_table`
  ADD PRIMARY KEY (`id`), /* PRIMARY KEY INDEX */
  ADD UNIQUE KEY `uk_acu_donor_name` (`name`);

As you may notice, the PRIMARY KEY index was set after the creation (and insertion of data) which was causing the problem.

Solution

The solution was to add the PRIMARY KEY index on table definition query for the id which was being referenced as foreign key, while also removing it from the ALTER TABLE part where indexes were being set:

CREATE TABLE `the_table` (
  `id` int(11) NOT NULL PRIMARY KEY,            /* <<== PRIMARY KEY INDEX ON CREATION */  
  `name` varchar(255) NOT NULL,
  `name_fa` varchar(255) NOT NULL,
  `name_pa` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Дата: 2.12.2016

Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru

Функционирование внешних ключей в MySQL имеет много нюансов и ограничений из-за чего существует немало возможностей получить ошибку при работе с ними. Одна из проблем состоит в том, что сообщения об ошибках содержат мало полезной информации и не указывают причину возникновения ошибки. В данной статье дается объяснение как получить дополнительную информацию об ошибке и приведен полный список причин возникновения ошибок внешних ключей. Каждая причина снабжена уникальным буквенно-цифровым кодом (А4, Б1, ..), использующимся в сводной таблице в конце статьи, которая поможет вам быстро диагностировать проблему.

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

В MySQL внешние ключи не реализованы на уровне сервера, их поддержка зависит от используемого хранилища данных. Содержание статьи справедливо для InnoDB (в том числе и для XtraDB).

Как получить больше данных об ошибке

После получения ошибки выполните SHOW ENGINE INNODB STATUS и смотрите содержимое секции LATEST FOREIGN KEY ERROR. Этот способ имеет следующие недостатки:

• требует привилегии SUPER
• содержит информацию о последней ошибке, связанной с внешними ключами, из-за чего нужно выполнять SHOW ENGINE INNODB STATUS сразу после возникновения ошибки, что не всегда удобно/возможно
• используются внутренние имена таблиц (например, ‘test.#sql-d88_b’), что затрудняет диагностику
• порой содержит мало полезной информации или таковая вообще отсутствует.

Альтернатива: использовать MariaDB версий больше 5.5.45 и 10.0.21, в которых сообщения об ошибках значительно улучшены и указывают причину возникновения ошибки.

Errno 150

Если в сообщении об ошибке содержится errno 150 (или errno 121), значит парсер MySQL не смог распознать ошибку и передал команду (create/alter) на выполнение в InnoDB. В этом разделе перечислены ситуации, приводящие к ошибкам, содержащим errno 150.

А1. Нет индекса в родительской таблице. Набор полей, на которые ссылается дочерняя таблица, должен быть проиндексирован (или являться левой частью другого индекса). Порядок полей в индексе должен быть таким же как в определении внешнего ключа. Сюда же относится случай отсутствия нужной колонки в родительской таблице (нет колонки, нет и индекса).

Неочевидный момент: на колонке родительской таблицы есть индекс — полнотекстовый (fulltext). Но внешний ключ всё равно не создается и сервер ругается на отсутствие индекса. Это происходит потому, что индекс должен быть обычным (btree).

Другой неочевидный момент: на колонке родительской таблицы есть индекс — префиксный. Но внешний ключ всё равно не создается и сервер ругается на отсутствие индекса. Это происходит потому, что индекс должен быть определен на всей длине колонки.

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

А2. Родительская таблица не найдена в словаре данных InnoDB. Это означает, что родительская таблица должна существовать и быть постоянной InnoDB таблицей. Не временной InnoDB таблицей, так как информация о временных таблицах не сохраняется в словаре данных InnoDB. И уж тем более не представлением.

А3. Синтаксическая ошибка. Внешние ключи реализованы на уровне хранилища, и в старых версиях парсер сервера MySQL не распознавал синтаксические ошибки внешних ключей, из-за чего их было трудно идентифицировать.

А4. Несовпадение типов данных. Столбцы дочерней таблицы, входящие в определение внешнего ключа, должны иметь такие же типы данных, что и столбцы родительской таблицы, на которые они ссылаются, вплоть до атрибутов: знак и кодировка/сопоставление.

А5. Некорректно задано действие внешнего ключа. Если в определении внешнего ключа указано ON UPDATE SET NULL и/или ON DELETE SET NULL, то соответствующие столбцы дочерней таблицы не должны быть определены как NOT NULL.

А6. Дочерняя таблица является временной InnoDB таблицей. Внешние ключи можно создавать только в постоянной, несекционированной InnoDB таблице.

А7. Родительская таблица является секционированной таблицей. На данный момент (MySQL 5.7 и MariaDB 10.1) внешние ключи не поддерживаются для секционированных таблиц (partitioned tables). Иными словами, ни родительская, ни дочерняя таблица не должны иметь секции. В случае, когда внешний ключ ссылается на секционированную таблицу диагностика ошибки затруднена ошибкой вывода show engine innodb status:

Errno 121

Такой результат возникает только в одном случае.

Б1. Неуникальное имя ограничения. Обратите внимание: речь не о имени внешнего ключа. Если при создании внешнего ключа вы указываете не обязательное ключевое слово CONSTRAINT, то идущий после него идентификатор должен быть уникальным в пределах базы данных.

Нет ошибок

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

В1. Дочерняя таблица не является InnoDB таблицей. В этом случае для совместимости с другими субд парсер MySQL просто проигнорирует конструкцию внешнего ключа.

В2. Не соответствует синтаксису MySQL. Стандарт SQL разрешает указывать внешний ключ сразу при объявлении колонки с помощью конструкции REFERENCES (например, … a int references t1(a), …), однако MySQL игнорирует такую форму записи. Единственный способ создать в нем внешний ключ — это использовать отдельный блок FOREIGN KEY:

Несоответствие данных

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

Г1. Удаление родительской таблицы. Нельзя удалить родительскую таблицу при наличии внешнего ключа.

Удаление следует понимать в расширенном варианте как удаление из множества InnoDB таблиц. Например, если мы сменим (alter table) движок родительской таблицы на MyISAM, то с точки зрения ограничения внешнего ключа родительская таблица перестанет существовать (т.к. она должна быть постоянной innodb таблицей):

alter table t1 engine=myisam;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Сначала нужно удалить внешний ключ (или всю дочернюю таблицу, что удалит в том числе и внешний ключ). Если вы не знаете какие таблицы являются дочерними для заданной таблицы, то это можно определить через запрос к information_schema:

select table_name from information_schema.key_column_usage
where table_schema = «test» and references_table_name = «t1»;

Г2. Изменение данных в родительской таблице. Если в определении внешнего ключа не задано действие при update/delete, то такие операции над родительской таблицей могут привести к несогласованности данных, т.е. появлению в дочерней таблице записей не имеющих соответствия в родительской таблице.

Г3. Изменение данных в дочерней таблице. Если insert/update записи в дочерней таблицы приводит к несогласованности данных, то

Г4. Добавление внешнего ключа на не пустую таблицу. При попытке добавить внешний ключ на таблицу, в которой есть записи, не удовлетворяющие условию внешнего ключа (т.е. не имеющие соответствия в родительской таблице), будет ошибка:

Г5. Не уникальный ключ в родительской таблице. По стандарту SQL набор полей, на которые ссылается внешний ключ, должен быть уникальным. Однако, реализация внешних ключей в InnoDB позволяет иметь несколько «родителей». Из-за этого возникает трудно диагностируемая ошибка:

Сводная таблица

По вертикали расположены коды ошибок MySQL, которые возникают при работе с внешними ключами («нет ошибок» соответствует ситуации, когда сервер не генерирует ошибку, но и не создает внешний ключ). По горизонтали — идентификаторы причин, которые могут привести к ошибке. Плюсы на пересечении указывают какие причины приводят к той или иной ошибке.

P.S. Если ваш случай не рассмотрен в статье, то задавайте вопрос на форуме SQLinfo. Вам ответят, а статья будет расширена.

Дата публикации: 2.12.2016

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

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

Это применимо, используете ли вы блокировки read committed (чтение зафиксированных транзакций) или версионную изоляцию снимков read committed snapshot isolation (RCSI). Обе могут приводить к блокировкам, когда связи внешних ключей проверяются ядром SQL Server.

Имеется дополнительное предостережение в случае изоляции снимка (SI). Фактически та же самая проблема может привести к неожиданным (и, возможно, нелогичным) сбоям транзакций из-за явных конфликтов обновления.

Статья состоит из двух частей. В первой части рассматривается блокировка внешних ключей при уровнях изоляции read committed и read committed snapshot isolation. Вторая часть посвящена связанным с обновлением конфликтам при изоляции снимка.

1. Проверки блокировки внешнего ключа

Давайте сначала посмотрим, как структура индекса может повлиять на блокировку из-за проверок внешнего ключа.

Следующий пример должен запускаться при изоляции read committed. По умолчанию для SQL Server используется блокировка read committed; а для Azure SQL Database — RCSI. Выбирайте то, что вам нравится, или выполняйте скрипты по разу для каждой установки, чтобы убедиться, что поведение то же самое.

-- Использование блокировки read committed

ALTER DATABASE CURRENT

    SET READ_COMMITTED_SNAPSHOT OFF;

-- Или используйте row-versioning read committed

ALTER DATABASE CURRENT

    SET READ_COMMITTED_SNAPSHOT ON;

Создайте две таблицы, связанных по внешнему ключу:

CREATE TABLE dbo.Parent

(

    ParentID integer NOT NULL,

    ParentNaturalKey varchar(10) NOT NULL,

    ParentValue integer NOT NULL,

    CONSTRAINT [PK dbo.Parent ParentID]

        PRIMARY KEY (ParentID),

    CONSTRAINT [AK dbo.Parent ParentNaturalKey]

        UNIQUE (ParentNaturalKey)

);

CREATE TABLE dbo.Child 

(

    ChildID integer NOT NULL,

    ChildNaturalKey varchar(10) NOT NULL,

    ChildValue integer NOT NULL,

    ParentID integer NULL,

     CONSTRAINT [PK dbo.Child ChildID]

        PRIMARY KEY (ChildID),

     CONSTRAINT [AK dbo.Child ChildNaturalKey]

        UNIQUE (ChildNaturalKey),

     CONSTRAINT [FK dbo.Child to dbo.Parent]

        FOREIGN KEY (ParentID)

            REFERENCES dbo.Parent (ParentID)

);

Добавьте строку в родительскую таблицу:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE

    @ParentID integer = 1,

    @ParentNaturalKey varchar(10) = 'PNK1',

    @ParentValue integer = 100;

INSERT dbo.Parent 

(

    ParentID, 

    ParentNaturalKey, 

    ParentValue

) 

VALUES 

(

    @ParentID, 

    @ParentNaturalKey, 

    @ParentValue

);

На втором подключении обновите неключевой атрибут родительской таблицы ParentValue внутри транзакции, но пока не делайте commit:

DECLARE

    @ParentID integer = 1,

    @ParentNaturalKey varchar(10) = 'PNK1',

    @ParentValue integer = 200;

BEGIN TRANSACTION;

    UPDATE dbo.Parent 

    SET ParentValue = @ParentValue 

    WHERE ParentID = @ParentID;

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

Вернитесь на первое подключение и попытайтесь добавить дочернюю запись:

DECLARE

    @ChildID integer = 101,

    @ChildNaturalKey varchar(10) = 'CNK1',

    @ChildValue integer = 999,

    @ParentID integer = 1;

INSERT dbo.Child 

(

    ChildID, 

    ChildNaturalKey,

    ChildValue, 

    ParentID

) 

VALUES 

(

    @ChildID, 

    @ChildNaturalKey,

    @ChildValue, 

    @ParentID    

);

Этот оператор Insert будет блокироваться вне зависимости от того, используете вы блокирующую или версионную изоляцию транзакций read committed в этом тесте.

Объяснение

План выполнения для этой вставки дочерней записи имеет вид:

После вставки новой строки в дочернюю таблицу план выполнения проверяет ограничение внешнего ключа. Проверка пропускается, если вставляемый родительский id есть null (достигается посредством предиката ‘pass through’ в левом полусоединении). В представленном случае добавляемый родительский id не является null, поэтому проверка внешнего ключа выполняется.

SQL Server проверяет ограничение внешнего ключа поиском соответствующей строки в родительской таблице. Чтобы сделать это, движок не может использовать версионность строки — требуется убедиться, что проверяемые данные являются последними зафиксированными данными, а не некоторой старой версией. Движок гарантирует это, добавляя внутренний табличный хинт READCOMMITTEDLOCK к проверке внешнего ключа на родительской таблице.

Это приводит к тому, что SQL Server пытается запросить разделяемую блокировку на соответствующую строку в родительской таблице, что блокируется, поскольку другая сессия удерживает несовместную эксклюзивную блокировку, т.к. обновление пока еще не зафиксировано.

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

Избежать блокировки

Зафиксируйте или откатите открытую транзакцию во втором подключении, затем восстановите тестовую среду:

DROP TABLE IF EXISTS

    dbo.Child, dbo.Parent;

Снова создайте тестовые таблицы, но теперь вместо принятия значений по умолчанию мы сделаем первичный ключ некластеризованным, а ограничение уникальности кластеризованным:

CREATE TABLE dbo.Parent

(

    ParentID integer NOT NULL,

    ParentNaturalKey varchar(10) NOT NULL,

    ParentValue integer NOT NULL,

    CONSTRAINT [PK dbo.Parent ParentID]

        PRIMARY KEY NONCLUSTERED (ParentID),

    CONSTRAINT [AK dbo.Parent ParentNaturalKey]

        UNIQUE CLUSTERED (ParentNaturalKey)

);

 
CREATE TABLE dbo.Child 

(

    ChildID integer NOT NULL,

    ChildNaturalKey varchar(10) NOT NULL,

    ChildValue integer NOT NULL,

    ParentID integer NULL,

    CONSTRAINT [PK dbo.Child ChildID]

        PRIMARY KEY NONCLUSTERED (ChildID),

     CONSTRAINT [AK dbo.Child ChildNaturalKey]

        UNIQUE CLUSTERED (ChildNaturalKey),

     CONSTRAINT [FK dbo.Child to dbo.Parent]

        FOREIGN KEY (ParentID)

            REFERENCES dbo.Parent (ParentID)

);

Как и раньше добавим строку в родительскую таблицу:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

 DECLARE

    @ParentID integer = 1,

    @ParentNaturalKey varchar(10) = 'PNK1',

    @ParentValue integer = 100;

 INSERT dbo.Parent 

(

    ParentID, 

    ParentNaturalKey, 

    ParentValue

) 

VALUES 

(

    @ParentID, 

    @ParentNaturalKey, 

    @ParentValue

);

Во второй сессии опять выполните обновление без фиксации. Я использую теперь естественный ключ просто для разнообразия — это не оказывает влияния на результат. Используйте суррогатный ключ, если вам нравится.

DECLARE

    @ParentID integer = 1,

    @ParentNaturalKey varchar(10) = 'PNK1',

    @ParentValue integer = 200;

BEGIN TRANSACTION 

    UPDATE dbo.Parent 

    SET ParentValue = @ParentValue 

    WHERE ParentNaturalKey = @ParentNaturalKey;

Теперь снова выполните вставку в дочернюю таблицу в первой сессии:

DECLARE

    @ChildID integer = 101,

    @ChildNaturalKey varchar(10) = 'CNK1',

    @ChildValue integer = 999,

    @ParentID integer = 1;

 INSERT dbo.Child 

(

    ChildID, 

    ChildNaturalKey,

    ChildValue, 

    ParentID

) 

VALUES 

(

    @ChildID, 

    @ChildNaturalKey,

    @ChildValue, 

    @ParentID    

);

Теперь вставка дочерней записи не блокируется. Это справедливо при запуске и блокирующей, и версионной изоляции транзакций read committed. Это не опечатка или ошибка: RCSI ничем не отличается.

Объяснение

План выполнения для вставки дочерней записи теперь немного отличается:

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

Но почему мы не получаем теперь блокировки?

Еще не зафиксированное обновление родительской таблицы во второй сессии накладывает эксклюзивную блокировку на строку в кластеризованном индексе, поскольку обновляется базовая таблица. Изменения в столбце ParentValue не влияют на некластеризованный первичный ключ на ParentID, поэтому эта строка некластеризованного индекса не блокируется.

Проверка внешнего ключа может, следовательно, запросить разделемую блокировку на индекс некластеризованного первичного ключа безпрепятственно, и вставка в дочернюю таблицу происходит сразу.

Когда первичный ключ был кластеризованным, проверка внешнего ключа требовала разделяемую блокировку на тот же ресурс (строка кластеризованного индекса), который был эксклюзивно блокирован оператором обновления.

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

2. Избежать конфликтов обновления

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

Хотя имеется одно важное потенциальное отличие при использовании SI. При изоляции read committed (блокирующей или RCSI) вставка дочерней строки в конечном итоге происходит после фиксации или отката обновления во второй сессии. При использовании SI существует риск прерывания транзакции из-за явного конфликта обновления.

Тут немного более хитрая демонстрация, поскольку транзакция снимка не начинается с оператора BEGIN TRANSACTION — она начинается с первого доступа пользователя к данным после этой точки.

Первый скрипт устанавливает демонстрацию SI с еще одной фиктивной таблицей, используемой только для того, чтобы убедиться, что транзакция снимка действительно началась. Он использует изменение теста, при котором ссылочный первичный ключ определен уникальным кластеризованным индексом (по умолчанию):

ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;

GO

DROP TABLE IF EXISTS

    dbo.Dummy, dbo.Child, dbo.Parent;

GO

CREATE TABLE dbo.Dummy

(

    x integer NULL

);

CREATE TABLE dbo.Parent

(

    ParentID integer NOT NULL,

    ParentNaturalKey varchar(10) NOT NULL,

    ParentValue integer NOT NULL,

    CONSTRAINT [PK dbo.Parent ParentID]

        PRIMARY KEY (ParentID),

    CONSTRAINT [AK dbo.Parent ParentNaturalKey]

        UNIQUE (ParentNaturalKey)

);

CREATE TABLE dbo.Child 

(

    ChildID integer NOT NULL,

    ChildNaturalKey varchar(10) NOT NULL,

    ChildValue integer NOT NULL,

    ParentID integer NULL,

    CONSTRAINT [PK dbo.Child ChildID]

        PRIMARY KEY (ChildID),

    CONSTRAINT [AK dbo.Child ChildNaturalKey]

        UNIQUE (ChildNaturalKey),

    CONSTRAINT [FK dbo.Child to dbo.Parent]

        FOREIGN KEY (ParentID)

            REFERENCES dbo.Parent (ParentID)

);

Вставка родительской строки:

DECLARE

    @ParentID integer = 1,

    @ParentNaturalKey varchar(10) = 'PNK1',

    @ParentValue integer = 100;

INSERT dbo.Parent 

(

    ParentID, 

    ParentNaturalKey, 

    ParentValue

) 

VALUES 

(

    @ParentID, 

    @ParentNaturalKey, 

    @ParentValue

);

Все еще в первой сессии начинаем транзакцию снимка:

-- Сессия 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

-- Проверка, что транзакция снимка началась

SELECT COUNT_BIG(*) FROM dbo.Dummy AS D;

Во второй сессии (выполняется при любом уровне изоляции):

-- Сессия 2

DECLARE

    @ParentID integer = 1,

    @ParentNaturalKey varchar(10) = 'PNK1',

    @ParentValue integer = 200;

BEGIN TRANSACTION;

    UPDATE dbo.Parent 

    SET ParentValue = @ParentValue 

    WHERE ParentID = @ParentID;

Попытка вставить дочернюю строку в первой сессии блокируется, как и ожидалось:

-- Сессия 1

DECLARE

    @ChildID integer = 101,

    @ChildNaturalKey varchar(10) = 'CNK1',

    @ChildValue integer = 999,

    @ParentID integer = 1;

INSERT dbo.Child 

(

    ChildID, 

    ChildNaturalKey,

    ChildValue, 

    ParentID

) 

VALUES 

(

    @ChildID, 

    @ChildNaturalKey,

    @ChildValue, 

    @ParentID    

);

Различие имеет место, когда мы завершаем транзакцию во второй сессии. Если мы откатим её, вставка дочерней строки в первой сессии завершается успешно.

Если же мы зафиксируем открытую транзакцию:

-- Сессия 2

COMMIT TRANSACTION;

Первая сессия сообщит о конфликте обновления и откатит транзакцию:

Объяснение

Конфликт обновления происходит, несмотря на тот факт, что проверяемый внешний ключ не менялся при обновлении во второй сессии.

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

Когда ключ поддерживается некластеризованным индексом, транзакция снимка видит только немодифицированную строку индекса, поэтому здесь нет блокирования и не обнаруживается никакого конфликта обновления.

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

Выводы

Имеется много соображений относительно выбора кластеризованного индекса для таблицы с построчным хранением. Описанная здесь проблема — просто еще один фактор, который следует принимать в расчет.

Это особенно справедливо, если вы будете использовать изоляцию снимка. Никому не понравится прерывание транзакции, особенно когда это не кажется логичным. Если вы будете использовать RCSI, блокирование при чтении, когда проверяется ограничение внешнего ключа, может оказаться неожиданным и привести к тупику.

По умолчанию для ограничения PRIMARY KEY выполняется создание поддерживающего его кластеризованного индекса, если явно не определен другой индекс или ограничение вместо кластеризованного. При проектировании является хорошей привычкой явно указывать ваши намерения, поэтому я бы настоятельно советовал писать всякий раз CLUSTERED или NONCLUSTERED.

Дублированные индексы?

Иногда по веским причинам вы серьезно рассматриваете вариант, когда кластеризованный индекс и некластеризованный индекс имеют одни и те же ключи.

Намерением может быть обеспечить оптимальный доступ на чтение для пользовательских запросов посредством кластеризованного индекса (избегая поиска ключа), обеспечивая при этом также минимально блокирующую (и конфликтующую на обновлениях) проверку внешних ключей посредством компактного некластеризованного индекса, как показано здесь.

Это достижимо, однако имеется пара подводных камней, на которые следует обратить внимание:

• При наличии более одного подходящего индекса SQL Server не дает способа, гарантирующего использование того или иного индекса для проверки ограничения внешнего ключа.

  Dan Guzman задокументировал свои наблюдения в Secrets of Foreign Key Index Binding, но они могут быть несовершенны и в любом случае недокументированы, а значит могут измениться.

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

• Если вы используете сокращенный синтаксис для внешнего ключа SQL Server будет только привязывать ограничение к первичному ключу, вне зависимости от того, является он некластеризованным или кластеризованным.

Следующий фрагмент кода демонстрирует последнее различие:

CREATE TABLE dbo.Parent

(

    ParentID integer NOT NULL UNIQUE CLUSTERED

);

-- Сокращенный (неявный) синтаксис

-- падает с ошибкой 1773

CREATE TABLE dbo.Child

(

    ChildID integer NOT NULL PRIMARY KEY NONCLUSTERED,

    ParentID integer NOT NULL 

        REFERENCES dbo.Parent

);

-- Явный синтаксис выполняется успешно

CREATE TABLE dbo.Child

(

    ChildID integer NOT NULL PRIMARY KEY NONCLUSTERED,

    ParentID integer NOT NULL 

        REFERENCES dbo.Parent (ParentID)

);

Люди привыкли в значительной степени игнорировать конфликты чтения-записи в RCSI и SI. Надеюсь, что эта статья дала вам дополнительные мысли о применении физического дизайна к таблицам, связанных внешним ключом.