ADD CONSTRAINT в MySQL (Ограничения внешних ключей)

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

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

[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
				      REFERENCES table_name (index_col_name, ...)
				      [ON DELETE CASCADE | ON DELETE SET NULL]

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

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

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

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

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

Начиная с версии 3.23.50 синтаксический анализатор InnoDB обеспечиваетвозможность использовать обратные кавычки (`), ограничивающие имена таблици столбцов в указанном выше значении, однако синтаксический анализатор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;

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

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

ALTER TABLE yourtablename
  ADD CONSTRAINT 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, чтонарушит ограничения внешнего ключа, относящиеся к таблице. Оператор CREATEINDEX в 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'

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