Bug #155 ANSI-style quotes don't work for FOREIGN KEY
Submitted: 15 Mar 2003 16:35 Modified: 22 Apr 2003 10:35
Reporter: Matthias Urlichs Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.1 OS:Linux (Linux)
Assigned to: Heikki Tuuri CPU Architecture:Any

[15 Mar 2003 16:35] Matthias Urlichs
Description:
ANSI quoting style doesn't work for FOREIGN KEY constraints on InnoDB. 
 
I'm migrating a system which dynamically creates tables, and this is one of the more 
annoying stumbling blocks. :-/ 

How to repeat:
Start mysqld with --sql-mode=ANSI_QUOTES. 
 
mysql> create table t1(id int not null, primary key(id)) type=innodb; 
mysql> create table t2(id int not null, primary key(id)) type=innodb; 
mysql> alter table t2 add constraint foreign key(id) references "t1"(id); 
ERROR 1005: Can't create table './test/#sql-5404_e92.frm' (errno: 150) 
mysql> alter table t2 add constraint foreign key(id) references `t1`(id); 
mysql> 
 
The same thing works for MyISAM tables: 
mysql> create table t1(id int not null, primary key(id)) type=myisam; 
mysql> create table t1(id int not null, primary key(id)) type=myisam; 
mysql> alter table t2 add constraint foreign key(id) references "t1"(id); 
mysql> 

Suggested fix:
[22 Apr 2003 10:35] Heikki Tuuri
Fixed in 4.0.13.
[12 Aug 2003 10:38] Sergey
I still have this bug in versions 4.0.14b and 4.1.0-alpha for Windows.
Should I submit it as new bug?

Sergey.
[12 Aug 2003 10:58] Matthias Urlichs
... why not re-open this one?