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
• Status: Closed
• 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?