7.5. Таблицы InnoDB
7.5.4. Создание таблиц InnoDB

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

Начиная с версии 3.23.43b, в InnoDB включены ограничения внешних ключей. InnoDB - первый формат таблиц MySQL, который обеспечивает возможность задавать ограничения внешнего ключа, чтобы обеспечить целостность данных.

Синтаксис задания ограничения внешнего ключа в InnoDB следующий:

[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
REFERENCES table_name (index_col_name, ...)
[ON DELETE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]

Обе таблицы должны быть InnoDB-типа; обязательно также наличие индекса, в котором внешний ключ и ссылочный ключ должны находиться в ПЕРВЫХ столбцах. Для таблиц InnoDB индексы по внешним ключам или ссылочным ключам не создаются автоматически: их создание требуется задавать явно.

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

Если вы указали действие SET NULL, убедитесь что вы не объявили столбец в дочерней таблице как NOT NULL.

Если оператор MySQL CREATE TABLE выдает ошибку с номером 1005, и в строке сообщения об ошибке присутствует ссылка на ошибку с номером 150, то произошел сбой создания таблицы из-за того, что ограничения внешнего ключа не были сформированы надлежащим образом. Аналогично и для оператора ALTER TABLE: если происходит ошибка при выполнении оператора и в сообщении присутствует ссылка на ошибку с номером 150, то определение внешнего ключа для преобразовываемой таблицы сформировано неправильно.

Начиная с версии 3.23.50 с ограничением внешнего ключа можно также связывать выражения ON DELETE CASCADE или ON DELETE SET NULL. Начиная с версии 4.0.8 вы можете это же использовать с ON UPDATE.

Если указано выражение ON DELETE CASCADE и строка в родительской таблице удалена, то в формате InnoDB все эти строки автоматически удаляются также и из дочерней таблицы, значения внешнего ключа которой равны значениям ссылочного ключа в строке родительской таблицы. Если указано выражение ON DELETE SET NULL, строки дочерней таблицы автоматически обновляются, поэтому столбцам во внешнем ключе также присваивается значение SQL NULL.

Начиная с версии 3.23.50 в InnoDB не осуществляется проверка ограничений внешних ключей на наличие значений внешних или родительских ключей, которые содержат столбец NULL.

Начиная с версии 3.23.50 синтаксический анализатор InnoDB обеспечивает возможность использовать обратные кавычки (`), ограничивающие имена таблиц и столбцов в FOREIGN KEY ... REFERENCES ..., однако синтаксический анализатор InnoDB еще "не знает" об опции lower_case_table_names, которая может быть задана в файле my.cnf.

Пример:

CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE SET NULL
) TYPE=INNODB;

Начиная с версии 3.23.50, для таблиц InnoDB обеспечивается возможность добавлять новые ограничения внешних ключей для таблиц при помощи

ALTER TABLE yourtablename
ADD [CONSTRAINT symbol] FOREIGN KEY (...) REFERENCES anothertablename(...)

Однако не следует забывать предварительно создавать необходимые индексы. В InnoDB версий < 3.23.50 команды ALTER TABLE или CREATE INDEX не должны использоваться совместно с таблицами, для которых установлены ограничения внешнего ключа или на которые есть ссылки в ограничениях внешних ключей: Команда ALTER TABLE удаляет все ограничения внешних ключей, определенные в таблице. Не следует использовать команду ALTER TABLE для таблиц, на которые есть ссылки; вместо этого необходимо применять команды DROP TABLE и CREATE TABLE, чтобы изменить логическую структуру. При выполнении команды ALTER TABLE MySQL может использовать команду RENAME TABLE, что нарушит ограничения внешнего ключа, относящиеся к таблице. Оператор CREATE INDEX в MySQL обрабатывается таким же образом, как и ALTER TABLE, поэтому приведенные выше ограничения распространяются и на этот оператор.

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

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

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

Начиная с версии 3.23.50 InnoDB возвращает определения внешних ключей таблицы, если вызвать

SHOW CREATE TABLE yourtablename

Помимо этого, mysqldump выводит корректные определения таблиц в файл дампа, "не забывая" о внешних ключах.

Список ограничений внешнего ключа таблицы T можно также вывести при помощи команды

SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'

Ограничения внешнего ключа выводятся в комментариях к таблице.