MySQL 5.7.18: 外键约束和 ALTER TABLE CHANGE COLUMN 从 NULL 到 NOT NULL

Question

以下 SQL 脚本适用于 MySQL 5.16.17 及更早版本，但不适用于我其中一个 MySQL 5.7.18 安装（另一个在 Docker 容器中启动的 MySQL 5.7.18 是正常的）

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;

错误信息为：

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

问题似乎是将具有外键约束的列从NULL更改为NOT NULL。我知道我可以在最后一个语句中包装“SET foreign_key_checks…”调用，但我很想知道是否有任何系统变量或配置设置会影响MySQL在这种情况下的行为，因为我无法解释两个5.7.18实例之间的不同行为。

- ndeuma

---

我无法重现这个问题。您可以告诉我们SQL_MODE变量的值。请参见db-fiddle。 - wchiquito

5.7.18本地安装中的sql_mode： NO_AUTO_CREATE_USER，NO_ENGINE_SUBSTITUTION5.7.18 Docker容器中的sql_mode： ONLY_FULL_GROUP_BY，STRICT_TRANS_TABLES，NO_ZERO_IN_DATE，NO_ZERO_DATE，ERROR_FOR_DIVISION_BY_ZERO，NO_AUTO_CREATE_USER，NO_ENGINE_SUBSTITUTION（是的，我在本地安装实例中禁用了STRICT_TRANS_TABLES以查看是否有任何影响，但没有成功） - ndeuma

使用db-fiddle，代码片段在5.6版本中失败，但在5.5和5.7版本中运行成功。有趣的是... - ndeuma

请参见Bug #77591 ALTER TABLE does not allow to change NULL/NOT NULL if foreign key exists。 - wchiquito

Answer 1

您可以将 FOREIGN_KEY_CHECKS 设置为零

SET FOREIGN_KEY_CHECKS = 0;

alter table bar change column foo_id foo_id int not null;

SET FOREIGN_KEY_CHECKS = 1;

- Nishant Nair