MySQL Bugs: #73757: Request: make error 150 specify constraint name

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

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:
.

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") */

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)

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)

should be nice documment warnings at http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html