{"id":8226,"date":"2023-05-01T09:49:20","date_gmt":"2023-05-01T07:49:20","guid":{"rendered":"https:\/\/code.openark.org\/blog\/?p=8226"},"modified":"2023-05-02T06:01:54","modified_gmt":"2023-05-02T04:01:54","slug":"things-that-dont-work-well-with-mysqls-foreign-key-implementation","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/things-that-dont-work-well-with-mysqls-foreign-key-implementation","title":{"rendered":"Things that don’t work well with MySQL’s FOREIGN KEY implementation"},"content":{"rendered":"

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<\/em>, the other being the\u00a0child<\/em>. 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.<\/p>\n

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<\/code> tables. I still JOIN<\/code> tables. I still have some id<\/code> column in one table and some parent_id<\/code> 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<\/code>, i.e. the declaration of a CONSTRAINT some_fk FOREIGN KEY ...<\/code> in a table’s definition.<\/p>\n

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:<\/p>\n

No binary log entries for cascaded writes<\/h2>\n

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.<\/p>\n

Alas, the fact foreign keys are implemented in the storage engine level has dire consequences. The engine does not have direct access to the binary log. If you create a foreign key constraint with ON DELETE|UPDATE<\/code> of SET NULL<\/code> or CASCADE<\/code>, you should be aware that cascaded operations are never written to the binary log. Consider these two tables:<\/p>\n

CREATE TABLE `parent_table` (\n  `id` int NOT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB;\n\nCREATE TABLE `child_table` (\n  `id` int NOT NULL,\n  `parent_id` int DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `parent_id_idx` (`parent_id`),\n  CONSTRAINT `child_parent_fk` FOREIGN KEY (`parent_id`) REFERENCES `parent_table` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB;\n\ninsert into parent_table values (1);\ninsert into child_table values (1, 1);\ninsert into child_table values (2, 1);\n<\/pre>\n
If you were to DELETE FROM parent_table WHERE id=1<\/code>, then the two rows in child_table<\/code> are also deleted, due to the CASCADE<\/code> rule. However, only the parent_table<\/code> deleted row is written in the binary log. The two child_table<\/code> rows are deleted internally by the InnoDB engine. The assumption is that when a replica applies the DELETE<\/code> on parent_table<\/code>\u00a0the replica’s own InnoDB engine will likewise delete the two relevant child_table<\/code> rows.<\/p>\n
Fair assumption. But we lose information along the way. As Change Data Captures are becoming more and more common, and as we stream changes from MySQL to other data stores, the DELETE<\/code>s on child_table<\/code> are never reflected and cannot be captured.<\/p>\n
Online DDL, aka online schema changes<\/h2>\n
I’ve written about this at length<\/a> in the past. But even that write up is incomplete!<\/p>\n
MySQL is pushing towards INSTANT<\/code> DDL, which is a wonderful thing. With 8.0.29<\/code>, even more schema change operations are supported by ALGORITHM=INSTANT<\/code>. But, there’s still quite a lot of operations unsupported yet, and until such time that INSTANT<\/code> DDL supports all (or at least all common) schema changes, Online Schema Change tools like gh-ost<\/code>, pt-online-schema-change<\/code>, and Vitess<\/code> (disclaimer: I’m a Vitess<\/code> maintainer and actively developing Vitess'<\/code>s Online DDL), are essential when it comes to production changes.<\/p>\n
Both Vitess<\/code> and gh-ost<\/code> tail the binary logs to capture changes to the table. In light of the previous section, it is impossible to run such an Online Schema Change operation on a foreign key child table that has either SET NULL<\/code> or CASCADE<\/code> rule. The changes to the table are never reflected in the binary log. pt-online-schema-change<\/code> is also unable to detect those changes as there’s nothing to invoke the triggers.<\/p>\n
Then, please do go ahead and read The problem with MySQL foreign key constraints in Online Schema Changes<\/a>, as it goes deep into what it otherwise means to deal with FK constraints in Online DDL, as it cannot fit in this post.<\/p>\n
Locked data types<\/h2>\n
In the above table definitions, id<\/code> and parent_id<\/code> are int<\/code>. As data grows, I might realize the choice of data type was wrong. I really should have used bigint unsigned<\/code>.<\/p>\n
Alas, it is impossible<\/em> to change the data type in either parent_table<\/code> or child_table<\/code>:<\/p>\n
> alter table parent_table modify column id bigint unsigned;\nERROR 3780 (HY000): Referencing column 'parent_id' and referenced column 'id' in foreign key constraint 'child_parent_fk' are incompatible.\n\n> alter table child_table modify column parent_id bigint unsigned;\nERROR 3780 (HY000): Referencing column 'parent_id' and referenced column 'id' in foreign key constraint 'child_parent_fk' are incompatible.\n<\/pre>\n
It’s impossible to do that with straight-DDL (never mind INSTANT<\/code>), and it’s impossible to do that with Online DDL. InnoDB (not MySQL) flatly refuses to accept any change in the related columns’s data type. Well, it’s not really about\u00a0changing<\/i>\u00a0them as it is about having an\u00a0incompatibility<\/i>. But then, we can’t change either. The column type changes are only made possible if we modify the child table to remove the foreign key constraint, then alter both parent and child to modify the respective columns types, then re-add the foreign key constraint. There are four different ALTER TABLE<\/code> statements. Neither removing nor adding a foreign key constraint is supported in INSTANT<\/code> algorithm, so you can expect a long time in which the foreign key relationship simply does not exist!<\/p>\n
CREATE TABLE … LIKE<\/h2>\n
One of those quirks that come with InnoDB owning the foreign key definition, is that CREATE TABLE ... LIKE<\/code> does not generate foreign keys. I think this is mostly an oversight. A SHOW CREATE TABLE<\/code> statement does produce foreign key output, so I’m not sure why CREATE TABLE ... LIKE<\/code> doesn’t. Continuing our above child_table<\/code> example:<\/p>\n
> create table child_table_copy like child_table;\nQuery OK, 0 rows affected (0.06 sec)\n\n> show create table child_table_copy \\G\n*************************** 1. row ***************************\n       Table: child_table_copy\nCreate Table: CREATE TABLE `child_table_copy` (\n  `id` int NOT NULL,\n  `parent_id` int DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `parent_id_idx` (`parent_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci\n<\/pre>\n
Unique constraint names<\/h2>\n
I know this is ANSI SQL, and so I won’t fault MySQL for this. I do think this is one of those scenarios where deviating from ANSI SQL would be beneficial. A foreign key constraint has a name (if you don’t provide one, one is auto-generated for you). And, that name, according to ANSI SQL, has to be unique across your schema. It means the following table conflicts with our original child_table<\/code>:<\/p>\n
CREATE TABLE `another_child_table` (\n  `id` int NOT NULL,\n  `parent_id` int DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `parent_id_idx` (`parent_id`),\n  CONSTRAINT `child_parent_fk` FOREIGN KEY (`parent_id`) REFERENCES `parent_table` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB;\n<\/pre>\n
You can’t have two foreign key constraints both named child_parent_fk<\/code>.<\/p>\n
I never understood that limitation. See, it’s just fine the tables both have a key named parent_id_idx<\/code>. No conflict about that. Why do foreign keys have to have unique names?<\/p>\n
Maybe, in ANSI SQL, foreign keys can be independent constructs, living outside the table scope. Meh, even so this could be technically solved using some sort of namespace. But, in MySQL this isn’t the case in the first place. Foreign keys are part of the table definition.<\/p>\n
This is again just painful for Online DDL, or for any automation that tries to duplicate tables on the fly.<\/p>\n
Lack of declarative-only definitions<\/h2>\n
This is more of a “I wish this existed” rather than “this is wrong”. One of the greatest benefits of foreign keys is the graph<\/em>. Given a schema with foreign keys, you can formally analyze the relationships between tables. You can draw the dependency graph. It’s really educating.<\/p>\n
What I wish for is to have a declarative-only foreign key definition. One that does not actually\u00a0enforce<\/em> anything. Merely indicates an association. Something like so:<\/p>\n
CREATE TABLE `child_table` (\n  `id` int NOT NULL,\n  `parent_id` int DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `parent_id_idx` (`parent_id`),\n  DECLARATIVE FOREIGN KEY (`parent_id`) REFERENCES `parent_table` (`id`)\n)\n<\/pre>\n
The declarative foreign key could still enforce the existence of the parent table and referenced column, definition-wise, but do nothing at all to enforce relationship of data.<\/p>\n
Anyway, just a wish.<\/p>\n
SET FOREIGN_KEY_CHECKS<\/h2>\n
I love that we have set foreign_key_checks<\/code>. But it’s a bit inconsistent. Basically, set foreign_key_checks=0<\/code> lets you override foreign key constraints. You can do any of the following:<\/p>\n