Bug #31456 Foreign keys (with InnoDB) aren't working (multiple problems)
Submitted: 8 Oct 2007 15:43 Modified: 10 Oct 2007 12:05
Reporter: Dmitry Tkach Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.0.45/4.1 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: D5 (Feature request)

[8 Oct 2007 15:43] Dmitry Tkach
Description:
mysql> create table x (a int primary key) engine innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> create table y (b int references x) engine innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into y values (1);
Query OK, 1 row affected (0.00 sec) 
*** This should have failed!

mysql> alter table y add foreign key (b) references x(a);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'x(a)' at line 1
*** I don't see any problems the syntax!

mysql> alter table y add foreign key (b) references x;
ERROR 1005 (HY000): Can't create table './test/#sql-64bf_7.frm' (errno: 150)
*** ????

How to repeat:
see description
[8 Oct 2007 16:52] Miguel Solorzano
Thank you for the bug report. I was not able to repeat with current
source server:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.23-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table x (a int primary key) engine innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> create table y (b int references x) engine innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table y\G
*************************** 1. row ***************************
       Table: y
Create Table: CREATE TABLE `y` (
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table y add foreign key (b) references x(a);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table y\G
*************************** 1. row ***************************
       Table: y
Create Table: CREATE TABLE `y` (
  `b` int(11) DEFAULT NULL,
  KEY `b` (`b`),
  CONSTRAINT `y_ibfk_1` FOREIGN KEY (`b`) REFERENCES `x` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>
[8 Oct 2007 22:52] Dmitry Tkach
mysql> create table y (b int references x) engine innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table y\G
*************************** 1. row ***************************
       Table: y
Create Table: CREATE TABLE `y` (
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

So, it seems that it happily and silently ignored the FK given with the table definition ... Looks like you very well CAN reproduce it :-)
[9 Oct 2007 0:34] Miguel Solorzano
Thank you for the feedback. Yes at least that should be documented or fixed.

mysql> create table y (b int references x) engine innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table y\G
*************************** 1. row ***************************
       Table: y
Create Table: CREATE TABLE `y` (
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[10 Oct 2007 12:05] Heikki Tuuri
This is a feature request that is already filed at bugs.mysql.com.
[12 Aug 2011 14:01] Morg. What?
This seems to not be fixed as of version 5.5.8, is that correct ?