from Hacker News

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

by grep_it on 5/1/23, 12:43 PM with 35 comments

• by wswope on 5/1/23, 4:54 PM

  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...

• by Dachande663 on 5/1/23, 3:36 PM

  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.

• by ksec on 5/1/23, 3:59 PM

  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

• by frazerclement on 5/1/23, 11:42 PM

  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?

• by ComputerGuru on 5/1/23, 3:48 PM

  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.)

• by pjungwir on 5/1/23, 5:24 PM

  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?