{"id":3463,"date":"2025-04-24T08:30:00","date_gmt":"2025-04-24T08:30:00","guid":{"rendered":"https:\/\/kedar.nitty-witty.com\/blog\/?p=3463"},"modified":"2025-04-24T08:21:43","modified_gmt":"2025-04-24T08:21:43","slug":"a-unique-foreign-key-issue-in-mysql-8-4","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4","title":{"rendered":"A Unique Foreign Key issue in MySQL 8.4"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Recently, a friend reached out to me for help after encountering an error while restoring a MySQL dump during an upgrade from MySQL 8.0 to 8.4. The error read:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ERROR 6125 (HY000) at line 75: Failed to add the foreign key constraint. Missing unique key for constraint 'orders_ibfk_1' in the referenced table 'clients'.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">At first glance, message is straightforward \u2014 the foreign key was pointing to a column without a unique constraint. <strong><em>BUT I&#8217;d not write a blog if that&#8217;s the only thing!<\/em><\/strong> In this post we analyze the working, the work around, the fixtures and meet a bug.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@mysql84 ~]# mysql -e \u201ccreate database production_database;\"\n&#91;root@mysql84 ~]# mysql production_database &lt; production_database.sql\nERROR 6125 (HY000) at line 75: Failed to add the foreign key constraint. Missing unique key for constraint 'orders_ibfk_1' in the referenced table 'clients'\n&#91;root@mysql84 ~]#<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The error clearly states that the SQL failed to add Foreign Key as the referenced table had no unique key on the targetted column. This happened while importing a MySQL dump from version 8.0 into MySQL 8.4, the process failed with an error:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><br><strong>ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint\u2026<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This happens because, starting with MySQL 8.4, the server enforces stricter foreign key rules \u2014 foreign keys must reference columns that are part of a unique or primary key constraint. This is a significant change from previous behavior, and it&#8217;s enforced by default using the new system variable restrict_fk_on_non_standard_key.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>MySQL 8.4 Documentation<\/strong> reads this&#8230;<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Nonstandard foreign keys. The use of non-unique or partial keys as foreign keys is nonstandard, and is deprecated in MySQL. Beginning with MySQL 8.4.0, you must explicitly enable such keys by setting restrict_fk_on_non_standard_key to OFF, or by starting the server with &#8211;skip-restrict-fk-on-non-standard-key.<\/li>\n\n\n\n<li>restrict_fk_on_non_standard_key is ON by default, which means that trying to use a nonstandard key as a foreign key in a CREATE TABLE or other SQL statement is rejected with ER_WARN_DEPRECATED_NON_STANDARD_KEY. Setting it to ON allows such statements to run, but they raise the same error as a warning.<\/li>\n\n\n\n<li>Upgrades from MySQL 8.0 are supported even if there are tables containing foreign keys referring to non-unique or partial keys. In such cases, the server writes a list of warning messages containing the names of any foreign keys which refer to nonstandard keys.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">He wasn&#8217;t sure of if he can make the unique index on the system and he was in &#8220;hurry&#8221;. So I suggested the workaround to skip the foreign key issue temporarily:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> set global <strong>restrict_fk_on_non_standard_key<\/strong>=OFF;\nQuery OK, 0 rows affected, 1 warning (0.00 sec)\n\nmysql> show warnings;\n+\u2014\u2014\u2014+\u2014\u2014+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\n| Level | Code | Message |\n+\u2014\u2014\u2014+\u2014\u2014+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\n| Warning | 4166 | \u2018restrict_fk_on_non_standard_key\u2019 is deprecated and will be removed in a future release. Foreign key referring to non-unique or partial keys is unsafe and may break replication. |\n+\u2014\u2014\u2014+\u2014\u2014+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\n1 row in set (0.00 sec)\n\nmysql> exit\nBye\n&#91;root@mysql84 ~]# mysql upgradetest &lt; upgradetest.sql\n&#91;root@mysql84 ~]# echo $?\n0\n&#91;root@mysql84 ~]#<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Note that this variable is deprecated and it is strongly recommended to follow the standard foreign keys to refer columns which are defined as unique.<br>I suggested my friend to follow proper upgrade approach and use check-for-server-upgrade utility before doing upgrades. Anyhow, this could have ended here but then there&#8217;s something called &#8220;let me see more&#8221;!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>If you&#8217;re as curious, I&#8217;d ask you to run this along with me.<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The &#8220;Unique&#8221; Foreign Key Issue<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">I decided to run some test around this to understand what&#8217;s going on in MySQL 8.4.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Following 4 tables were created in MySQL 8.0:\n\n\nDROP TABLE IF EXISTS clients;\nCREATE TABLE clients (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    client_id INT NOT NULL,\n    name VARCHAR(100),\n<strong>    UNIQUE KEY (client_id),<\/strong>\n<strong>    INDEX idx_client_id (client_id)<\/strong>\n);\n\nDROP TABLE IF EXISTS orders;\nCREATE TABLE orders (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    client_id INT,\n    order_details VARCHAR(255),\n    FOREIGN KEY (client_id) REFERENCES clients(client_id)\n);\n\nDROP TABLE IF EXISTS orders_2;\nDROP TABLE IF EXISTS clients_2;\nCREATE TABLE clients_2 (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    client_id INT NOT NULL,\n    name VARCHAR(100),\n    INDEX idx_client_id (client_id)\n);\n\nCREATE TABLE orders_2 (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    client_id INT,\n    order_details VARCHAR(255),\n    FOREIGN KEY (client_id) REFERENCES clients_2(client_id)\n);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">To ensure compatibility before upgrading to MySQL 8.4, I ran the check-for-server-upgrade utility using MySQL Shell 8.4:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@centos_1 ~]# mysqlsh -- util check-for-server-upgrade root@localhost\n\n...\n\n4) Checks for foreign keys not referencing a full unique index\n(foreignKeyReferences)\n   Foreign keys to partial indexes may be forbidden as of 8.4.0, this check\n   identifies such cases to warn the user.\n\n<strong>   test.orders_ibfk_1 - invalid foreign key defined as 'orders(client_id)'<\/strong>\n      references a non unique key at table 'clients'.\n   test.orders_2_ibfk_1 - invalid foreign key defined as 'orders_2(client_id)'\n      references a non unique key at table 'clients_2'.\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This tool flagged invalid foreign key definitions \u2014 specifically, those referencing non-unique keys, which are now disallowed by default in MySQL 8.4. These checks help identify upgrade-breaking schema issues such as foreign keys pointing to non-unique or partial indexes.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to identify Foreign Key compatibility?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The beauty of opensource allows you to explore the code under the hood and look what I found: <a href=\"https:\/\/github.com\/mysql\/mysql-shell\/blob\/8.4.4\/modules\/util\/upgrade_checker\/upgrade_check_creators.cc#L1581\">https:\/\/github.com\/mysql\/mysql-shell\/blob\/8.4.4\/modules\/util\/upgrade_checker\/upgrade_check_creators.cc#L1581<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This query there fetches the required information to provide us with the FK \/ UK relation. That said, I saw a problem in the query, nothing critical but surely wrong.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Did you note that the query wrongly identifies error about client_id not having unique key for clients table?! The clients table&#8217;s client_id does have a UNIQUE KEY yet it is reported. Why? It is not a critical issue but it is a bug.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Executing above query manually returns two records:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select\n  fk.constraint_schema,\n  fk.constraint_name,\n  '',\n  fk.parent_fk_definition as fk_definition,\n  fk.REFERENCED_TABLE_NAME as target_table,\n  '##fkToNonUniqueKey'\nfrom (select\n      rc.constraint_schema,\n      rc.constraint_name,\n      CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name order by kc.ORDINAL_POSITION),')') as parent_fk_definition,\n      CONCAT(kc.REFERENCED_TABLE_SCHEMA,'.',kc.REFERENCED_TABLE_NAME, '(', GROUP_CONCAT(kc.REFERENCED_COLUMN_NAME order by kc.POSITION_IN_UNIQUE_CONSTRAINT),')') as target_fk_definition,\n      rc.REFERENCED_TABLE_NAME\n    from\n      information_schema.REFERENTIAL_CONSTRAINTS rc\n        join\n          information_schema.KEY_COLUMN_USAGE kc\n        on\n          rc.constraint_schema = kc.constraint_schema AND\n          rc.constraint_name = kc.constraint_name AND\n          rc.constraint_schema = kc.REFERENCED_TABLE_SCHEMA AND\n          rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND\n          kc.REFERENCED_TABLE_NAME is not NULL AND\n          kc.REFERENCED_COLUMN_NAME is not NULL\n    where\n      true\n    group by\n      rc.constraint_schema,\n      rc.constraint_name,\n      rc.table_name,\n      rc.REFERENCED_TABLE_NAME) fk\n  join (SELECT\n      CONCAT(table_schema,'.',table_name,'(',GROUP_CONCAT(column_name order by seq_in_index),')') as fk_definition,\n      SUM(non_unique) as non_unique_count\n    FROM\n      INFORMATION_SCHEMA.STATISTICS\n    WHERE\n      sub_part IS NULL\n    GROUP BY\n      table_schema, table_name, index_name) idx\n    on\n      fk.target_fk_definition = idx.fk_definition AND\n      idx.non_unique_count > 0\n+-------------------+-----------------+--+---------------------+--------------+--------------------+\n| constraint_schema | constraint_name |  | fk_definition       | target_table | ##fkToNonUniqueKey |\n+-------------------+-----------------+--+---------------------+--------------+--------------------+\n| test              | orders_ibfk_1   |  | orders(client_id)   | clients      | ##fkToNonUniqueKey |\n| test              | orders_2_ibfk_1 |  | orders_2(client_id) | clients_2    | ##fkToNonUniqueKey |\n+-------------------+-----------------+--+---------------------+--------------+--------------------+<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Fixing the query<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">We can rewrite this query and correctly report:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n  fk.constraint_schema,\n  fk.constraint_name,\n  '',\n  fk.parent_fk_definition AS fk_definition,\n  fk.REFERENCED_TABLE_NAME AS target_table,\n  '##fkToNonUniqueKey'\nFROM (\n  SELECT\n    rc.constraint_schema,\n    rc.constraint_name,\n    CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name ORDER BY kc.ORDINAL_POSITION), ')') AS parent_fk_definition,\n    CONCAT(kc.REFERENCED_TABLE_SCHEMA, '.', kc.REFERENCED_TABLE_NAME, '(', GROUP_CONCAT(kc.REFERENCED_COLUMN_NAME ORDER BY kc.POSITION_IN_UNIQUE_CONSTRAINT), ')') AS target_fk_definition,\n    rc.REFERENCED_TABLE_NAME\n  FROM\n    information_schema.REFERENTIAL_CONSTRAINTS rc\n  JOIN\n    information_schema.KEY_COLUMN_USAGE kc\n  ON\n    rc.constraint_schema = kc.constraint_schema AND\n    rc.constraint_name = kc.constraint_name AND\n    rc.constraint_schema = kc.REFERENCED_TABLE_SCHEMA AND\n    rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND\n    kc.REFERENCED_TABLE_NAME IS NOT NULL AND\n    kc.REFERENCED_COLUMN_NAME IS NOT NULL\n  GROUP BY\n    rc.constraint_schema,\n    rc.constraint_name,\n    rc.table_name,\n    rc.REFERENCED_TABLE_NAME\n) fk\nLEFT JOIN (\n  SELECT\n    CONCAT(table_schema, '.', table_name, '(', GROUP_CONCAT(column_name ORDER BY seq_in_index), ')') AS fk_definition\n  FROM\n    INFORMATION_SCHEMA.STATISTICS\n  WHERE\n    sub_part IS NULL AND\n    non_unique = 0  -- Only consider unique indexes\n  GROUP BY\n    table_schema, table_name, index_name\n) unique_idx ON fk.target_fk_definition = unique_idx.fk_definition\nWHERE\n  unique_idx.fk_definition IS NULL;\n+-------------------+-----------------+--+---------------------+--------------+--------------------+\n| constraint_schema | constraint_name |  | fk_definition       | target_table | ##fkToNonUniqueKey |\n+-------------------+-----------------+--+---------------------+--------------+--------------------+\n| test              | orders_2_ibfk_1 |  | orders_2(client_id) | clients_2    | ##fkToNonUniqueKey |\n+-------------------+-----------------+--+---------------------+--------------+--------------------+<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">I have already reported this bug and it has been verified.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Bug Report: <a href=\"https:\/\/bugs.mysql.com\/bug.php?id=118023\">https:\/\/bugs.mysql.com\/bug.php?id=118023<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Hope this post clarified what &#8220;unique&#8221; about &#8220;foreign keys&#8221; in MySQL 8.4. What started as a simple foreign key error led to uncovering how MySQL 8.4 handles constraints more strictly than before. It&#8217;s a good reminder: every upgrade can change behavior, and knowing the details matters.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Quick fixes like disabling <code>restrict_fk_on_non_standard_key<\/code> might help short-term, but the real fix is adapting to MySQL\u2019s latest standards.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let me know your learning stories until I come back with another one. Happy Upgrading.<\/p>\n","protected":false},"excerpt":{"rendered":"Recently, a friend reached out to me for help after encountering an error while restoring a MySQL dump during an upgrade from MySQL 8.0 to 8.4. The error read: At&hellip;\n","protected":false},"author":1,"featured_media":3464,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,867],"tags":[1093,315,427,1094,1096,1090,1095,495,1091,1092],"class_list":["post-3463","post","type-post","status-publish","format-standard","has-post-thumbnail","category-mysql","category-mysql-upgrade","tag-er_warn_deprecated_non_standard_key","tag-foreign-key","tag-mysql","tag-mysql-8-0-to-8-4-compatibility","tag-mysql-8-0-to-8-4-upgrade","tag-mysql-8-4","tag-mysql-8-4-foreign-keys","tag-mysql-bug","tag-mysql-upgrade","tag-restrict_fk_on_non_standard_key"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 4.9.10 - aioseo.com -->\n\t<meta name=\"description\" content=\"MySQL 8.4 enforces stricter foreign key rules. This post breaks down ERROR 6125, the causes, workaround, and how to properly prepare schema before upgrade.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Kedar\"\/>\n\t<meta name=\"google-site-verification\" content=\"_Bu1h5r0PxIC0D2jBVdmYN45RTeG9ogcX85XLkbG1qA\" \/>\n\t<meta name=\"msvalidate.01\" content=\"116B62074CBCA1BA99B8CCE09CCF2FB8\" \/>\n\t<meta name=\"keywords\" content=\"er_warn_deprecated_non_standard_key,foreign key,mysql,mysql 8.0 to 8.4 compatibility,mysql 8.0 to 8.4 upgrade,mysql 8.4,mysql 8.4 foreign keys,mysql bug,mysql upgrade,restrict_fk_on_non_standard_key\" \/>\n\t<link rel=\"canonical\" href=\"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 4.9.10\" \/>\n\t\t<meta property=\"og:locale\" content=\"en_US\" \/>\n\t\t<meta property=\"og:site_name\" content=\"Change Is Inevitable | Kedar Vaijanapurkar&#039;s Blog for MySQL, technology and various subjects\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"A Unique Foreign Key issue in MySQL 8.4 | Change Is Inevitable\" \/>\n\t\t<meta property=\"og:description\" content=\"MySQL 8.4 enforces stricter foreign key rules. This post breaks down ERROR 6125, the causes, workaround, and how to properly prepare schema before upgrade.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2025-04-24T08:30:00+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2025-04-24T08:21:43+00:00\" \/>\n\t\t<meta name=\"twitter:card\" content=\"summary\" \/>\n\t\t<meta name=\"twitter:title\" content=\"A Unique Foreign Key issue in MySQL 8.4 | Change Is Inevitable\" \/>\n\t\t<meta name=\"twitter:description\" content=\"MySQL 8.4 enforces stricter foreign key rules. This post breaks down ERROR 6125, the causes, workaround, and how to properly prepare schema before upgrade.\" \/>\n\t\t<script type=\"application\/ld+json\" class=\"aioseo-schema\">\n\t\t\t{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4#article\",\"name\":\"A Unique Foreign Key issue in MySQL 8.4 | Change Is Inevitable\",\"headline\":\"A Unique Foreign Key issue in MySQL 8.4\",\"author\":{\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/author\\\/admin#author\"},\"publisher\":{\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/#person\"},\"image\":{\"@type\":\"ImageObject\",\"url\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/04\\\/mysql8.4-foreign-keys-png.avif\",\"width\":1200,\"height\":675,\"caption\":\"mysql8.4-foreign-keys\"},\"datePublished\":\"2025-04-24T08:30:00+00:00\",\"dateModified\":\"2025-04-24T08:21:43+00:00\",\"inLanguage\":\"en-US\",\"commentCount\":4,\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4#webpage\"},\"isPartOf\":{\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4#webpage\"},\"articleSection\":\"MySQL, MySQL Upgrade, ER_WARN_DEPRECATED_NON_STANDARD_KEY, foreign key, MySQL, MySQL 8.0 to 8.4 compatibility, MySQL 8.0 to 8.4 Upgrade, MySQL 8.4, MySQL 8.4 Foreign Keys, mysql bug, MySQL upgrade, restrict_fk_on_non_standard_key\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4#breadcrumblist\",\"itemListElement\":[{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog#listItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\",\"nextItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/category\\\/mysql#listItem\",\"name\":\"MySQL\"}},{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/category\\\/mysql#listItem\",\"position\":2,\"name\":\"MySQL\",\"item\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/category\\\/mysql\",\"nextItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/category\\\/mysql\\\/mysql-upgrade#listItem\",\"name\":\"MySQL Upgrade\"},\"previousItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog#listItem\",\"name\":\"Home\"}},{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/category\\\/mysql\\\/mysql-upgrade#listItem\",\"position\":3,\"name\":\"MySQL Upgrade\",\"item\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/category\\\/mysql\\\/mysql-upgrade\",\"nextItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4#listItem\",\"name\":\"A Unique Foreign Key issue in MySQL 8.4\"},\"previousItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/category\\\/mysql#listItem\",\"name\":\"MySQL\"}},{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4#listItem\",\"position\":4,\"name\":\"A Unique Foreign Key issue in MySQL 8.4\",\"previousItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/category\\\/mysql\\\/mysql-upgrade#listItem\",\"name\":\"MySQL Upgrade\"}}]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/#person\",\"name\":\"Kedar\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4#personImage\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/be1f7021485c325d92cc4c5d961accb9c1af72b1b11281a790f7f4f066ef10d3?s=96&d=mm&r=g\",\"width\":96,\"height\":96,\"caption\":\"Kedar\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/author\\\/admin#author\",\"url\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/author\\\/admin\",\"name\":\"Kedar\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4#authorImage\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/be1f7021485c325d92cc4c5d961accb9c1af72b1b11281a790f7f4f066ef10d3?s=96&d=mm&r=g\",\"width\":96,\"height\":96,\"caption\":\"Kedar\"}},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4#webpage\",\"url\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4\",\"name\":\"A Unique Foreign Key issue in MySQL 8.4 | Change Is Inevitable\",\"description\":\"MySQL 8.4 enforces stricter foreign key rules. This post breaks down ERROR 6125, the causes, workaround, and how to properly prepare schema before upgrade.\",\"inLanguage\":\"en-US\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/#website\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4#breadcrumblist\"},\"author\":{\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/author\\\/admin#author\"},\"creator\":{\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/author\\\/admin#author\"},\"image\":{\"@type\":\"ImageObject\",\"url\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/04\\\/mysql8.4-foreign-keys-png.avif\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4\\\/#mainImage\",\"width\":1200,\"height\":675,\"caption\":\"mysql8.4-foreign-keys\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/a-unique-foreign-key-issue-in-mysql-8-4#mainImage\"},\"datePublished\":\"2025-04-24T08:30:00+00:00\",\"dateModified\":\"2025-04-24T08:21:43+00:00\"},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/\",\"name\":\"..::CHANGE is INEVITABLE::..\",\"description\":\"Kedar Vaijanapurkar's Blog for MySQL, technology and various subjects\",\"inLanguage\":\"en-US\",\"publisher\":{\"@id\":\"https:\\\/\\\/kedar.nitty-witty.com\\\/blog\\\/#person\"}}]}\n\t\t<\/script>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"A Unique Foreign Key issue in MySQL 8.4 | Change Is Inevitable","description":"MySQL 8.4 enforces stricter foreign key rules. This post breaks down ERROR 6125, the causes, workaround, and how to properly prepare schema before upgrade.","canonical_url":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4","robots":"max-image-preview:large","keywords":"er_warn_deprecated_non_standard_key,foreign key,mysql,mysql 8.0 to 8.4 compatibility,mysql 8.0 to 8.4 upgrade,mysql 8.4,mysql 8.4 foreign keys,mysql bug,mysql upgrade,restrict_fk_on_non_standard_key","webmasterTools":{"google-site-verification":"_Bu1h5r0PxIC0D2jBVdmYN45RTeG9ogcX85XLkbG1qA","msvalidate.01":"116B62074CBCA1BA99B8CCE09CCF2FB8","miscellaneous":""},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4#article","name":"A Unique Foreign Key issue in MySQL 8.4 | Change Is Inevitable","headline":"A Unique Foreign Key issue in MySQL 8.4","author":{"@id":"https:\/\/kedar.nitty-witty.com\/blog\/author\/admin#author"},"publisher":{"@id":"https:\/\/kedar.nitty-witty.com\/blog\/#person"},"image":{"@type":"ImageObject","url":"https:\/\/kedar.nitty-witty.com\/blog\/wp-content\/uploads\/2025\/04\/mysql8.4-foreign-keys-png.avif","width":1200,"height":675,"caption":"mysql8.4-foreign-keys"},"datePublished":"2025-04-24T08:30:00+00:00","dateModified":"2025-04-24T08:21:43+00:00","inLanguage":"en-US","commentCount":4,"mainEntityOfPage":{"@id":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4#webpage"},"isPartOf":{"@id":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4#webpage"},"articleSection":"MySQL, MySQL Upgrade, ER_WARN_DEPRECATED_NON_STANDARD_KEY, foreign key, MySQL, MySQL 8.0 to 8.4 compatibility, MySQL 8.0 to 8.4 Upgrade, MySQL 8.4, MySQL 8.4 Foreign Keys, mysql bug, MySQL upgrade, restrict_fk_on_non_standard_key"},{"@type":"BreadcrumbList","@id":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4#breadcrumblist","itemListElement":[{"@type":"ListItem","@id":"https:\/\/kedar.nitty-witty.com\/blog#listItem","position":1,"name":"Home","item":"https:\/\/kedar.nitty-witty.com\/blog","nextItem":{"@type":"ListItem","@id":"https:\/\/kedar.nitty-witty.com\/blog\/category\/mysql#listItem","name":"MySQL"}},{"@type":"ListItem","@id":"https:\/\/kedar.nitty-witty.com\/blog\/category\/mysql#listItem","position":2,"name":"MySQL","item":"https:\/\/kedar.nitty-witty.com\/blog\/category\/mysql","nextItem":{"@type":"ListItem","@id":"https:\/\/kedar.nitty-witty.com\/blog\/category\/mysql\/mysql-upgrade#listItem","name":"MySQL Upgrade"},"previousItem":{"@type":"ListItem","@id":"https:\/\/kedar.nitty-witty.com\/blog#listItem","name":"Home"}},{"@type":"ListItem","@id":"https:\/\/kedar.nitty-witty.com\/blog\/category\/mysql\/mysql-upgrade#listItem","position":3,"name":"MySQL Upgrade","item":"https:\/\/kedar.nitty-witty.com\/blog\/category\/mysql\/mysql-upgrade","nextItem":{"@type":"ListItem","@id":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4#listItem","name":"A Unique Foreign Key issue in MySQL 8.4"},"previousItem":{"@type":"ListItem","@id":"https:\/\/kedar.nitty-witty.com\/blog\/category\/mysql#listItem","name":"MySQL"}},{"@type":"ListItem","@id":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4#listItem","position":4,"name":"A Unique Foreign Key issue in MySQL 8.4","previousItem":{"@type":"ListItem","@id":"https:\/\/kedar.nitty-witty.com\/blog\/category\/mysql\/mysql-upgrade#listItem","name":"MySQL Upgrade"}}]},{"@type":"Person","@id":"https:\/\/kedar.nitty-witty.com\/blog\/#person","name":"Kedar","image":{"@type":"ImageObject","@id":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4#personImage","url":"https:\/\/secure.gravatar.com\/avatar\/be1f7021485c325d92cc4c5d961accb9c1af72b1b11281a790f7f4f066ef10d3?s=96&d=mm&r=g","width":96,"height":96,"caption":"Kedar"}},{"@type":"Person","@id":"https:\/\/kedar.nitty-witty.com\/blog\/author\/admin#author","url":"https:\/\/kedar.nitty-witty.com\/blog\/author\/admin","name":"Kedar","image":{"@type":"ImageObject","@id":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4#authorImage","url":"https:\/\/secure.gravatar.com\/avatar\/be1f7021485c325d92cc4c5d961accb9c1af72b1b11281a790f7f4f066ef10d3?s=96&d=mm&r=g","width":96,"height":96,"caption":"Kedar"}},{"@type":"WebPage","@id":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4#webpage","url":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4","name":"A Unique Foreign Key issue in MySQL 8.4 | Change Is Inevitable","description":"MySQL 8.4 enforces stricter foreign key rules. This post breaks down ERROR 6125, the causes, workaround, and how to properly prepare schema before upgrade.","inLanguage":"en-US","isPartOf":{"@id":"https:\/\/kedar.nitty-witty.com\/blog\/#website"},"breadcrumb":{"@id":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4#breadcrumblist"},"author":{"@id":"https:\/\/kedar.nitty-witty.com\/blog\/author\/admin#author"},"creator":{"@id":"https:\/\/kedar.nitty-witty.com\/blog\/author\/admin#author"},"image":{"@type":"ImageObject","url":"https:\/\/kedar.nitty-witty.com\/blog\/wp-content\/uploads\/2025\/04\/mysql8.4-foreign-keys-png.avif","@id":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4\/#mainImage","width":1200,"height":675,"caption":"mysql8.4-foreign-keys"},"primaryImageOfPage":{"@id":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4#mainImage"},"datePublished":"2025-04-24T08:30:00+00:00","dateModified":"2025-04-24T08:21:43+00:00"},{"@type":"WebSite","@id":"https:\/\/kedar.nitty-witty.com\/blog\/#website","url":"https:\/\/kedar.nitty-witty.com\/blog\/","name":"..::CHANGE is INEVITABLE::..","description":"Kedar Vaijanapurkar's Blog for MySQL, technology and various subjects","inLanguage":"en-US","publisher":{"@id":"https:\/\/kedar.nitty-witty.com\/blog\/#person"}}]},"og:locale":"en_US","og:site_name":"Change Is Inevitable | Kedar Vaijanapurkar's Blog for MySQL, technology and various subjects","og:type":"article","og:title":"A Unique Foreign Key issue in MySQL 8.4 | Change Is Inevitable","og:description":"MySQL 8.4 enforces stricter foreign key rules. This post breaks down ERROR 6125, the causes, workaround, and how to properly prepare schema before upgrade.","og:url":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4","article:published_time":"2025-04-24T08:30:00+00:00","article:modified_time":"2025-04-24T08:21:43+00:00","twitter:card":"summary","twitter:title":"A Unique Foreign Key issue in MySQL 8.4 | Change Is Inevitable","twitter:description":"MySQL 8.4 enforces stricter foreign key rules. This post breaks down ERROR 6125, the causes, workaround, and how to properly prepare schema before upgrade."},"aioseo_meta_data":{"post_id":"3463","title":null,"description":"MySQL 8.4 enforces stricter foreign key rules. This post breaks down ERROR 6125, the causes, workaround, and how to properly prepare schema before upgrade.","keywords":null,"keyphrases":{"focus":{"keyphrase":"","score":0,"analysis":{"keyphraseInTitle":{"score":0,"maxScore":9,"error":1}}},"additional":[]},"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":"","og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"Article","isEnabled":true},"graphs":[]},"schema_type":"default","schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":"-1","robots_max_videopreview":"-1","robots_max_imagepreview":"large","priority":null,"frequency":"default","location":null,"local_seo":null,"breadcrumb_settings":null,"limit_modified_date":false,"ai":null,"created":"2025-04-24 07:06:20","updated":"2025-08-16 19:23:36","seo_analyzer_scan_date":null},"aioseo_breadcrumb":"<div class=\"aioseo-breadcrumbs\"><span class=\"aioseo-breadcrumb\">\n\t\t\t<a href=\"https:\/\/kedar.nitty-witty.com\/blog\" title=\"Home\">Home<\/a>\n\t\t<\/span><span class=\"aioseo-breadcrumb-separator\">\u00bb<\/span><span class=\"aioseo-breadcrumb\">\n\t\t\t<a href=\"https:\/\/kedar.nitty-witty.com\/blog\/category\/mysql\" title=\"MySQL\">MySQL<\/a>\n\t\t<\/span><span class=\"aioseo-breadcrumb-separator\">\u00bb<\/span><span class=\"aioseo-breadcrumb\">\n\t\t\t<a href=\"https:\/\/kedar.nitty-witty.com\/blog\/category\/mysql\/mysql-upgrade\" title=\"MySQL Upgrade\">MySQL Upgrade<\/a>\n\t\t<\/span><span class=\"aioseo-breadcrumb-separator\">\u00bb<\/span><span class=\"aioseo-breadcrumb\">\n\t\t\tA Unique Foreign Key issue in MySQL 8.4\n\t\t<\/span><\/div>","aioseo_breadcrumb_json":[{"label":"Home","link":"https:\/\/kedar.nitty-witty.com\/blog"},{"label":"MySQL","link":"https:\/\/kedar.nitty-witty.com\/blog\/category\/mysql"},{"label":"MySQL Upgrade","link":"https:\/\/kedar.nitty-witty.com\/blog\/category\/mysql\/mysql-upgrade"},{"label":"A Unique Foreign Key issue in MySQL 8.4","link":"https:\/\/kedar.nitty-witty.com\/blog\/a-unique-foreign-key-issue-in-mysql-8-4"}],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3463","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=3463"}],"version-history":[{"count":1,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3463\/revisions"}],"predecessor-version":[{"id":3465,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3463\/revisions\/3465"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media\/3464"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=3463"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=3463"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=3463"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}