Mysql error 150

Дата: 2.12.2016

Дата: 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 автоматически его создаст при добавлении внешнего ключа (в совсем уж древних версиях требовалось предварительное создание индекса).

Примеры

create table t1 (a int, b int, index(a)) engine=innodb;

create table t2 (a int, foreign key (a) references t1(a), foreign key (a) references t1(b)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

SHOW ENGINE INNODB STATUS;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-16 06:37:39 0x14c1c Error in foreign key constraint of table test/t2:
foreign key (a) references t1(b)) engine=innodb:
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-constr
aints.html for correct foreign key definition.
————

— при использовании оператора ALTER ошибка и секция
— LATEST FOREIGN KEY ERROR будут содержать внутреннее имя таблицы test.#sql-a64_1

create table t2 (a int) engine=innodb;
alter table t2 add foreign key (a) references t1(a), add foreign key (a) references t1(b);
ERROR 1005 (HY000): Cannot create table ‘test.#sql-a64_1’ (errno: 150)

— в новых версиях парсер MySQL определяет некорректность
— конструкции и возвращает другую ошибку (без errno 150)

alter table t2 add foreign key (a) references t1(a), add foreign key (a) references t1(b);
ERROR 1215 (HY000): Cannot add foreign key constraint

— аналогично и для оператора CREATE

drop table t2;
create table t2 (a int, foreign key (a) references t1(a), foreign key (a) references t1(b)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

Обратите внимание, если внешний ключ уже существует и в результате изменений (alter table) возникает ситуация отсутствия индекса в родительской таблице, то код ошибки будет 1025:

create table t1 (a int, b int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;

alter table t1 drop a;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_5′ to ‘.testt1′ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161220  7:14:25 Error in foreign key constraint of table test/t2:
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 «t2_ibfk_1» FOREIGN KEY («a») REFERENCES «t1» («a»)
The index in the foreign key in table is «a»
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
InnoDB: Renaming table `test`.`#sql-d6c_5` to `test`.`t1` failed!
———

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

Примеры

mysql> create table t1 (a int, index(a)) engine=myisam;

mysql> create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

— в старых версиях будет ошибка вида
ERROR 1005 (HY000): Cannott create table ‘test.t2’ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-17 16:30:09 0x364c Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb
————

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

Примеры

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

create table t1(id int not null primary key, b int, key(b)) engine=innodb;
Query OK, 0 rows affected (0.22 sec)

alter table t1 add foreign key(id,b) references t1(id);
ERROR 1005 (HY000): Can‘t create table ‘test.#sql-d88_b’ (errno: 150)

show warnings;
+——-+——+—————————————————+
| Level | Code | Message                                           |
+——-+——+—————————————————+
| Error | 1005 | Can‘t create table ‘test.#sql-d88_b’ (errno: 150) |
+——-+——+—————————————————+

— понять, что причина в синтаксической ошибке
— можно только из:

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
160605 22:28:23 Error in foreign key constraint of table test/#sql-d88_b:
foreign key(id,b) references t1(id):
Syntax error close to:

— в новых версиях парсер распознает синтаксическую ошибку
— и сообщает об этом:
ERROR 1239 (42000): Incorrect foreign key definition for ‘foreign key without name’: Key reference and table reference don‘t match

Другой пример: попробуем создать внешний ключ на поле типа text:

create table t1 (a text , index(a(50))) engine=innodb;

create table t2 (a text, foreign key (a) references t1(a)) engine=innodb;
ERROR 1170 (42000): BLOB/TEXT column ‘a’ used in key specification without a key length

— MySQL автоматически пытается создать индекс на колонке `a`, и
— сообщает, что нельзя создать индекс по всей длине поля типа text.
— Хорошо, укажем префикс и получим errno 150:

create table t2 (a text, foreign key (a(50)) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

— понять, что произошла ошибка синтаксиса можно:
— или через show engine innodb status;
— или внимательно сравнить разрешенный синтаксис в документации
— с написанной командой.

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

Примеры

— например, если у одной колонки мы определим
— атрибут unsigned, а у другой нет, то:
create table t1 (a int unsigned, index(a)) engine=innodb;

create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

— в старых версиях будет ошибка вида
ERROR 1005 (HY000): Cannott create table ‘test.t2’ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-26 03:00:47 0x10894 Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a)) engine=innodb:
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-constr
aints.html for correct foreign key definition.
————

Если несоответствие типов данных возникает во время изменения таблицы при уже существующем внешнем ключе, то ошибка будет иметь вид:

create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;

MariaDB [test]> alter table t1 modify a int unsigned;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_6′ to ‘.testt1′ (errno: 150)

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

Примеры

create table t1 (a int not null, index(a)) engine=innodb;

create table t2 (a int not null, foreign key (a) references t1(a) on delete set null) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

— в старых версиях будет:
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-26 06:24:42 0x10894 Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a) on delete set null) engine=innodb:
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.
————

Если коллизия возникает при уже существующем внешнем ключе, то:

create table t1 (a int not null, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a) on delete set null) engine=innodb;

alter table t2 modify a int not null;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_6′ to ‘.testt2′ (errno: 150)

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

Примеры

create table t1 (a int, index(a)) engine=innodb;

