A Unique Foreign Key issue in MySQL 8.4

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:

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'.

At first glance, message is straightforward — the foreign key was pointing to a column without a unique constraint. BUT I’d not write a blog if that’s the only thing! In this post we analyze the working, the work around, the fixtures and meet a bug.

[root@mysql84 ~]# mysql -e “create database production_database;"
[root@mysql84 ~]# mysql production_database < production_database.sql
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'
[root@mysql84 ~]#

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:

ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint…

This happens because, starting with MySQL 8.4, the server enforces stricter foreign key rules — 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’s enforced by default using the new system variable restrict_fk_on_non_standard_key.

MySQL 8.4 Documentation reads this…

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 –skip-restrict-fk-on-non-standard-key.
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.
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.

He wasn’t sure of if he can make the unique index on the system and he was in “hurry”. So I suggested the workaround to skip the foreign key issue temporarily:

mysql> set global restrict_fk_on_non_standard_key=OFF;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+———+——+———————————————————————————————————————————————————————————–+
| Level | Code | Message |
+———+——+———————————————————————————————————————————————————————————–+
| Warning | 4166 | ‘restrict_fk_on_non_standard_key’ 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. |
+———+——+———————————————————————————————————————————————————————————–+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@mysql84 ~]# mysql upgradetest < upgradetest.sql
[root@mysql84 ~]# echo $?
0
[root@mysql84 ~]#

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.
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’s something called “let me see more”!

If you’re as curious, I’d ask you to run this along with me.

The “Unique” Foreign Key Issue

I decided to run some test around this to understand what’s going on in MySQL 8.4.

Following 4 tables were created in MySQL 8.0:


DROP TABLE IF EXISTS clients;
CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    name VARCHAR(100),
    UNIQUE KEY (client_id),
    INDEX idx_client_id (client_id)
);

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT,
    order_details VARCHAR(255),
    FOREIGN KEY (client_id) REFERENCES clients(client_id)
);

DROP TABLE IF EXISTS orders_2;
DROP TABLE IF EXISTS clients_2;
CREATE TABLE clients_2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    name VARCHAR(100),
    INDEX idx_client_id (client_id)
);

CREATE TABLE orders_2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT,
    order_details VARCHAR(255),
    FOREIGN KEY (client_id) REFERENCES clients_2(client_id)
);

To ensure compatibility before upgrading to MySQL 8.4, I ran the check-for-server-upgrade utility using MySQL Shell 8.4:

[root@centos_1 ~]# mysqlsh -- util check-for-server-upgrade root@localhost

...

4) Checks for foreign keys not referencing a full unique index
(foreignKeyReferences)
   Foreign keys to partial indexes may be forbidden as of 8.4.0, this check
   identifies such cases to warn the user.

   test.orders_ibfk_1 - invalid foreign key defined as 'orders(client_id)'
      references a non unique key at table 'clients'.
   test.orders_2_ibfk_1 - invalid foreign key defined as 'orders_2(client_id)'
      references a non unique key at table 'clients_2'.

This tool flagged invalid foreign key definitions — 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.

How to identify Foreign Key compatibility?

The beauty of opensource allows you to explore the code under the hood and look what I found: http://github.com/mysql/mysql-shell/blob/8.4.4/modules/util/upgrade_checker/upgrade_check_creators.cc#L1581

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.

Did you note that the query wrongly identifies error about client_id not having unique key for clients table?! The clients table’s client_id does have a UNIQUE KEY yet it is reported. Why? It is not a critical issue but it is a bug.

Executing above query manually returns two records:

select
  fk.constraint_schema,
  fk.constraint_name,
  '',
  fk.parent_fk_definition as fk_definition,
  fk.REFERENCED_TABLE_NAME as target_table,
  '##fkToNonUniqueKey'
