Bug #73757 Request: make error 150 specify constraint name
Submitted: 28 Aug 2014 14:06
Reporter: Federico Razzoli Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:any OS:Any
Assigned to: CPU Architecture:Any
Tags: foreign-keys

[28 Aug 2014 14:06] Federico Razzoli
Description:
When creating a table with multiple foreign keys, this error can be a bit frustrating:

ERROR 1005 (HY000): Can't create table `mwa`.`tab1` (errno: 150 "Foreign key constraint is incorrectly formed")

We would save some time if we could see a constraint name in the error. I mean something like this:

ERROR 1005 (HY000): Can't create table `mwa`.`tab1` (errno: 150 "Foreign key constraint: `fk_city` is incorrectly formed")

I hope it is possible.

How to repeat:
.
[29 Aug 2014 20:04] Roberto Spadim
test case:

create table t11 (f1 integer primary key) engine innodb;
alter table t11 add constraint c1 foreign key (f1) references t1(f1);

/* Erro SQL (1005): Can't create table `teste`.`#sql-3f90_25c30` (errno: 150 "Foreign key constraint is incorrectly formed") */
[29 Aug 2014 20:09] Shane Bester
Current 5.7 version behaves like this:

mysql> create table t1(a int)engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t2(a int, constraint a foreign key a (a) references t1(a))engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 150
Message: 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.

*************************** 2. row ***************************
  Level: Error
   Code: 1215
Message: Cannot add foreign key constraint
2 rows in set (0.00 sec)
[29 Aug 2014 20:10] Shane Bester
mysql> create table t11 (f1 integer primary key) engine innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> alter table t11 add constraint c1 foreign key (f1) references t1(f1);
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> show warnings;
+-------+------+-----------------------------------+
| Level | Code | Message                           |
+-------+------+-----------------------------------+
| Error | 1215 | Cannot add foreign key constraint |
+-------+------+-----------------------------------+
1 row in set (0.00 sec)

So, nothing useful there.
---------

show engine innodb status is a bit more verbose:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2014-08-29 22:08:17 0x288c Error in foreign key constraint of table test/#sql-2654_2:
 foreign key (f1) references t1(f1):
Cannot resolve table name close to:
(f1)
[29 Aug 2014 20:29] Roberto Spadim
should be nice documment warnings at http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html