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.