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 6:25	Modified:	3 Feb 2004 12:57
Reporter:	Juan Pedro Reyes Molina
Status:	Closed
Category:	Server: InnoDB	Severity:	S3 (Non-critical)
Version:	4.0.17	OS:	Microsoft Windows (Windows 2000 server)
Assigned to:	Bugs System	Target Version:

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