MySQL foreign keys' referenced table name always forced to lowercase

Question

First, create these two tables:

CREATE TABLE IF NOT EXISTS TAB_COMPANY (
  ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(100) NOT NULL,
  PRIMARY KEY(ID),
  UNIQUE KEY(NAME)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS TAB_DEPARTMENT (
  ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(100) NOT NULL,
  COMPANYID INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY(ID),
  INDEX FK_TAB_DEPARTMENT_TAB_COMPANY_COMPANYID(COMPANYID ASC),
  CONSTRAINT FK_TAB_DEPARTMENT_TAB_COMPANY_COMPANYID
    FOREIGN KEY (COMPANYID) REFERENCES TAB_COMPANY(ID) ON DELETE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

Then show create table TAB_DEPARTMENT:

CREATE TABLE `TAB_DEPARTMENT` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `NAME` varchar(100) NOT NULL,
  `COMPANYID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK_TAB_DEPARTMENT_TAB_COMPANY_COMPANYID` (`COMPANYID`),
  CONSTRAINT `FK_TAB_DEPARTMENT_TAB_COMPANY_COMPANYID` FOREIGN KEY (`COMPANYID`)
REFERENCES `tab_company` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The table name in foreign key reference clause is forced to lower case.

mysql> show variables like '%lower%';
lower_case_file_system ON
lower_case_table_names 0

mysql> show variables like 'version';
version 5.1.43-community

My platform is Window XP with SP3. I tested this on Linux, it is OK.

Has anyone encountered this issue before? I already reported a bug to MySQL.

I just tried on version 5.1.49-community, and the issue is still there.

Answer 1

Got the reply from MySQL:

From Miguel Solorzano:

Thank you for the bug report. This a documented restriction of InnoDB table:

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

"On Windows, InnoDB always stores database and table names internally in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, you should create all databases and tables using lowercase names. "