MySQL Bugs: #27576: "references" in field declaration is silently ignored for innodb

Bug #27576	"references" in field declaration is silently ignored for innodb
Submitted:	1 Apr 2007 21:56	Modified:	30 Aug 2007 14:56
Reporter:	Philip karasko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.27-community	OS:	Any
Assigned to:	MC Brown	CPU Architecture:	Any

Description:
InnoDB engine parses but  ignores foreign key constraints when they are declared 'inline' in create table:
...
  field_id int REFERENCES master_table(id),
...

the same constraints are applied when the rule is specified in the “foreign key” section.
  FOREIGN KEY (field_id) REFERENCES master_table(id)  -- this works

How to repeat:

create table languages  ( 
  id int auto_increment primary key,
  title varchar(60)
) engine = InnoDB;

create table category_styles  ( -- category types
  id int auto_increment primary key,
  language_id int REFERENCES languages(id),
  style_name varchar(60) ,
  style_comment varchar(60) 
 -- ,  FOREIGN KEY (language_id) REFERENCES languages(id)
) engine = InnoDB;

--------- populate sample   ------------
-- insert into languages( id,title)
-- values (1,'English');

insert into category_styles (id,language_id,style_name,style_comment)
values (1,1,'test',null);

--- the insert must fail

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Our manual says at http://dev.mysql.com/doc/refman/5.0/en/create-table.html:

#InnoDB tables support checking of foreign key constraints. See Section 14.2, “The InnoDB Storage Engine”. Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented for the CREATE TABLE statement at the beginning of this section: The columns of the referenced table must always be explicitly named. InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys. For the precise syntax, see Section 14.2.6.4, “FOREIGN KEY Constraints”.

For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. The CHECK clause is parsed but ignored by all storage engines. See Section 1.9.5.5, “Foreign Keys”.

And at http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html:

 InnoDB also supports foreign key constraints. The syntax for a foreign key constraint definition in InnoDB looks like this:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

If it is not clear, feel free to reopen the report and change category to "Documentation"

the statements to reproduce the behavior don't contradict the documentation:
there at least 3 problems here :
1) conceptual 
2) documentation
3) error reporting
4) incorrect interpretation/parsing of the DDL statements

the case I have submitted doesn't contradict the documentation, moreover there is a direct hint to the inline style:
column_definition:
    col_name data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string'] [reference_definition]
http://dev.mysql.com/doc/refman/5.0/en/create-table.html

as innodb engine doesn't should not parse directly the statements, it should not matter if the constraint (or primary key for that matter) is defined on a separate expression line, or inline while next to the field it applies to.

if for , some reason, the parser cannot pass this information to innodb, in no case it should be ignored, but rather the whole "create table" statement should fail.

this "feature" is potentially dangerous and could lead to data inconsistent data, hence to data corruption.

Thank you for the additional comment.

Verified as documentation bug.

The documentation has been updated to note that inline (per column) REFERENCES specifications are not honoured but are silently ignored.