MariaDB设置已创建主键auto_increment报外键约束错误排查

两个核心错误导致报错：

• 删错了外键约束
  你之前只删除了fk_produzione（这是Produzione表中id_film关联Film.id的外键），但真正阻塞操作的是InnoDB自动生成的外键Produzione_ibfk_1——这个约束是Produzione.id_casaProduzione关联CasaProduzione.id的外键，从你贴的SHOW CREATE TABLE结果可以看到，这个约束从头到尾都没被删掉。
• ALTER语句存在笔误，且违反InnoDB外键规则
  你贴的报错SQL是修改Produzione表的id字段，但Produzione表根本不存在id字段，你实际要修改的是父表CasaProduzione的id字段——你之前执行modify column id tinyint(4) first的时候没写auto_increment属性，已经把这个字段原有的自增属性弄丢了。
  InnoDB要求外键的引用列和被引用列定义必须完全一致，只要外键存在，直接修改被引用的父表列、或者子表的引用列定义，都会触发1833报错。
  另外你之前执行的alter table Produzione modify data in smallint(4);存在语法错误（多写了一个in），执行后已经把data字段原有的NOT NULL属性弄丢了，后续需要一并修复。

按顺序执行以下SQL即可：

1. 删除真正阻塞操作的外键

ALTER TABLE Produzione DROP FOREIGN KEY Produzione_ibfk_1;

2. 修复CasaProduzione表的id字段，把列顺序、主键、自增属性都配置正确

ALTER TABLE CasaProduzione MODIFY COLUMN id TINYINT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;

3. 修复Produzione表被改坏的data字段，再把外键加回去

-- 修复data字段的类型和非空属性
ALTER TABLE Produzione MODIFY data SMALLINT(4) NOT NULL;
-- 加回关联CasaProduzione的外键，建议显式指定约束名方便后续维护
ALTER TABLE Produzione ADD CONSTRAINT fk_produzione_casa
FOREIGN KEY (id_casaProduzione) REFERENCES CasaProduzione(id)
ON UPDATE CASCADE ON DELETE RESTRICT;
-- 如果之前删除了fk_produzione，一并加回
ALTER TABLE Produzione ADD CONSTRAINT fk_produzione
FOREIGN KEY (id_film) REFERENCES Film(id);

• 执行ALTER操作前，先通过SHOW CREATE TABLE 表名确认当前表的字段、约束真实状态，不要凭记忆写SQL
• 建议创建外键时都手动指定有业务意义的约束名，不要依赖InnoDB自动生成的xxx_ibfk_n格式命名，避免后续删约束找错对象
• 用MODIFY修改字段时，必须把该字段原有的所有属性（非空、主键、默认值、自增等）都写全，否则未声明的属性会被直接清空，你之前丢失auto_increment和NOT NULL属性都是这个原因导致的。

内容的提问来源于stack exchange，提问作者keiichi