MySQL ERROR 1832: cannot change a column used in a foreign key
ERROR 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.
Fix it fast
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\G2Match what you see, then jump to its fix
- 1
You 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
The change that fails is only a NOT NULL on the child column.NULL to NOT NULL in a non-strict sessionFix - 3
The 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
- 1
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\G2Fix
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\GConfirmed by dev.mysql.com
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
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\GConfirmed by dev.mysql.com
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Cannot drop index: needed in a foreign key constraint (MySQL 1553)
Cannot drop index: needed in a foreign key constraint
InnoDB raises error 1553 when you try to drop the last index that can enforce a foreign key.
2 causes, each with a fixDatabases
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 fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fix