create temporary table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161130  4:22:26 Error in foreign key constraint of table temp/#sql318_4_1:
foreign key (a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb
———

— в новых версиях ошибка будет иметь вид:
ERROR 1215 (HY000): Cannot add foreign key constraint

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

Примеры

create table t1 (a int, index(a)) partition by range (a)  
(partition p0 values less than (10),
partition p1 values less than (20),
partition p2 values less than maxvalue);

create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161223 19:38:14 Error in foreign key constraint of table test/t2:
foreign key (a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb
———
— сообщение указывает на то, что родительская таблица
— не найдена в словаре данных innodb (bug: 84331)

— в новых версиях ошибка будет иметь вид:

create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

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

create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;

alter table t1 PARTITION BY HASH(a) PARTITIONS 8;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

show engine innodb status;
— не содержит секцию LATEST FOREIGN KEY ERROR

Errno 121

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

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

Примеры

create table t1 (a int, index(a)) engine=innodb;

create table t2 (a int, CONSTRAINT q1 foreign key (a) references t1(a)) engine=innodb;

create table t3 (a int, CONSTRAINT q1 foreign key (a) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t3’ (errno: 121)

— в 5.7 будет другая ошибка
ERROR 1022 (23000): Cannot write; duplicate key in table ‘t3’

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161130  3:31:11 Error in foreign key constraint creation for table `test`.`t3`.
A foreign key constraint of name `test`.`q1`
already exists. (Note that internally InnoDB adds ‘databasename’
in front of the user-defined constraint name.)
Note that InnoDB FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
———

Нет ошибок

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

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

Примеры

create table t1 (a int, index(a)) engine=innodb;

create table t2 (a int, foreign key (a) references t1(a)) engine=myisam;
Query OK, 0 rows affected (0.33 sec)

MariaDB [test]> show create table t2G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

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

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, …)
    REFERENCES tbl_name (index_col_name,…)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

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

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

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

create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;

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

Удаление следует понимать в расширенном варианте как удаление из множества 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, то такие операции над родительской таблицей могут привести к несогласованности данных, т.е. появлению в дочерней таблице записей не имеющих соответствия в родительской таблице.

Примеры

create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;

insert into t1 values(1);
insert into t2 values(1);

update t1 set a=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1`(`a`))

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

Примеры

create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;

insert into t2 values(15);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))

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

Примеры

create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, index(a)) engine=innodb;

insert into t2 values(2);

alter table t2 add foreign key (a) references t1(a);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-3f0_4`, CONSTRAINT `#sql-3f0_4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))

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

Примеры

create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, index(a)) engine=innodb;

insert into t1 values (1),(1);
insert into t2 values(1);

delete from t1 where a=1 limit 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1`(`a`))

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

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

А1 А2 А3 А4 А5 А6 А7 Б1 В1 В2 Г1 Г2 Г3 Г4 Г5
MySQL error 1005 + + + + + + + +
MySQL error 1022 +
MySQL error 1025 + + +
MySQL error 1215 + + + + +
MySQL error 1217 + +
MySQL error 1239 +
MySQL error 1451 + +
MySQL error 1452 + +
нет ошибок + +

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

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

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

Источник

Содержание

  1. Ошибки внешних ключей: errno 150, MySQL error 1005 и другие
  2. Как получить больше данных об ошибке
  3. Errno 150
  4. Errno 121
  5. Нет ошибок
  6. Несоответствие данных
  7. Сводная таблица

Ошибки внешних ключей: errno 150, MySQL error 1005 и другие

Автор: Василий Лукьянчиков , 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 автоматически его создаст при добавлении внешнего ключа (в совсем уж древних версиях требовалось предварительное создание индекса).

create table t1 ( a int , b int , index ( a ) ) engine = innodb ;

create table t2 ( a int , foreign key ( a ) references t1 ( a ) , foreign key ( a ) references t1 ( b ) ) engine = innodb ;
ERROR 1005 ( HY000 ) : Cannot create table ‘test.t2’ ( errno: 150 )

SHOW ENGINE INNODB STATUS;
————————
LATEST FOREIGN KEY ERROR
————————
2016 -11 -16 06 : 37 : 39 0x14c1c Error in foreign key constraint of table test/t2:
foreign key ( a ) references t1 ( b ) ) engine = innodb :
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-constr
aints.html for correct foreign key definition.
————

— при использовании оператора ALTER ошибка и секция
— LATEST FOREIGN KEY ERROR будут содержать внутреннее имя таблицы test.#sql-a64_1

create table t2 ( a int ) engine = innodb ;
alter table t2 add foreign key ( a ) references t1 ( a ) , add foreign key ( a ) references t1 ( b ) ;
ERROR 1005 ( HY000 ) : Cannot create table ‘test.#sql-a64_1’ ( errno: 150 )

— в новых версиях парсер MySQL определяет некорректность
— конструкции и возвращает другую ошибку (без errno 150)

alter table t2 add foreign key ( a ) references t1 ( a ) , add foreign key ( a ) references t1 ( b ) ;
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

— аналогично и для оператора CREATE

drop table t2;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) , foreign key ( a ) references t1 ( b ) ) engine = innodb ;
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

Обратите внимание, если внешний ключ уже существует и в результате изменений (alter table) возникает ситуация отсутствия индекса в родительской таблице, то код ошибки будет 1025:

create table t1 ( a int , b int , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;

alter table t1 drop a;
ERROR 1025 ( HY000 ) : Error on rename of ‘. t est # sql-d6c_5’ to ‘. t est t 1’ ( errno: 150 )

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161220 7 : 14 : 25 Error in foreign key constraint of table test/t2:
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 «t2_ibfk_1» FOREIGN KEY ( «a» ) REFERENCES «t1» ( «a» )
The index in the foreign key in table is «a»
See http://dev.mysql.com/doc/refman/ 5.5 /en/innodb-foreign-key-constraints.html
for correct foreign key definition.
InnoDB : Renaming table `test`.` #sql-d6c_5` to `test`.`t1` failed!
———

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

mysql> create table t1 ( a int , index ( a ) ) engine = myisam ;

mysql> create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

— в старых версиях будет ошибка вида
ERROR 1005 ( HY000 ) : Cannott create table ‘test.t2’ ( errno: 150 )

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016 -11 -17 16 : 30 : 09 0x364c Error in foreign key constraint of table world/t2:
foreign key ( a ) references t1 ( a ) ) engine = innodb :
Cannot resolve table name close to :
( a ) ) engine = innodb
————

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

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

create table t1 ( id int not null primary key , b int , key ( b ) ) engine = innodb ;
Query OK, 0 rows affected ( 0.22 sec )

alter table t1 add foreign key ( id,b ) references t1 ( id ) ;
ERROR 1005 ( HY000 ) : Can ‘t create table ‘ test. #sql-d88_b’ (errno: 150)

— понять, что причина в синтаксической ошибке
— можно только из:

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
160605 22 : 28 : 23 Error in foreign key constraint of table test/ #sql-d88_b:
foreign key ( id,b ) references t1 ( id ) :
Syntax error close to :

— в новых версиях парсер распознает синтаксическую ошибку
— и сообщает об этом:
ERROR 1239 ( 42000 ) : Incorrect foreign key definition for ‘foreign key without name’ : Key reference and table reference don ‘t match

Другой пример: попробуем создать внешний ключ на поле типа text:

