Good old references constrains. They work like a charm when defined at the table level.

create table foo (id int primary key);

create table bar (id int, foreign key(id) references foo(id));
insert into bar values (1);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (...)

But if you come from another ecosystem and are used to occasionally define foreign key constrains at the column level, this is what happens:

create table baz (id int references foo(id));
insert into baz values (1); -- happily takes a value that isn't there in foo
select id from baz;
-- 1

What happens is that the references has been recognized, but ignored.

It turns out that this is not a bug. The MySQL documentation says they do it, and that's all you need to know:

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 MariaDB documentation is slightly more verbose on their rationale:

MariaDB accepts the REFERENCES clause in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS's. However, only the syntax described below creates foreign keys.

Now what could be the use for this "feature" that helps "compatibility" with other DBMS — and the standard — by silently breaking the very purpose of the reference, while at the same time, correctly implementing it does not look like a big effort since foreign key constrains are indeed enforced when declared at the table level? And don't tell me this cannot be fixed because people rely on the fact that foreign constrains can be broken when declared at the column level.

Please help me make sense out of this.

EDIT: I just realized that by "compatibility with other DBMS", the MariaDB documentation may actually be referring to MySQL. This could either be a good motive for MariaDB to stick to the (unmotivated) behavior of MySQL, or a missed opportunity to improve their fork.

  • Each time you create a table you must check its final DDL looking to its SHOW CREATE TABLE. This is the only way to ensure that you have created the structure which you want to create. fiddle.
    – Akina
    Apr 1 '20 at 21:59
  • 1
    It has been submitted as a bug a long time ago: bugs.mysql.com/bug.php?id=17943 , but I cannot find a concrete justification for rejecting/not fixing it
    – dbilid
    Apr 1 '20 at 22:02
  • Both MySQL and MariaDB accept code contributions. Since it doesn't look like a bit effort, maybe you want to have a go at it? Seems like a small payback for a huge amount of free software provided in all the other features you like and use.
    – danblack
    Apr 1 '20 at 22:07
  • @danblack This is not a bug -- shoudn't the specification change first before submitting a patch?
    – user209974
    Apr 2 '20 at 6:21
  • Documentation is a description of what is implemented and can be changed. Its not a specification. "Bug" database include feature requests and you're welcome to treat it as however makes you feel better. Please stop wining about why your desired feature isn't implemented and just write it!
    – danblack
    Apr 2 '20 at 6:32

By design MySQL has been less SQL standard compliant than other open source database (i.e PostgreSQL): I think this was an intented design choice in both cases and I think that for MySQL the road is still long even with all Oracle Corp. and MariaDB achievements in the last decade.

  • I also found an interesting comment here: "MySQL does not give a syntax error, because it was the intention in the 1990's that table definitions containing FOREIGN KEY constraints should be importable to MySQL, even though MySQL at that time did not support foreign keys."
    – user209974
    Apr 2 '20 at 7:07
  • While Postgres users, and even SQLite developers, refer to the Postgres as the gold standard of standards compliance, they too explicitly choose to disregard the spec in many aspects for good reasons, in the same way MySQL also made choices. PG also contains many features not implemented yet too.
    – danblack
    Apr 2 '20 at 7:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.