from (select
      rc.constraint_schema,
      rc.constraint_name,
      CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name order by kc.ORDINAL_POSITION),')') as parent_fk_definition,
      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,
      rc.REFERENCED_TABLE_NAME
    from
      information_schema.REFERENTIAL_CONSTRAINTS rc
        join
          information_schema.KEY_COLUMN_USAGE kc
        on
          rc.constraint_schema = kc.constraint_schema AND
          rc.constraint_name = kc.constraint_name AND
          rc.constraint_schema = kc.REFERENCED_TABLE_SCHEMA AND
          rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND
          kc.REFERENCED_TABLE_NAME is not NULL AND
          kc.REFERENCED_COLUMN_NAME is not NULL
    where
      true
    group by
      rc.constraint_schema,
      rc.constraint_name,
      rc.table_name,
      rc.REFERENCED_TABLE_NAME) fk
  join (SELECT
      CONCAT(table_schema,'.',table_name,'(',GROUP_CONCAT(column_name order by seq_in_index),')') as fk_definition,
      SUM(non_unique) as non_unique_count
    FROM
      INFORMATION_SCHEMA.STATISTICS
    WHERE
      sub_part IS NULL
    GROUP BY
      table_schema, table_name, index_name) idx
    on
      fk.target_fk_definition = idx.fk_definition AND
      idx.non_unique_count > 0
+-------------------+-----------------+--+---------------------+--------------+--------------------+
| constraint_schema | constraint_name |  | fk_definition       | target_table | ##fkToNonUniqueKey |
+-------------------+-----------------+--+---------------------+--------------+--------------------+
| test              | orders_ibfk_1   |  | orders(client_id)   | clients      | ##fkToNonUniqueKey |
| test              | orders_2_ibfk_1 |  | orders_2(client_id) | clients_2    | ##fkToNonUniqueKey |
+-------------------+-----------------+--+---------------------+--------------+--------------------+

Fixing the query

We can rewrite this query and correctly report:

SELECT
  fk.constraint_schema,
  fk.constraint_name,
  '',
  fk.parent_fk_definition AS fk_definition,
  fk.REFERENCED_TABLE_NAME AS target_table,
  '##fkToNonUniqueKey'
FROM (
  SELECT
    rc.constraint_schema,
    rc.constraint_name,
    CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name ORDER BY kc.ORDINAL_POSITION), ')') AS parent_fk_definition,
    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,
    rc.REFERENCED_TABLE_NAME
  FROM
    information_schema.REFERENTIAL_CONSTRAINTS rc
  JOIN
    information_schema.KEY_COLUMN_USAGE kc
  ON
    rc.constraint_schema = kc.constraint_schema AND
    rc.constraint_name = kc.constraint_name AND
    rc.constraint_schema = kc.REFERENCED_TABLE_SCHEMA AND
    rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND
    kc.REFERENCED_TABLE_NAME IS NOT NULL AND
    kc.REFERENCED_COLUMN_NAME IS NOT NULL
  GROUP BY
    rc.constraint_schema,
    rc.constraint_name,
    rc.table_name,
    rc.REFERENCED_TABLE_NAME
) fk
LEFT JOIN (
  SELECT
    CONCAT(table_schema, '.', table_name, '(', GROUP_CONCAT(column_name ORDER BY seq_in_index), ')') AS fk_definition
  FROM
    INFORMATION_SCHEMA.STATISTICS
  WHERE
    sub_part IS NULL AND
    non_unique = 0  -- Only consider unique indexes
  GROUP BY
    table_schema, table_name, index_name
) unique_idx ON fk.target_fk_definition = unique_idx.fk_definition
WHERE
  unique_idx.fk_definition IS NULL;
+-------------------+-----------------+--+---------------------+--------------+--------------------+
| constraint_schema | constraint_name |  | fk_definition       | target_table | ##fkToNonUniqueKey |
+-------------------+-----------------+--+---------------------+--------------+--------------------+
| test              | orders_2_ibfk_1 |  | orders_2(client_id) | clients_2    | ##fkToNonUniqueKey |
+-------------------+-----------------+--+---------------------+--------------+--------------------+

I have already reported this bug and it has been verified.

Bug Report: http://bugs.mysql.com/bug.php?id=118023

Conclusion

Hope this post clarified what “unique” about “foreign keys” 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’s a good reminder: every upgrade can change behavior, and knowing the details matters.

Quick fixes like disabling restrict_fk_on_non_standard_key might help short-term, but the real fix is adapting to MySQL’s latest standards.

Let me know your learning stories until I come back with another one. Happy Upgrading.

A Unique Foreign Key issue in MySQL 8.4

The “Unique” Foreign Key Issue

How to identify Foreign Key compatibility?

Fixing the query

Conclusion

Kedar

Related Tags

1 comment

Leave a Reply Cancel reply