create table t1 ( a text , index ( a ( 50 ) ) ) engine = innodb ;

create table t2 ( a text , foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1170 ( 42000 ) : BLOB / TEXT column ‘a’ used in key specification without a key length

— MySQL автоматически пытается создать индекс на колонке `a`, и
— сообщает, что нельзя создать индекс по всей длине поля типа text.
— Хорошо, укажем префикс и получим errno 150:

create table t2 ( a text , foreign key ( a ( 50 ) ) references t1 ( a ) ) engine = innodb ;
ERROR 1005 ( HY000 ) : Cannot create table ‘test.t2’ ( errno: 150 )

— понять, что произошла ошибка синтаксиса можно:
— или через show engine innodb status;
— или внимательно сравнить разрешенный синтаксис в документации
— с написанной командой.

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

— например, если у одной колонки мы определим
— атрибут unsigned, а у другой нет, то:
create table t1 ( a int unsigned , index ( a ) ) engine = innodb ;

create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

— в старых версиях будет ошибка вида
ERROR 1005 ( HY000 ) : Cannott create table ‘test.t2’ ( errno: 150 )

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016 -11 -26 03 : 00 : 47 0x10894 Error in foreign key constraint of table world/t2:
foreign key ( a ) references t1 ( a ) ) engine = innodb :
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-constr
aints.html for correct foreign key definition.
————

Если несоответствие типов данных возникает во время изменения таблицы при уже существующем внешнем ключе, то ошибка будет иметь вид:

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;

MariaDB [ test ] > alter table t1 modify a int unsigned ;
ERROR 1025 ( HY000 ) : Error on rename of ‘. t est # sql-d6c_6’ to ‘. t est t 1’ ( errno: 150 )

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

create table t1 ( a int not null , index ( a ) ) engine = innodb ;

create table t2 ( a int not null , foreign key ( a ) references t1 ( a ) on delete set null ) engine = innodb ;
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

— в старых версиях будет:
ERROR 1005 ( HY000 ) : Cannot create table ‘test.t2’ ( errno: 150 )

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016 -11 -26 06 : 24 : 42 0x10894 Error in foreign key constraint of table world/t2:
foreign key ( a ) references t1 ( a ) on delete set null ) engine = innodb :
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL .
————

Если коллизия возникает при уже существующем внешнем ключе, то:

create table t1 ( a int not null , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) on delete set null ) engine = innodb ;

alter table t2 modify a int not null ;
ERROR 1025 ( HY000 ) : Error on rename of ‘. t est # sql-d6c_6’ to ‘. t est t 2’ ( errno: 150 )

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

create table t1 ( a int , index ( a ) ) engine = innodb ;

create temporary table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1005 ( HY000 ) : Cannot create table ‘test.t2’ ( errno: 150 )

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161130 4 : 22 : 26 Error in foreign key constraint of table temp/ #sql318_4_1:
foreign key ( a ) references t1 ( a ) ) engine = innodb :
Cannot resolve table name close to :
( a ) ) engine = innodb
———

— в новых версиях ошибка будет иметь вид:
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

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

create table t1 ( a int , index ( a ) ) partition by range ( a )
( partition p0 values less than ( 10 ) ,
partition p1 values less than ( 20 ) ,
partition p2 values less than maxvalue ) ;

create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1005 ( HY000 ) : Cannot create table ‘test.t2’ ( errno: 150 )

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161223 19 : 38 : 14 Error in foreign key constraint of table test/t2:
foreign key ( a ) references t1 ( a ) ) engine = innodb :
Cannot resolve table name close to :
( a ) ) engine = innodb
———
— сообщение указывает на то, что родительская таблица
— не найдена в словаре данных innodb (bug: 84331)

— в новых версиях ошибка будет иметь вид:

create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1215 ( HY000 ) : Cannot add foreign key constraint

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

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;

alter table t1 PARTITION BY HASH ( a ) PARTITIONS 8 ;
ERROR 1217 ( 23000 ) : Cannot delete or update a parent row: a foreign key constraint fails

show engine innodb status;
— не содержит секцию LATEST FOREIGN KEY ERROR

Errno 121

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

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

create table t1 ( a int , index ( a ) ) engine = innodb ;

create table t2 ( a int , CONSTRAINT q1 foreign key ( a ) references t1 ( a ) ) engine = innodb ;

create table t3 ( a int , CONSTRAINT q1 foreign key ( a ) references t1 ( a ) ) engine = innodb ;
ERROR 1005 ( HY000 ) : Cannot create table ‘test.t3’ ( errno: 121 )

— в 5.7 будет другая ошибка
ERROR 1022 ( 23000 ) : Cannot write ; duplicate key in table ‘t3’

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161130 3 : 31 : 11 Error in foreign key constraint creation for table `test`.`t3`.
A foreign key constraint of name `test`.`q1`
already exists . ( Note that internally InnoDB adds ‘databasename’
in front of the user-defined constraint name. )
Note that InnoDB FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case , then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
———

Нет ошибок

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

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

create table t1 ( a int , index ( a ) ) engine = innodb ;

create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = myisam ;
Query OK, 0 rows affected ( 0.33 sec )

MariaDB [ test ] > show create table t2G
*************************** 1 . row ***************************
Table : t2
Create Table : CREATE TABLE `t2` (
`a` int ( 11 ) DEFAULT NULL ,
KEY `a` ( `a` )
) ENGINE = MyISAM DEFAULT CHARSET =latin1
1 row in set ( 0.00 sec )

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

[ CONSTRAINT [ symbol ] ] FOREIGN KEY
[ index_name ] ( index_col_name, . )
REFERENCES tbl_name ( index_col_name. )
[ ON DELETE reference_option ]
[ ON UPDATE reference_option ]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

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

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

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

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;

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

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

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

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

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;

insert into t1 values ( 1 ) ;
insert into t2 values ( 1 ) ;

update t1 set a= 2 ;
ERROR 1451 ( 23000 ) : Cannot delete or update a parent row: a foreign key constraint fails ( `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY ( `a` ) REFERENCES `t1` ( `a` ) )

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

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , foreign key ( a ) references t1 ( a ) ) engine = innodb ;

insert into t2 values ( 15 ) ;
ERROR 1452 ( 23000 ) : Cannot add or update a child row: a foreign key constraint fails ( `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY ( `a` ) REFERENCES `t1` ( `a` ) )

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

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , index ( a ) ) engine = innodb ;

