修改外键引用目标失败求助：调整company表actor_id外键指向

问题场景 #

你创建了company表，将actor_id字段设置为外键，引用actor表的主键id：

CREATE TABLE company(
id INT NOT NULL AUTO_INCREMENT,
actor_id INT NOT NULL,
CONSTRAINT aID FOREIGN KEY (actor_id) REFERENCES actor(id),
PRIMARY KEY(id)
);

尝试修改该外键，使其引用director表的主键id，执行了以下语句后，发现外键仍指向actor表，未达到预期效果：

ALTER TABLE company
 DROP FOREIGN KEY aID,
 ADD CONSTRAINT actID FOREIGN KEY (actor_id) REFERENCES director(id);

调整方案 #

针对这个问题，可以从以下几个方面调整操作：

• 拆分ALTER语句，确保外键删除操作生效
  部分数据库（如MySQL）在单条ALTER语句中执行多操作时，若某一步失败，可能会出现后续操作不生效但原有约束保留的情况。建议拆分语句分步执行：

  -- 单独执行外键删除
  ALTER TABLE company DROP FOREIGN KEY aID;
  -- 再执行新外键添加
  ALTER TABLE company ADD CONSTRAINT actID FOREIGN KEY (actor_id) REFERENCES director(id);
  

  执行后可以用以下语句验证外键状态：

  -- MySQL环境下查询外键关联信息
  SELECT CONSTRAINT_NAME, REFERENCED_TABLE_NAME 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
  WHERE TABLE_NAME = 'company' AND COLUMN_NAME = 'actor_id';
  

• 校验字段类型一致性
  外键字段与引用的主键字段必须数据类型、长度、符号属性（如UNSIGNED）完全匹配。如果director表的id字段类型与company表的actor_id不匹配，新外键会创建失败，原有外键自然保留。可以通过以下语句查看字段定义：

  DESC company;
  DESC director;
  

  若类型不一致，需先修改company表的actor_id字段类型，使其与director.id一致，再执行外键修改操作。

• 检查事务与锁表情况
  如果存在未提交的事务，可能会阻塞ALTER TABLE操作，导致外键修改不生效。需先提交或回滚所有相关事务，确保表处于可修改状态后，再执行外键调整操作。

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