添加外键遇Error 3780/1452错误，求排查解决方法

我创建了数据库hpbooks2及相关数据表，执行ALTER TABLE添加外键约束时遇到两个错误：

1. Error Code: 3780. 外键约束FK_scriptID中的引用列ScriptID和被引用列scriptID不兼容。
2. Error Code: 1452. 无法添加或更新子行：外键约束失败（hpbooks2.#sql-152_b, CONSTRAINT FK_translatorID FOREIGN KEY (TranslatorID) REFERENCES translator (ID)）

原SQL代码如下：

create database hpbooks2;

use hpbooks2;
CREATE TABLE Books 
(
    BookID  INT primary key,
    BookNo  INT,
    LanguageID  INT,
    ScriptID    VARCHAR(512),
    Category    VARCHAR(512),
    Authorised  VARCHAR(512),
    TranslatorID    INT,
    Title   VARCHAR(512)
);
CREATE TABLE Script 
(
    Script  VARCHAR(512),
    ScriptID    INT primary key
);
CREATE TABLE Translator 
(
    Translator  VARCHAR(512),
    ID  INT primary key
);
CREATE TABLE Language 
(
    Language    VARCHAR(512),
    LanguageID  INT primary key
);
CREATE TABLE Category 
(
    Category    VARCHAR(512) primary key,
    Description VARCHAR(512)
);
use hpbooks2;
alter table books
add constraint FK_translatorID foreign key (translatorID) references Translator(ID);
use hpbooks2;
alter table books
add constraint FK_scriptID foreign key (ScriptID) references Script(scriptID);

错误1：列类型不兼容（Error 3780） #

原因 #

Books表的ScriptID是VARCHAR(512)类型，但Script表的主键ScriptID是INT类型。外键约束要求引用列和被引用列的数据类型、长度、字符集必须完全一致，类型不匹配直接导致约束创建失败。

解决步骤 #

先修改Books表中ScriptID的类型，和Script表主键类型对齐：

ALTER TABLE Books MODIFY COLUMN ScriptID INT;

之后再创建外键约束：

ALTER TABLE Books ADD CONSTRAINT FK_scriptID FOREIGN KEY (ScriptID) REFERENCES Script(ScriptID);

错误2：子行数据违反外键约束（Error 1452） #

原因 #

Books表中已经存在的TranslatorID值，在Translator表的ID列里找不到对应记录。外键约束要求子表（Books）的外键列值必须全部存在于父表（Translator）的主键列中，否则无法创建约束。

解决步骤 #

两种处理方式二选一：

• 方式一：清理无效数据，把Books表中找不到对应Translator的TranslatorID设为NULL

  UPDATE Books b
  SET b.TranslatorID = NULL
  WHERE NOT EXISTS (SELECT 1 FROM Translator t WHERE t.ID = b.TranslatorID);
  

• 方式二：往Translator表补全缺失的ID记录

  INSERT INTO Translator(ID)
  SELECT DISTINCT TranslatorID FROM Books WHERE TranslatorID IS NOT NULL;
  

处理完数据后，执行添加外键的语句：

ALTER TABLE Books ADD CONSTRAINT FK_translatorID FOREIGN KEY (TranslatorID) REFERENCES Translator(ID);

修正后的完整SQL流程 #

CREATE DATABASE hpbooks2;

USE hpbooks2;

CREATE TABLE Books 
(
    BookID INT PRIMARY KEY,
    BookNo INT,
    LanguageID INT,
    ScriptID INT, -- 直接设为INT，避免后续修改
    Category VARCHAR(512),
    Authorised VARCHAR(512),
    TranslatorID INT,
    Title VARCHAR(512)
);

CREATE TABLE Script 
(
    Script VARCHAR(512),
    ScriptID INT PRIMARY KEY
);

CREATE TABLE Translator 
(
    Translator VARCHAR(512),
    ID INT PRIMARY KEY
);

CREATE TABLE Language 
(
    Language VARCHAR(512),
    LanguageID INT PRIMARY KEY
);

CREATE TABLE Category 
(
    Category VARCHAR(512) PRIMARY KEY,
    Description VARCHAR(512)
);

-- 先处理TranslatorID数据问题（示例：补全缺失的ID）
INSERT INTO Translator(ID)
SELECT DISTINCT TranslatorID FROM Books WHERE TranslatorID IS NOT NULL;

-- 添加Translator外键
ALTER TABLE Books ADD CONSTRAINT FK_translatorID FOREIGN KEY (TranslatorID) REFERENCES Translator(ID);

-- 添加Script外键
ALTER TABLE Books ADD CONSTRAINT FK_scriptID FOREIGN KEY (ScriptID) REFERENCES Script(ScriptID);

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