insert into t2 values ( 2 ) ;

alter table t2 add foreign key ( a ) references t1 ( a ) ;
ERROR 1452 ( 23000 ) : Cannot add or update a child row: a foreign key constraint fails ( `test`.` #sql-3f0_4`, CONSTRAINT `#sql-3f0_4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))

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

create table t1 ( a int , index ( a ) ) engine = innodb ;
create table t2 ( a int , index ( a ) ) engine = innodb ;

insert into t1 values ( 1 ) , ( 1 ) ;
insert into t2 values ( 1 ) ;

delete from t1 where a= 1 limit 1 ;
ERROR 1451 ( 23000 ) : Cannot delete or update a parent row: a foreign key constraint fails ( `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY ( `a` ) REFERENCES `t1` ( `a` ) )

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

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

Источник

Источник

MySQL Foreign Key Errors: errno 150, errno 121, and others

Diagnosing Errors

  1. SHOW ENGINE INNODB STATUS is Your New Best Friend: Click for solution

    If you get one of the really helpful errors (sarcasm) like the errno 150 or errno 121, then by simply typing in SHOW ENGINE INNODB STATUS, there is a section called «LATEST FOREIGN KEY ERROR». Under that it will give you a very helpful error message, which typically will tell you right away what is the matter. What’s the catch?
    You need SUPER privileges to run it, so if you don’t have that, you’ll just have to test out the following scenarios.

  2. Use Eliacom’s MySQL GUI tool to catch most errors: Click for solution

MySQL errno 150

ERROR 1005 (HY000): Can’t create table ‘table’ (errno: 150)

ERROR 1025 (HY000): Error on rename of ‘table’ to ‘newtable’ (errno: 150)

