MySQL Bugs: #2424: You cannot create a constraint if reference table has spaces in its name

Bug #2424	You cannot create a constraint if reference table has spaces in its name
Submitted:	16 Jan 2004 5:25	Modified:	3 Feb 2004 11:57
Reporter:	Juan Pedro Reyes Molina	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.0.17	OS:	Windows (Windows 2000 server)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
If you try to create a foreign key constraint an the reference table has spaces in its name you get the error: [Explotación] ERROR 1005: Can't create table '.\test\maestro_de_cuentas.frm' (errno: 150)

How to repeat:
Create these tables:

CREATE TABLE `maestro_de_bancos` (
  `Banco` varchar(4) NOT NULL default '',
  `Descripción` varchar(50) default NULL,
  PRIMARY KEY  (`Banco`)
) TYPE=InnoDB; 

CREATE TABLE `maestro_de_cuentas` (
  `Id cuenta` int(11) auto_increment,
  `Banco` varchar(4) default NULL,
  `Sucursal` varchar(4) default NULL,
  `Dígito Control` char(2) default NULL,
  `Cuenta corriente` varchar(15) default NULL,
  PRIMARY KEY  (`Id cuenta`),
  KEY `cuenta` (`Banco`,`Sucursal`,`Cuenta corriente`),
  CONSTRAINT FOREIGN KEY (`Banco`) REFERENCES `maestro_de_bancos` (`Banco`) ON UPDATE CASCADE on delete restrict
) TYPE=InnoDB; 

rename `maestro_de_bancos` to `maestro de bancos`

issue a show create table `maestro_de_bancos` and you will get:

CREATE TABLE `maestro_de_cuentas` (
  `Id cuenta` int(11) NOT NULL auto_increment,
  `Banco` varchar(4) default NULL,
  `Sucursal` varchar(4) default NULL,
  `Dígito Control` char(2) default NULL,
  `Cuenta corriente` varchar(15) default NULL,
  PRIMARY KEY  (`Id cuenta`),
  KEY `cuenta` (`Banco`,`Sucursal`,`Cuenta corriente`),
  CONSTRAINT `0_60` FOREIGN KEY (`Banco`) REFERENCES `maestro de bancos` (`Banco`) ON UPDATE CASCADE
) TYPE=InnoDB; 

drop table `maestro_de_cuentas` and then submit the create table you have just obtained. It won't work despite it was the system you gave it to you.

Verified with 4.0.18/Linux.  Thank you.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

It appears that the bug fix for Bug #1725 also fixed this issue.
(At least the supplied test worked fine for me)

Regards,
Monty