MySQL Bugs: #55897: Foreign Key's Referenced table name always forced to lower-case

Bug #55897	Foreign Key's Referenced table name always forced to lower-case
Submitted:	11 Aug 2010 0:42	Modified:	11 Aug 2010 7:27
Reporter:	Kevin Yang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Windows	Severity:	S2 (Serious)
Version:	5.1.43-community	OS:	Windows (Windows XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	foreign key table name lower-case

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

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

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

How to repeat:
1. 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 FK_TAB_DEPARTMENT_TAB_COMPANY_COMPANYID(COMPANYID) REFERENCES TAB_COMPANY(ID) ON DELETE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

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

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

Thanks a lot. I should read the document more carefully.

Tomorrow will be a busy day, I have convert all sql scripts and java code which contain UPPER CASE table names and fields to lower case.