Causes and Solutions for errno 150

  1. Data Types Don’t Match: Click for solution

    The types of the columns have to be the same (usually). This is one of the most common reasons for errno 150. For instance, if the type of the child column is VARCHAR(50), the type of the parent column should be exactly VARCHAR(50) (since they’re supposed to hold the same data). For numeric types, if one is UNSIGNED, then both have to be UNSIGNED. They should match exactly!. I have run into circumstances where it has let me create a foreign key where the child column was a VARCHAR(50) and the parent column was a VARCHAR(200). Interestingly, if I tried to do the opposite for the same tables, reference a child column that was a VARCHAR(200) to a parent column that was a VARCHAR(50), it threw the errno 150 error. This all might depend on the version of MySQL you are using, and really, the data types should match exactly since the same data is being stored in both places.

    How do you fix it? You need to check the data types for the columns. You can check them by using SHOW COLUMNS, or SHOW CREATE TABLE. If you are using Eliacom’s MySQL GUI tool, then the system should alert you if their data types are different before it attempts to create the foreign key, so you shouldn’t have to worry about this. If you don’t know how to add foreign keys using Eliacom’s MySQL GUI tool, see the video tutorial on adding foreign keys and indexes.

  2. Parent Columns Not Indexed (Or Indexed in Wrong Order): Click for solution

    MySQL requires that both the child columns and parent columns have indexes on them so that the operations to ensure the constraint is in effect can be done quickly. If there isn’t a key (index) on the child table, it will automatically create it. But if there isn’t one on the parent table, then it will throw a (very unhelpful) error. Important: For multi-column foreign keys, you need a multi-column index. The order of the columns in the index matters! This means you could have an index on the two columns you’re trying to match, but if they’re in a different order than how you put them into the foreign key statement, you’ll get this error.

    How do you fix it? You need to check that you have an appropriate index on the parent table. If you are creating a foreign key on multiple columns, then you need to create an index on those columns in the right order. If you are creating a foreign key on one column, and that column has a multi-column index, then it should work if the column is the first in the index. I have heard that sometimes this doesn’t work, but I’ve never been able to confirm that (let me know if you’ve had this experience). If you are using Eliacom’s MySQL GUI tool, then the system will check if there is an appropriate index on the parent table. If there isn’t, then it will automatically (and silently) create one for you. You can always view the indexes that exists on each table easily in the Table Manager as well. If you don’t know how to add foreign keys (or view indexes) using our MySQL GUI tool, see the video tutorial on adding foreign keys and indexes.

  3. Column Collations Don’t Match:Click for solution

    For character string type columns (CHAR, VARCHAR, etc.), the column collations have to match exactly. This of course means that the CHARACTER SETs have to match exactly as well. If they aren’t, you can expect the errno 150 error.

    How do you fix it? You need to check the collations for the columns to see if this might be the cause of the issue. The easiest way to do this using MySQL queries is using SHOW FULL COLUMNS. That will tell you the collation for each column in a table. If you are using Eliacom’s MySQL GUI tool, then when you create the foreign key, our MySQL GUI tool will precheck the collations. If they are not the same, then it will tell you that they are different and need to be fixed before the foreign key can be implemented. If you don’t know how to add foreign keys (or view indexes) using Eliacom’s MySQL GUI tool, see the video tutorial on adding foreign keys and indexes.

  4. Using SET NULL on a NOT NULL Column: Click for solution

    If you try to execute a statement like:

    ALTER TABLE `child_table` ADD FOREIGN KEY (`child_column`) REFERENCES `parent_table` (`parent_column`) ON DELETE SET NULL

    and if the child_column’s definition has NOT NULL in it, you will get the errno 150 error.

    How do you fix it? This takes some thought. Do you really want to set the child to NULL if the parent is deleted (or updated if you did ON UPDATE SET NULL)? If so, you need to make sure that NULL is allowed for that column in the child table. You can check this using SHOW COLUMNS or SHOW CREATE TABLE. If you didn’t really want that, then change the ON DELETE/UPDATE SET NULL to something like CASCADE or RESTRICT. If you are using Eliacom’s MySQL GUI tool, then when you go to create the foreign key, the system will alert you if you are trying to SET NULL to a column that is NOT NULL, so you can decide if you want to change it. If you don’t how know to add foreign keys using Eliacom’s MySQL GUI tool, see the video tutorial on adding foreign keys and indexes.

  5. Table Collations Don’t Match: Click for solution

    Just like the Column Collations issue above, having different table collations, even though the column collations match, can cause some problems (at least on some versions of MySQL; this may have been fixed in later versions since this in principle the table default shouldn’t matter). Where we have seen this error crop up is if you have two tables with different collations, but the column collations are the same: it did allow us to create the foreign key without any errors (this was done on MySQL 5.1.41). However, if we ever attempted to modify the child column at all (say rename it, or even just run a «MODIFY COLUMN» query that kept its attributes the same, we would get the errno 150 error. In this case, SHOW INNODB STATUS was completely unhelpful. It said something about needing indexes, or that we possibly SET NULL on a NOT NULL column.
    Note: Actually in the case we found, it was different default character sets at the table level, but I’m guessing it happens if only the collations are different as well.

    How do you fix it? You’ll have to change the table collations to match as well as the column collations. It’s possible that in more recent versions of MySQL that this has been fixed. If you are using Eliacom’s MySQL GUI tool, then you can change the table collation by using our MySQL GUI’s table editor. If you don’t know how to edit tables using our MySQL GUI tool, see the video tutorial on editing tables.

  6. Parent Column Doesn’t Actually Exist In Parent Table: Click for solution

    This is the kind of error that you will spend hours looking for, and then kick yourself when you find it. Check your spelling and look for spaces! Take for instance the query below:

    alter table esp_empdata add constraint foreign key (`empClass`) references `esp_empclasses` (` id2`)

    I went through all the other checks in this paper over and over about 50 times. I was about to give up hope, when I discovered the trick of using SHOW INNODB STATUS, and it will actually take all the mystery away of why these foreign key errors are happening. It told me «Cannot resolve column name close to: «. I thought, what does that mean? I looked more closely, realized I had a space before id2, and kicked myself, repeatedly. You will get the errno 150 error.

    How do you fix it? Double check that the column that you are trying to reference actually exists. If it checks out, then triple check for things like spaces at the beginning or end of the column, or anything that might make it miss the column in the parent table.

  7. One of the indexes on one of the columns is incomplete (column is too long) Click for solution

MySQL errno 121

ERROR 1005 (HY000): Can’t create table ‘table’ (errno: 121)

ERROR 1025 (HY000): Error on rename of ‘table’ to ‘newtable’ (errno: 121)

Causes and Solutions for errno 121

  1. Constraint Name Taken: Click for solution

    The constraint name that you picked is already taken. If you’re wondering what the constraint name is, in the example below where you are altering a table adding a foreign key, the constraint name is in blue:

    ALTER TABLE `child_table` ADD CONSTRAINT `some_constraint_name` FOREIGN KEY `key_name` (`child_column`)
    REFERENCES `parent_table` (`parent_column`)

    If the constraint name happens to be taken, you will get an errno 121.

    How do you fix it? If you’re explicitly choosing a constraint name, then choose something different (since what you chose is apparently already taken). Or you can let MySQL automatically set it for you by not choosing one at all (that’s what I recommend). If you really want to set your own, you can check what the other names are by looking in `information_schema`.`table_constraints` in your MySQL server, to see what’s taken. If you’re relatively certain which table has the constraint that has taken your name, then you can use SHOW CREATE TABLE to view them. If you are using Eliacom’s MySQL GUI tool, then when you go to create the foreign key, the system will precheck your name, and alert you if the name you chose is already taken. If you don’t how know to add foreign keys using Eliacom’s MySQL GUI tool, see the video tutorial on adding foreign keys and indexes.

  2. Two Tables With Same Name But Different Case: Click for solution

    One of the most difficult times I ever had tracking down a foreign key error. What was weird was is it worked on one server, but not on another, which I thought were identical installations. The one difference between the two was that one server had case sensitive table naming turned on and the other didn’t. On the server with the case sensitive table naming turned on, I attempted to run the following query:

    alter table esp_empData add constraint foreign key (`empClass`) references `esp_empclasses` (`id2`)

    When I ran the above query, I got the following error:

    ERROR 1025 (HY000): Error on rename of ‘./otb10/#sql-37c_2f534’ to ‘./otb10/esp_empData’ (errno: 121)

    This was not good. It turned out I had another table with the same name but all lowercase(esp_empdata).

    How do you fix it? When this is the case (you have two tables that are the same name, but with case differences), you’re stuck. There’s nothing you can do (or at least as far as I could see) but to change the table name of the upper case table to something different (adding a «2» to the end fixed it). Luckily, it was was a mistake I had these two tables. And honestly, you really shouldn’t have more than one table named the exact same thing other than their case being different. So just don’t do that.

Other Foreign Key Errors You Might Encounter

  1. A Foreign Key Constraint Fails: Data Doesn’t Match: Click for solution

    The most common but easy error to track (because it actually tells you what is wrong, is when you have data in the child table that does not match to the parent table. If you get this error, chances are you don’t have any of the problems below, since it actually got to the point of checking the data. However, if you’re getting this error and you’re SURE that you don’t have any bad data see «Duplicate Foreign Keys» below. Your error probably looks something like this when you go to create the foreign key (it will vary based on your columns):

    ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`database`.`#sql-37c_2f534`, CONSTRAINT `#sql-37c_2f534_ibfk_5` FOREIGN KEY (`child_column`) REFERENCES `parent_table` (`parent_column`))

    How do you fix it? To fix this, you need to find all the child values and get rid of them, either by setting them to NULL (if that’s allowed), or by making them actually allowed values. If you’re pretty MySQL savvy, you can make a query to check what values are offending. It should look something like:SELECT `child_table`.`child_column` from `child_table` where `child_table`.`child_column` IS NOT NULL AND NOT EXISTS (SELECT * FROM `parent_table` WHERE `parent_table`.`parent_column`=`child_table`.`child_column`)
    It will look a little different if you have a multiple column foreign key. If any rows are returned, those are the offenders. If you are using Eliacom’s MySQL GUI tool to create the foreign key, if there are any offending child values, it will list them when you attempt to create the foreign key, to help you find the values that need to be fixed. If you don’t know how to add foreign keys using Eliacom’s MySQL GUI tool, see the video tutorial on adding foreign keys and indexes.

    How not to fix it: Some people say that you can use the query «SET foreign_key_checks=0» to get around this. That is true, but don’t do this, unless you think that the discrepancy is temporary. For instance, this is useful when cloning a database. If you copy over a child table data before the parent table data, the parent values won’t be there to start with, so the foreign key constraint will fail. In this case you should use foreign_key_checks=0, because once the parent table is in place everything will match. But if this isn’t used carefully, you can end up with child data that doesn’t match to any parent data in your child table. Use wisely!

  2. No Error, but Foreign Key Won’t Create: Table Isn’t InnoDB: Click for solution

    Both tables need to be using the InnoDB Engine. If they don’t, then MySQL will NOT throw an error, but it also won’t create the foreign key. It just silently dies. This is probably worse than actually getting an error. So if you create a foreign key, and then the foreign key isn’t there, see if you are using the InnoDB Engine for both the child and parent tables. If you do this, you will get:

    No error at all!

    How do you fix it? You will have to change the engine for your tables. You can do this by doing ALTER TABLE `tableName` ENGINE=InnoDB; If you’re using Eliacom’s MySQL GUI tool, then when you go to create the foreign key, it will check the engines and throw an error if they don’t match before attempting to do the query. If you don’t how know to add foreign keys using Eliacom’s MySQL GUI tool, see the video tutorial on adding foreign keys and indexes.

  3. Identifier Name is Too Long: Click for solution

    This is an issue with the fact that the MySQL doesn’t allow any identifier names to be longer than 64 characters. Note, that if your table name is pushing 64 characters, then the way that MySQL creates the default constraint name is using the table, and a suffix/prefix appended to it so that it might exceed the number of characters. Apparently, this error sometimes allows things like table creation even though the foreign key creation failed. If you have this problem, you will get an error that looks like this:

    ERROR 1059 (42000): Identifier name ‘myreallyreallyreallyreallyreallllllllllyreallyreallyreallyreallyreallylongname’ is too long

    How do you fix it?This one is more tricky. If it’s because you have a really long table name, then you can’t let MySQL assign the foreign key name automatically since it will throw the error. So you will have to set it manually. See the syntax in the Foreign Key White Paper for how to set this manually. If you are using Eliacom’s MySQL GUI tool, then when you go to create the foreign key, there is a spot in the foreign key creation form for you to create your own foreign key constraint name. If you don’t how know to add foreign keys using Eliacom’s MySQL GUI tool, see the video tutorial on adding foreign keys and indexes.

  4. A Foreign Key Constraint Fails (Duplicate Foreign Keys): Click for solution

    I have gotten the «foreign key constraint fails» error a few times, when I’ve tried to update a parent table (with an ON UPDATE CASCADE foreign key, but it might happen with other types). I have double-double checked there is no data that is bad. In the end, the problem was that I had two identical foreign keys. It appears that when one tried to update the child table, the other caught it as an attempt to change the child table. The indicator that this is your problem is if you are updating the parent table, and it complains about the parent/child relationship. You should get an error that looks like this:

    ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`database`.`#sql-37c_2f534`, CONSTRAINT `#sql-37c_2f534_ibfk_5` FOREIGN KEY (`child_column`) REFERENCES `parent_table` (`parent_column`) ON UPDATE CASCADE)

    How do you fix it? Easy one. Just delete the duplicate foreign key. To get the foreign key names, you can use SHOW CREATE TABLE to see what the constraint names are to delete them. If you are using Eliacom’s MySQL GUI tool, you can delete the foreign key from the «Foreign Keys» tab for that table. If you don’t how know to find foreign keys using Eliacom’s MySQL GUI tool, see the video tutorial on foreign keys and indexes.

  5. Anything else?: Click for solution

    If you’ve run into something that doesn’t seem to be here, let us know. Even if you figured it out, we’d love to help out future generations.

