Last Updated on May 14, 2020

Foreign keys are a controversial topic. Some developers love them, because they make the database more robust. Some DBAs forbid them, because they propagate locks, increase the number of reads, and pollute the buffer pool.

This article doesn’t discuss the foreign key problem globally. It is about the MySQL and MariaDB implementations: more specifically, it is a list of the most important bugs and limitations that involve foreign keys.

For each bug you can see its year, and the current state – in many cases, we can probably assume that the bug will never be fixed, and consider those bugs as permanent limitations.

I didn’t include bugs fixed in 8.0. Some of them were still “verified”, which probably means that they’re not fixed in old versions. I added comments to ask Oracle if those bugs are fixed, but Oracle didn’t care to answer or change the bug status until now.

Other considerations, or maybe a quick foreign key tutorial, may follow in other posts.

Contribute

I will try periodically review the list to keep it up to date. I don’t want it to get obsolete.

Please use the comments to inform me about bugs that are important for you. I may include them in the list. As usually, you are very welcome to report my mistakes, outdated information and state your opinions.

A personal note… this it the first time publish a post of this type. I confess that I’m worried about the judgement of a famous MySQL entomologist who does this kind of stuff much better than I. If you’re into bugs, follow his blog.

Bugs

Data inconsistencies

I’ve chosen to put this category of bugs first, because having foreign keys that generate data inconsistencies defies their purpose. This includes inconsistencies in replication, foreign keys not working, foreign keys preventing other features from working, or missing errors / wrong error handling (without a proper errors the application cannot handle anomalies).

• #4919 – Bad Foreign Key Definition does not work nor does it produce an error (verified, 2004)
• #6254 – IGNORE: should allow transactional tables to do partial transactions (verified, 2004)
• #11232 – Return a warning if creating a FOREIGN KEY on storage engines w/out support (verified, 2005)
• #11472 – Triggers not executed following foreign key updates/deletes (verified, 2005; now this is also documented, in the unrealistic hope that all users read the documentation in great detail before using any feature)
  • As Marko Makela explained in our #DB2w interview and in our meetup in London, this is because foreign keys are implemented in InnoDB. When MariaDB moves them to the SQL layer this bug should be fixed.
• #13977 – InnoDB foreign keys with multiple parent rows act incorrectly (open, 2005)Explanation: When an InnoDB foreign key reference has several parent rows, unexpected behaviour occurs: attempts to delete one of many parent rows in a RESTRICT relationship fail, even when the relationship would still be satisfied. This is documented.
  Personal note: While the documentation generally determines the correct behavior, there should always be a logic behind it. An illogical documented behaviour is, in my opinion, a documented bug.
• #24668 – CASCADING FOREIGN KEYS do not work on same TABLE (verified, 2010, formally a feature request)
• #34932 – CREATE TABLE xyz like zyx (verified 2008, but probably informally considered “won’t fix”). The problem is that CREATE TABLE LIKE doesn’t copy foreign keys, and the motivation for not fixing this is “so no special code is needed for this”. Not creating a foreign key without any warning will probably lead to data corruption.
• #35358 – updating an incorrect foreign key to the same value does not raise error (verified, 2008)
• #44207 – LEFT JOIN Multi-table DELETE fails to delete records when using foreign keys (verified, 2009)

Crashes and corruption

This class of problem is slightly less important, because there is not a risk of a permanent or impossible to fix inconsistency. However, these bugs cause outages when you hit them.

• #80821 – Replication breaks if multi-table DELETE is used in conjunction with Foreign Key (verified, 2016)
• 85605 – Corrupted index for ON DELETE CASCADE with self-referential ON DELETE SET NULL (verified, 2017)
• 86774 – DELETE CASCADE on record with another DELETE SET NULL results in corrrupt tables (verified, 2017)
• #98719 – Parallel CREATE TABLE statement binlogged in a wrong order. MySQL 5.6 and 5.7 could binlog CREATE TABLEs in the wrong order, which leads to replication outage if table1 has a foreign key referencing table2.
• #97836 – In group replication foreign key will break slave aplier

Wrong behavior

• #10992 – MyISAM foreign key definitions create indexes (verified, 2005)
• #38585 – Foreign key constraint for column in same table fails on delete when same row (verified, 2008, but also a documented limitation)
• #39495 – Incorrect result of “show create table” with constraints (verified, 2008)
• #45290 – Cant convert innodb tables from latin1 to utf8 if there is a varchar foreign key (verified, 2009)
• #97501 – Combination of “truncate table parent” and “SET foreign_key_checks = 0”. It’s currently unclear from the comments if the bug is in InnoDB or in the documentation.

Private bugs

Oracle makes many bugs private short after they’re reported. In that case, no details about the bug are available. While in some cases some non-Oracle resources post a link to the bug with its title, it is impossible to see the test cases, comments, and the bug status. Though it is possible to find references to private bugs related to foreign keys, it would be useless to post them here.

Limitations

Foreign key limitations are documented. But the page dedicated to limitations seems to assume that we already know some of them from other documentation pages. I’ll try to maintain a more complete list, unless this particular section of the great documentation is fixed.

• Foreign keys are implemented at storage engine level. To my knowledge, only InnoDB supports foreign keys.
• Even if other storage engines will support foreign keys at some point, it will not be possible to “link” tables that use different storage engines. We saw this in the past, with the PBXT storage engine, now discontinued.
• SET DEFAULT is treated by InnoDB as an alias for RESTRICT (#3558, MDEV-10393).
  • Historical curiosity: it worked as expected in PBXT. This wasn’t documented: maybe the authors never noticed that it didn’t work in InnoDB?
• The MATCH clause has no effect.
• Integrity constraints, including foreign keys, are always checked row by row in MySQL. Other DBMSs validate constraints at the end of a statement by default, and the user can defer them to the end of the current transaction.
• Errors generated when trying to create a foreign key in a wrong way are obscure and frustrating before MySQL 8 and in MariaDB.
• MySQL and MariaDB allow to create identical foreign keys with different names. I saw duplicate foreign keys in some production databases – so draw your own conclusions about the correctness of this behavior, but in any case, be careful when you create foreign keys.
• Partitioned tables cannot have foreign keys, and cannot be referenced by foreign keys. Plans to fix this in MariaDB: MDEV-12483…
  • …or at least to allow foreign keys in the current partition of a system-versioned table: MDEV-19191.
• There is no way to create a foreign key without a matching index. This is however possible in Oracle, and possibly other DBMSs. In some cases this has relevant performance benefits. I reported a feature request to MariaDB, that is currently assigned: MDEV-20777.
• When MySQL or MariaDB implement some cool new feature that you’d like to use, don’t assume that it can be used in conjunction with foreign keys. Test extensively and report bugs, so other users will know about those problems.
• From the docs:

If there are several rows in the parent table that have the same referenced key value, InnoDB acts in foreign key checks as if the other parent rows with the same key value do not exist. For example, if you have defined a RESTRICT type constraint, and there is a child row with several parent rows, InnoDB does not permit the deletion of any of those parent rows.

InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints.

• From the docs:

If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the same cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.

• From the docs:

In an SQL statement that inserts, deletes, or updates many rows, foreign key constraints (like unique constraints) are checked row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records that it must examine. MySQL checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. This means that it is not possible to delete a row that refers to itself using a foreign key.

Kudos

You may contribute this page by writing comments in this page. But some people may prefer to use social networks or emails, so I feel the necessity to thank them here.

• Georgi D. Sotirov reminded me on Twitter that partitioned tables do not support foreign keys.