MySQL 5.7.18: Foreign key constraints and ALTER TABLE CHANGE COLUMN from NULL to NOT NULL

Question

The following SQL script works with MySQL 5.16.17 and older, but not with one of my MySQL 5.7.18 installations (the other one, MySQL 5.7.18 launched in a Docker container, is OK as well)

drop table if exists bar;
drop table if exists foo;

create table foo (foo_id int not null primary key, description varchar(32));
insert into foo values ("1", "foo-one");
insert into foo values ("2", "foo-two");

create table bar (bar_id int not null primary key, foo_id int null, description varchar(32), foreign key (foo_id) references foo(foo_id));
insert into bar values ("1", "1", "bar-one");
insert into bar values ("2", "1", "bar-two");

alter table bar change column foo_id foo_id int not null;

The error message is:

Error Code: 1832. Cannot change column 'foo_id': used in a foreign key constraint 'bar_ibfk_1'

The problems seems to be changing a column with a foreign key constraint from NULL to NOT NULL.

I know I could just wrap the last statement in a "SET foreign_key_checks..." call, but I am be interested in whether there are any system variables or configuration settings that influence the behavior of MySQL in this case, because I cannot explain the different behavior between two 5.7.18 instances.

Answer 1

you can set FOREIGN_KEY_CHECKS to zero

SET FOREIGN_KEY_CHECKS = 0;

alter table bar change column foo_id foo_id int not null;

SET FOREIGN_KEY_CHECKS = 1;