MySQL Bugs: #31456: Foreign keys (with InnoDB) aren't working (multiple problems)

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

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

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>

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 :-)

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)

This is a feature request that is already filed at bugs.mysql.com.

This seems to not be fixed as of version 5.5.8, is that correct ?