Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL ERROR 1832: cannot change a column used in a foreign key

ErrorERROR 1832 (HY000): Cannot change column 'user_id': used in a foreign key constraint 'fk_orders_user'

Short answer

MySQL blocks changes to a foreign key column when they could break the link between parent and child. Drop the foreign key, change the column, then add the key again with the same name. Do the same for the parent column; a character set change needs foreign_key_checks off on both tables.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the table definition to get the constraint name and the columns on both sides.

    SHOW CREATE TABLE orders\G
  2. 2Match what you see, then jump to its fix

    1. 1You changed a VARCHAR length, an integer type or a collation on a column that has a foreign key.The new data type is not a safe changeFix
    2. 2The change that fails is only a NOT NULL on the child column.NULL to NOT NULL in a non-strict sessionFix
    3. 3The statement is ALTER TABLE ... CONVERT TO CHARACTER SET on a table with a text column in a foreign key.A character set conversion with foreign_key_checks onFix

What does the error look like?

  • ERROR 1832 (HY000): Cannot change column 'user_id': used in a foreign key constraint 'fk_orders_user'
  • Cannot change column '%s': used in a foreign key constraint '%s'
  • Cannot change column '%s': used in a foreign key constraint '%s' of table '%s'

Cause 1 of 3

The new data type is not a safe change

1Check

The server prohibits changes that may lose referential integrity. VARCHAR(20) to VARCHAR(30) is allowed, but to VARCHAR(1024) is not, because the number of length bytes changes.

SHOW CREATE TABLE orders\G

2Fix

Drop the foreign key, change the column in both tables so the types still match, then add the key back.

ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;
ALTER TABLE users MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE orders MODIFY user_id BIGINT UNSIGNED NOT NULL;
ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id);

3Verify

The definition shows the new type and the constraint again.

SHOW CREATE TABLE orders\G

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

NULL to NOT NULL in a non-strict session

1Check

In non-strict mode this is prohibited so that NULLs are not turned into default values that have no parent row. In strict mode it is allowed, and fails only if a conversion is needed.

SELECT @@SESSION.sql_mode;
SELECT COUNT(*) FROM orders WHERE user_id IS NULL;

2Fix

Fix the NULL rows first, then run the change in a strict session.

SET SESSION sql_mode = CONCAT(@@SESSION.sql_mode, ',STRICT_TRANS_TABLES');
ALTER TABLE orders MODIFY user_id BIGINT UNSIGNED NOT NULL;

3Verify

The column shows NOT NULL.

SHOW COLUMNS FROM orders LIKE 'user_id';

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

A character set conversion with foreign_key_checks on

1Check

With foreign_key_checks enabled, conversion is not allowed on a table with a character column used in a foreign key.

SELECT @@foreign_key_checks;

2Fix

Turn checks off, convert both tables, turn checks on. Do not turn them back on after only one table.

SET foreign_key_checks = 0;
ALTER TABLE parent CONVERT TO CHARACTER SET utf8mb4;
ALTER TABLE child CONVERT TO CHARACTER SET utf8mb4;
SET foreign_key_checks = 1;

3Verify

Both tables show the new character set.

SHOW CREATE TABLE child\G

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Change parent and child columns in one migration so their types never drift.
  • Name foreign keys yourself, so the drop and the re-add use the same name.
  • Run such migrations on a copy of production first; the table is rebuilt.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Databases

Adding a foreign key fails because the two columns do not match

ERROR 3780 (HY000): Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'fk_orders_user' are incompatible.

Error 3780 means the column in the child table and the column it references are defined differently.

2 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.