code.openark.org🔒 SSL Verified

Table of Contents

• ↓ Things that don’t work well with MySQL’s FOREIGN KEY implementation
• ↓ No binary log entries for cascaded writes
• ↓ Reducing my OSS involvement, and how it affects orchestrator & gh-ost
• ↓ The “What”
• ↓ When? For how long?
• ↓ The problem with MySQL foreign key constraints in Online Schema Changes
• ↓ Top Alternatives to openark.org

6-Month Traffic Trend Estimate

Official Contact & Profiles

Visual representation extracted from openark.org

Content Snippets from openark.org

"Foreign keys are an important database construct, that let you keep an aspect of data integrity within your relational model. Data integrity has many faces, most of which are application-specific. Foreign keys let you maintain an association integrity between rows in two tables, one being the parent, the other being the child. I’ve personally mostly avoided using foreign keys in MySQL for many years in my professional work, for several reasons. I’d like to now highlight things I find to be wrong/broken with the MySQL implementation."

"But, first, as in the past this caused some confusion: when I say I’m not using foreign keys, that does not mean I don’t JOIN tables. I still JOIN tables. I still have some id column in one table and some parent_id in another. I still use the benefit of the relational model. In a sense, I do use foreign keys. What I don’t normally is the foreign key CONSTRAINT, i.e. the declaration of a CONSTRAINT some_fk FOREIGN KEY ... in a table’s definition."

"So here are things I consider to be broken, either specific to the MySQL implementation, or in the general concept. Some are outright deal breakers for environments I’ve worked with. Others are things to work around. In no particular order:"

"I think there are many people unaware of this. In a way, MySQL doesn’t really support foreign keys. The InnoDB engine does. This is old history, from before InnoDB was even officially a MySQL technology, and was developed independently as a 3rd party product. There was a time when MySQL sought alternative engines. There was a time when there was a plan to implement foreign keys in MySQL, above the storage engine level. But as history goes, MySQL and InnoDB both became one with Oracle acquiring both, and I’m only guessing implementing foreign keys in MySQL became lower priority, to be eventually abandoned."

Key Topics & Focus Areas for openark.org:

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

No binary log entries for cascaded writes

Reducing my OSS involvement, and how it affects orchestrator & gh-ost

The “What”

When? For how long?

The problem with MySQL foreign key constraints in Online Schema Changes