Things that don’t work well with MySQL’s FOREIGN KEY implementation

[wswope]

Just to throw additional warnings on the pile, MySQL FKs are not ANSI SQL compliant and can be set up on non-unique columns, which can be a major pain when porting across DBs.

https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-ke...

[zerocrates]

Whoa, I've never made a foreign key on a non-unique column, had no idea MySQL would allow that. I guess I've pretty much always done foreign keys pointing to primary keys, so they're definitely unique.

[pphysch]

> A FOREIGN KEY constraint that references a non-UNIQUE key is not standard SQL but rather an InnoDB extension.

Anyone have any background on why this exists? For what purpose would you want a non-unique FK; what are the semantics of a FK that resolves to multiple different records?

[Dachande663]

I am probably in the minority and would get shouted at by a DBA of yore, but I use foreign keys for referential integrity and… that’s it. We use soft deletes for almost all rows, so the whole cascading side is less relevant for us than getting an error in test or even production because a missing relation has been used. Combined with using non-integer primary keys, it’s godsend.

[Svip]

I remember working on a large database without foreign keys for a while. When asked why did not use foreign keys, I was told that they didn't like the opaqueness of CASCADE, which I could understand. I guess I did not give it much thought afterwards, until I later ended up in a shop, where the database did include foreign keys, but only with RESTRICT. It was eye-opening how useful foreign keys were, when they were just integrity checks.

[gigatexal]

I was a DBA for 5 years and we outlawed cascading anything be they updates or deletes. So you’re not doing anything wrong.

[Phelinofist]

What's wrong with non-integer primary keys?

[ksec]

I will take another rare opportunity of anything MySQL ends up being on HN, Considering [1] mySQL v5.x will EOL this year. And MySQL 8.0 with EOL in 2026. Does anyone knows if MySQL 9.0 will come anytime soon?

[1] https://endoflife.software/applications/databases/mysql

[throwusawayus]

only oracle knows. and they don’t share answer yet, on this site or any other

only public news so far is extremely brief twitter mention of future switch to separate LTS releases from feature releases

big picture, hard to see what would motivate them to major re-invest in current mysql product model! amazon, planetscale, and co all profit off of oracle’s mysql server development efforts. and oracle does not get anything in return

assume this why more and more mysql dev efforts go to saas-only product like “mysql heatwave”!

[frazerclement]

Nice article.

Note that there's more than one 'MySQL FOREIGN KEY' implementation. MySQL Ndb Cluster also supports foreign keys with some differences wrt the InnoDB implementation :

- NDB, therefore not limited to a single MySQL Server, shard etc - Not limited to references between tables in a single database - Supports NoAction deferred constraint checks - Cascaded changes Binlogged independently as part of RBR (Nice side effect of reducing replica apply time work) ...

https://dev.mysql.com/blog-archive/foreign-keys-in-mysql-clu...

Some of the issues described wrt DDL limitations are shared.

Many schemas seem to overuse foreign keys perhaps under the assumption that they are required for or accelerate joins?

[throwusawayus]

innodb supports cross-schema foreign keys, there’s no limitation to tables in a single database

can be terrible in mysql 8 though due to metadata locks now extending across foreign key boundaries. this means alter in one schema can block things in other schema if foreign key across databases

speaking of, am surprised that blog post author doesnt discuss the new mysql 8 metadata locking behavior, is new major problem with mysql foreign keys!

[ComputerGuru]

The last time I checked, MySQL still can’t do foreign keys with binary blobs. (I think it works with some very specific limitations but not enough to actually use in the real world.)

[AugustoCAS]

I have the strong feeling I would never do this regardless of the DB engine for performance and storage reasons. I would try to hash the blob into a bigint and use that as the PK/FK.

Out of curiosity, is there a scenario you can share in which using a binary blob as a PK/FK would be the best solution?

[pjungwir]

It was interesting to hear this aside about the MySQL roadmap:

> MySQL is pushing towards INSTANT DDL

When I need MySQL these days I automatically go for MariaDB instead, but I guess they are going to diverge more and more. Does anyone more involved in the MySQL/MariaDB world have any thoughts about how they choose and the future of those two projects?