MySQL Bugs: #42418: Referential constraint as part of column definition is ignored

Bug #42418	Referential constraint as part of column definition is ignored
Submitted:	28 Jan 2009 12:52	Modified:	3 Feb 2009 15:30
Reporter:	Bernt Marius Johnsen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.74, 6.0.9, any	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Referential constraint as part of column definition is ignored without error message or warning. This is valid standard SQL which should either work or fail, not be ignored.

I know it is documented behavior, but still I consider it a bug which now has cost me several hours of my time!

How to repeat:
set storage_engine=innodb;
create table t1 (pk integer primary key);
create table t2 (fk integer references t1(pk));
insert into t2 values (1);

Thank you for a problem report. You are right, it is simply ignored:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)

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

mysql> drop table t1;
Query OK, 0 rows affected (0.81 sec)

mysql> drop table t2;
Query OK, 0 rows affected (0.27 sec)

mysql> set storage_engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (pk integer primary key);
Query OK, 0 rows affected (0.16 sec)

mysql> create table t2 (fk integer references t1(pk));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t2 values (1);
Query OK, 1 row affected (0.05 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `fk` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.06 sec)

while our manual (http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html) claims:

"This syntax will cause an error if used in trying to define an InnoDB table."

So, this is a bug.

It's a duplicate because the same behaviour is described in
Bug#17943 "Foreign Keys: inline foreign key constraint definition should give
a warning" and Bug #19173 "Foreign Key reference integrity".
Those are feature requests because the behaviour
is described in the manual, for example here:
http://dev.mysql.com/doc/refman/6.0/en/example-foreign-keys.html

That page does hint about an error with InnoDB (not necessarily
at CREATE TABLE time). I've written to docs-private about that.