Frustrated?

There are many reasons why you can get foreign key errors, and often very different reasons give the same error, which is why it’s sometimes so hard to track down exactly what is the cause. The purpose of this white paper is to create an exhaustive list of the reasons why you get these error, and then to expand on that list with other things that can go wrong when trying to create foreign keys. Again, if you happen to run into a situation we don’t cover, please let us know so we can try to help you, and so we can put the information here to help future generations?

Obscure MySQL Error

We hope you found this white paper useful. Please let us know if you have any questions you felt were not addressed in the white paper or if you have any feedback: Contact Us
Источник

Introduction

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The purpose of the foreign key is to identify a particular row of the referenced table. Therefore, it is required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value). This is called a referential integrity constraint between the two tables. Because violations of these constraints can be the source of many database problems, most database management systems provide mechanisms to ensure that every non-null foreign key corresponds to a row of the referenced table. Consider following simple example:

create table parent (
    id int not null primary key,
    name char(80)
) engine=innodb;

create table child (
    id int not null,
    name char(80),
    parent_id int, 
    foreign key(parent_id) references parent(id)
) engine=innodb;

As far as I know, the following storage engines for MariaDB and/or MySQL support foreign keys:

  • InnoDB (both innodb_plugin and XtraDB)
  • PBXT (https://mariadb.com/kb/en/mariadb/about-pbxt/)
  • SolidDB for MySQL (http://sourceforge.net/projects/soliddb/)
  • ScaleDB (https://mariadb.com/kb/en/mariadb/scaledb/ and http://scaledb.com/pdfs/TechnicalOverview.pdf)
  • MySQL Cluster NDB 7.3 or later (https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndb-innodb-engines.html)

MariaDB foreign key syntax is documented at https://mariadb.com/kb/en/mariadb/foreign-keys/ (and MySQL at http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html). While most of the syntax is parsed and checked when the CREATE TABLE or ALTER TABLE clause is parsed, there are still several error cases that can happen inside InnoDB. Yes, InnoDB has its own internal foreign key constraint parser (in dict0dict.c function dict_create_foreign_constraints_low()).

However, the error messages shown in CREATE or ALTER TABLE, and SHOW WARNINGS in versions of MariaDB prior to 5.5.45 and 10.0.21 are not very informative or clear. There are additional error messages if you issue SHOW ENGINE INNODB STATUS, which help, but were not an ideal solution. In this blog I’ll present a few of the most frequent error cases using MariaDB 5.5.44 and how these error messages are improved in MariaDB 5.5.45 and 10.0.21. I will use the default InnoDB (i.e. XtraDB) but innodb_plugin works very similarly.

Constraint name not unique

Foreign name constraint names must be unique in a database. However, the error message is unclear and leaves a lot unclear:

--------------
CREATE TABLE t1 (
  id int(11) NOT NULL PRIMARY KEY,
  a int(11) NOT NULL,
  b int(11) NOT NULL,
  c int not null,
  CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
--------------
Query OK, 0 rows affected (0.45 sec)

--------------
CREATE TABLE t2 (
id int(11) NOT NULL PRIMARY KEY,
a int(11) NOT NULL,
b int(11) NOT NULL,
c int not null,
CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id),
CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
--------------

ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update")
--------------
show warnings
--------------

+---------+------+--------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------+
| Error | 1005 | Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") |
| Warning | 1022 | Can't write; duplicate key in table 't2' |
+---------+------+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

These messages are not very helpful because there are two foreign key constraints. Looking into SHOW ENGINE INNODB STATUS we get a better message:

show engine innodb status
--------------
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2015-07-30 12:37:48 7f44a1111700 Error in foreign key constraint creation for table `test`.`t2`.
A foreign key constraint of name `test`.`test`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:

CREATE TABLE t1 (
  id int(11) NOT NULL PRIMARY KEY,
  a int(11) NOT NULL,
  b int(11) NOT NULL,
  c int not null,
  CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
--------------

Query OK, 0 rows affected (0.14 sec)

--------------
CREATE TABLE t2 (
  id int(11) NOT NULL PRIMARY KEY,
  a int(11) NOT NULL,
  b int(11) NOT NULL,
  c int not null,
  CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id),
  CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
--------------

ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 121)
--------------
show warnings
--------------

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                                                                                     |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  121 | Create or Alter table `test`.`t2` with foreign key constraint failed. Foreign key constraint `test/test` already exists on data dictionary. Foreign key constraint names need to be unique in database. Error in foreign key definition: CONSTRAINT `test` FOREIGN KEY (`b`) REFERENCES `test`.`t2` (`id`). |
| Error   | 1005 | Can't create table 'test.t2' (errno: 121)                                                                                                                                                                                                                                                                   |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

No index

There should be an index for columns in a referenced table that contains referenced columns as the first columns.

create table t1(a int, b int, key(b)) engine=innodb
--------------
Query OK, 0 rows affected (0.46 sec)

--------------
create table t2(a int, b int, constraint b foreign key (b) references t1(b), constraint a foreign key a (a) references t1(a)) engine=innodb
--------------

ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
--------------
show warnings
--------------

+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 150 | Create table 'test/t2' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error | 1005 | Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") |
| Warning | 1215 | Cannot add foreign key constraint |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Fine but again we have no idea which foreign key it was. As before, there is a better message in the SHOW ENGINE INNODB STATUS output:

LATEST FOREIGN KEY ERROR
------------------------
2015-07-30 13:44:31 7f30e1520700 Error in foreign key constraint of table test/t2:
 foreign key a (a) references t1(a)) engine=innodb:
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.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:

create table t1(a int, b int, key(b)) engine=innodb
--------------

Query OK, 0 rows affected (0.16 sec)

--------------
create table t2(a int, b int, constraint b foreign key (b) references t1(b), constraint a foreign key a (a) references t1(a)) engine=innodb
--------------

ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150)
--------------
show warnings
--------------

+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  150 | Create  table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Error close to  foreign key a (a) references t1(a)) engine=innodb. |
| Error   | 1005 | Can't create table 'test.t2' (errno: 150)                                                                                                                                                                                              |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Referenced table not found

A table that is referenced on foreign key constraint should exist in InnoDB data dictionary. If not:

create table t1 (f1 integer primary key) engine=innodb
--------------

Query OK, 0 rows affected (0.47 sec)

--------------
alter table t1 add constraint c1 foreign key (f1) references t11(f1)
--------------

ERROR 1005 (HY000): Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed")
--------------
show warnings
--------------

+---------+------+-----------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                             |
+---------+------+-----------------------------------------------------------------------------------------------------+
| Error   | 1005 | Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") |
| Warning | 1215 | Cannot add foreign key constraint                                                                   |
+---------+------+-----------------------------------------------------------------------------------------------------+
show engine innodb status
--------------
LATEST FOREIGN KEY ERROR
------------------------
2015-07-30 13:44:34 7f30e1520700 Error in foreign key constraint of table test/#sql-2612_2:
 foreign key (f1) references t11(f1):
Cannot resolve table name close to:
(f1)

Both messages are first referring to an internal table name and the foreign key error message is referring to an incorrect name. In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:

create table t1 (f1 integer primary key) engine=innodb
--------------

Query OK, 0 rows affected (0.11 sec)

--------------
alter table t1 add constraint c1 foreign key (f1) references t11(f1)
--------------

ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150)
--------------
show warnings
--------------

