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)
...
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!
Comments
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?
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!
You are right about cross schema foreign keys being supported, my mistake.