Major bug in MySQL 8.x? — foreign keys

While retrieving foreign keys information from MySQL (for a code generator) I noticed this strange behavior. It looks like a major bug in MySQL 8.x. When I create a foreign key using REFERENCES the engine does not enforce it. For example:

create table p (
  id int primary key not null
) engine=innodb;

create table q (
  pid int references p (id)
) engine=innodb;

insert into q (pid) values (123); -- succeeds (!)

See example at DB Fiddle.

However, if I create the foreign key typing FOREIGN KEY (col) REFERENCES table (col) it works properly:

create table p (
  id int primary key not null
) engine=innodb;

create table r (
  pid int,
  foreign key (pid) references p (id)
) engine=innodb;

insert into r (pid) values (456); -- fails, as expected

See running example at DB Fiddle.

If this is a bona fide major bug in MySQL, is there any way of disabling the bad syntax?

Note: I just verified that MariaDB presented the same bug until 10.4, but it seems it’s fixed in 10.5.

Answer

Not fixed in MySQL. It permits legal SQL syntax, but MySQL does not save the constraint when using the column-level foreign key syntax.

This was reported as a bug in 2005, and closed with a “won’t fix” message.

https://bugs.mysql.com/bug.php?id=13301

https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html says:

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. For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.

There is no way to disable the unsupported syntax, or even to make it return an error or a warning.