MySQL: Broken foreign key implementaion?

Question

The inline syntax for defining foreign keys in MySQL does not seem to do anything, while the longer CONSTRAINT syntax seems to work as expected. I'm very curious why this happens.

I have recently discovered that the inline syntax for defining foreign keys, which I thought would work normally, doesn't do any actual checking of referential integrity. It doesn't matter what is defined in the ON DELETE/UPDATE clauses.
I have of course tried this out with InnoDB since I know that MyISAM doesn't support foreign key checks.

Please have a look at the examples/fiddles and you will see what I mean.

Inline Syntax

This is a correct way of defining foreign keys according to the documentation, which I have used for years.

-- Create a basic foreign key relationship.
CREATE TABLE `parent` (
  `id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `a` VARCHAR(255) NOT NULL
)ENGINE=InnoDB; -- Just to be sure.

CREATE TABLE `child` (
  `id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  -- Short legitimate syntax, frequently used.
  `parent_id` INTEGER UNSIGNED NOT NULL REFERENCES `parent` (`id`)
    ON DELETE RESTRICT
)ENGINE=InnoDB; -- Just to be sure.

Now if I delete a row from the parent table, which is referenced in the child table, it is deleted just as I had never defined the foreign key. Try it for yourself: Fiddle

CONSTRAINT syntax

The longer syntax using the CONSTRAINT keyword is more cumbersome to write, but seems to work as expected in contrast to the inline definition.

-- `parent` table has been omitted, since it is the same as above.
CREATE TABLE `child` (
  `id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `parent_id` INTEGER UNSIGNED NOT NULL,
  -- Longer, more cumbersome syntax.
  CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
    ON DELETE RESTRICT
)ENGINE=InnoDB; -- Just to be sure.

If I try to delete a referenced row from parent it fails as expected. Try it for yourself: Fiddle (You have to uncomment the last statement on the left hand side to see it fail)

Conclusion/Actual Question

As you have seen for yourself in the fiddles, the inline syntax for creating foreign keys does not seem to do anything, while the longer CONSTRAINT syntax works fine.

Does anyone know why it is that way? Is there any reason or is this just another MySQL quirk we have to work around?
Please share your knowledge, I'm very curious.

Update/SHOW CREATE TABLE Verification

Here's the output of SHOW CREATE TABLE as pointed out by @MikePurcell.

"Short" Syntax

CREATE TABLE `child`(
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

CONSTRAINT Syntax

CREATE TABLE `child` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_child_parent` (`parent_id`),
  CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

It is now clear that the parser simply ignores the REFERENCES clause in the table definition using the "short" syntax. At least a documentation update would be helpful. Thanks for the help guys.

FYI: This has already been filed as bug back in 2004 (Bug #4919) and seems to be known a while longer. I truly hope they will at least update the documentation concerning this, because I don't think that this will be fixed anytime soon.

Answer 1

The only "short syntax" I used to know of was:

create_definition:
    col_name column_definition
...
    | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

Notice the mandatory FOREIGN KEY clause.

However, the parser does accepts the "shorter syntax" you used, in accordance with the manual:

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
...
      [reference_definition]

Notice the lack of FOREIGN KEY clause.

As a matter of fact, this odd behaviour is documented in the same page:

MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

The limitation is also discussed more extensively in the tutorial.

Whether this is a bug or a missing feature has been long debated, so it seems.