+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                    |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  150 | Alter  table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary close to  foreign key (f1) references t11(f1). |
| Error   | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150)                                                                                                                         |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

--------------
show engine innodb status
--------------
150730 13:50:36 Error in foreign key constraint of table `test`.`t1`:
Alter  table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary close to  foreign key (f1) references t11(f1).

Temporary tables

Temporary tables can’t have foreign key constraints because temporary tables are not stored to the InnoDB data dictionary.

create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb
--------------

ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
--------------
show warnings
--------------

+---------+------+--------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                    |
+---------+------+--------------------------------------------------------------------------------------------+
| Error   | 1005 | Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") |
| Warning | 1215 | Cannot add foreign key constraint                                                          |
+---------+------+--------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

--------------
show engine innodb status
--------------
LATEST FOREIGN KEY ERROR
------------------------
2015-07-30 13:44:35 7f30e1520700 Error in foreign key constraint of table tmp/#sql2612_2_1:
foreign key(a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb

--------------
alter table t1 add foreign key(b) references t1(a)
--------------

ERROR 1005 (HY000): Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed")
--------------
show warnings
--------------

+---------+------+-----------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                             |
+---------+------+-----------------------------------------------------------------------------------------------------+
| Error   | 1005 | Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") |
| Warning | 1215 | Cannot add foreign key constraint                                                                   |
+---------+------+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

These error messages do not really help the user, because the actual reason for the error is not printed and the foreign key error references an internal table name. In MariaDB 5.5.45 and 10.0.21 this is clearly improved:

create temporary table t1(a int not null primary key, b int, key(b)) engine=innodb
--------------

Query OK, 0 rows affected (0.04 sec)

--------------
create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb
--------------

ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150)
--------------
show warnings
--------------

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                             |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  150 | Create  table `tmp`.`t2`Ï with foreign key constraint failed. Referenced table `tmp`.`t1` not found in the data dictionary close to foreign key(a) references t1(a)) engine=innodb.  |
| Error   | 1005 | Can't create table 'test.t2' (errno: 150)                                                                                                                                           |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

