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

Re:Things that don’t work well with MySQL’s FOREIGN KEY impl... by frazerclement on Monday May 01, 2023 @07:42PM (#35780907)

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?

[ Parent | on HN ]

Re:Things that don’t work well with MySQL’s FOREIGN KEY impl... by throwusawayus on Monday May 01, 2023 @08:43PM (#35781536)

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!

[ Parent | on HN ]

Re:Things that don’t work well with MySQL’s FOREIGN KEY impl... by frazerclement on Tuesday May 02, 2023 @04:49AM (#35784909)

You are right about cross schema foreign keys being supported, my mistake.

[ Parent | on HN ]