--------------
alter table t1 add foreign key(b) references t1(a)
--------------

ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150)
--------------
show warnings
--------------

+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                             |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  150 | Alter  table `tmp`.`t1`Ï with foreign key constraint failed. Referenced table `tmp`.`t1` not found in the data dictionary close to foreign key(b) references t1(a).  |
| Error   | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150)                                                                                                                  |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Column count does not match

There should be exactly the same number of columns in both the foreign key column list and the referenced column list. However, this currently raises the following error:

create table t1(a int not null primary key, b int, key(b)) engine=innodb
--------------

Query OK, 0 rows affected (0.17 sec)

--------------
alter table t1 add foreign key(a,b) references t1(a)
--------------

ERROR 1005 (HY000): Can't create table 'test.#sql-4856_1' (errno: 150)
--------------
show warnings
--------------

+-------+------+----------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------+
| Error | 1005 | Can't create table 'test.#sql-4856_1' (errno: 150) |
+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

-----------------+
show engine innodb status;
-----------------+
LATEST FOREIGN KEY ERROR
------------------------
150730 15:15:57 Error in foreign key constraint of table test/#sql-4856_1:
foreign key(a,b) references t1(a):
Syntax error close to: 2015-07-30 13:44:35 7f30e1520700 Error in foreign key constraint of table tmp/#sql2612_2_2: foreign key(b) references t1(a): Cannot resolve table name close to: (a)

The error message is not clear and the foreign key error refers to an internal table name. In MariaDB 5.5.45 and 10.0.21 there is additional information:

create table t1(a int not null primary key, b int, key(b)) engine=innodb
--------------

Query OK, 0 rows affected (0.14 sec)

--------------
alter table t1 add foreign key(a,b) references t1(a)
--------------

ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150)
--------------
show warnings
--------------

+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. Foreign key constraint parse error in foreign key(a,b) references t1(a) close to ). Too few referenced columns, you have 1 when you should have 2. |
| Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Incorrect cascading

A user may define a foreign key constraint with ON UPDATE SET NULL or ON DELETE SET NULL. However, this requires that the referenced columns are not defined as NOT NULL. Currently, the error message on this situation is:

create table t1 (f1 integer not null primary key) engine=innodb
--------------

Query OK, 0 rows affected (0.40 sec)

--------------
alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null
--------------

ERROR 1005 (HY000): Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed")
--------------
show warnings
--------------

+---------+------+-----------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                             |
+---------+------+-----------------------------------------------------------------------------------------------------+
| Error   | 1005 | Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") |
| Warning | 1215 | Cannot add foreign key constraint                                                                   |
+---------+------+---------------------------------------------------------------------------------------------

--------+
show engine innodb status;
--------+
LATEST FOREIGN KEY ERROR
------------------------
2015-07-30 13:44:37 7f30e1520700 Error in foreign key constraint of table test/#sql-2612_2:
 foreign key (f1) references t1(f1) on update set null:
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.

Both error messages are not very useful, because the first does not really tell how the foreign key constraint is incorrectly formed and later does not say which column has the problem. This is improved in MariaDB 5.5.45 and 10.0.21:

create table t1 (f1 integer not null primary key) engine=innodb
--------------

Query OK, 0 rows affected (0.10 sec)

--------------
alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null
--------------

ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150)
--------------
show warnings
--------------

+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                         |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  150 | Alter  table `test`.`t1` with foreign key constraint failed. You have defined a SET NULL condition but column f1 is defined as NOT NULL in  foreign key (f1) references t1(f1) on update set null close to  on update set null. |
| Error   | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150)                                                                                                                                                                              |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Incorrect types

Column types for foreign key columns and referenced columns should match and use the same character set. If they do not, you currently get:

create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb
--------------

Query OK, 0 rows affected (0.47 sec)

--------------
create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb
--------------

ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
--------------
show warnings
--------------

+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                         |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  150 | Create table 'test/t2' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
 |
| Error   | 1005 | Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")                                                                      |
| Warning | 1215 | Cannot add foreign key constraint                                                                                                                               |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

--------+
show engine innodb status;
--------+
LATEST FOREIGN KEY ERROR
------------------------
2015-07-30 13:44:39 7f30e1520700 Error in foreign key constraint of table test/t2:
foreign key(a) references t1(f1)) engine=innodb:
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.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

But do we have an index for the referenced column f1 in the table t2? So if there are multiple columns in both the foreign key column list and the referenced column list, where do we look for the error? In MariaDB 5.5.45 and 10.0.21 this is improved by:

create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb
--------------

Query OK, 0 rows affected (0.15 sec)

--------------
create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb
--------------

ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150)
--------------
show warnings
--------------

+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                            |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  150 | Create  table `test`.`t2` with foreign key constraint failed. Field type or character set for column a does not mach referenced column f1 close to foreign key(a) references t1(f1)) engine=innodb |
| Error   | 1005 | Can't create table 'test.t2' (errno: 150)                                                                                                                                                          |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Conclusions

There are several different ways to incorrectly define a foreign key constraint. In many cases when using earlier versions of MariaDB (and MySQL), the error messages produced by these cases were not very clear or helpful. In MariaDB 5.5.45 and 10.0.21 there are clearly improved error messages to help out the user. Naturally, there is always room for further improvements, so feedback is more than welcome!

References

  • https://mariadb.atlassian.net/browse/MDEV-6697
  • https://mariadb.atlassian.net/browse/MDEV-8524
Источник

Понравилась статья? Поделить с друзьями:
  • Mysql error 1410 42000 you are not allowed to create a user with grant
  • Mysql error 1396 hy000 operation alter user failed
  • Mysql error 1317
  • Mysql error 1130 localhost is not allowed to connect to this mysql server
  • Mysql error 1130 hy000 host is not allowed to connect to this mysql server