MySQL环境下如何校验Employee表录入的Region值存在于Regions表

前提确认 #

• 首先确保Regions表中存储区域值的字段设置了唯一约束/主键，通常建议将该字段命名为region_name，数据类型设为VARCHAR(20)即可适配你现有的5个区域枚举值。
• 确保两张表的存储引擎都是InnoDB，Employee表的Region字段和Regions表的区域名字段的数据类型、长度、字符集、排序规则完全一致，否则外键约束无法创建。
• 确认你的Amazon RDS MySQL实例参数组中foreign_key_checks参数值为ON，这是RDS MySQL的默认配置，没有手动修改过不需要额外调整。

方案1：使用外键约束（推荐，性能最高、校验逻辑原生可靠） #

步骤1：初始化Regions表（若还未创建） #

执行以下SQL创建表并预置你需要的5个区域值：

CREATE TABLE Regions (
    region_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '区域主键ID',
    region_name VARCHAR(20) NOT NULL UNIQUE COMMENT '区域名称'
);

INSERT INTO Regions (region_name) 
VALUES ('Northeast'), ('Southeast'), ('Central'), ('Northwest'), ('Southwest');

步骤2：给Employee表加外键约束 #

分两种场景处理：

场景A：Employee表还未创建

创建表时直接关联Regions表：

CREATE TABLE Employee (
    emp_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',
    -- 其他员工字段，比如name、phone等
    region_name VARCHAR(20) NOT NULL COMMENT '所属区域',
    -- 外键约束
    FOREIGN KEY (region_name) REFERENCES Regions(region_name)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

约束规则说明：ON DELETE RESTRICT表示如果某区域还有关联的员工，不允许删除Regions表中的该区域记录；ON UPDATE CASCADE表示如果Regions表的区域名修改，Employee表对应的值会自动同步。

场景B：Employee表已经存在

首先先校验现有Employee表中的region字段有没有不在Regions表中的值，避免加约束失败：

SELECT DISTINCT region FROM Employee 
WHERE region NOT IN (SELECT region_name FROM Regions);

把返回的异常值修正后，再执行加约束的语句：

ALTER TABLE Employee
ADD CONSTRAINT fk_employee_region
FOREIGN KEY (region) REFERENCES Regions(region_name)
ON DELETE RESTRICT ON UPDATE CASCADE;

用MySQL Workbench可视化操作步骤 #

如果不想写SQL，可以用Workbench操作：

1. 连接到你的RDS MySQL实例，在左侧导航栏找到Regions表，右键点击「Alter Table」，确认存储区域的字段勾选了「PK」（主键）或者「UQ」（唯一约束），保存修改。
2. 同样右键点击Employee表选「Alter Table」，切换到「Foreign Keys」标签页。
3. 点击左下角的「+」添加外键，参考表选Regions，Employee这边的列选你存储区域的字段，Regions那边的列选对应的区域名字段，设置删除规则为RESTRICT、更新规则为CASCADE，点击应用即可。

约束生效验证 #

执行一条非法插入语句测试：

INSERT INTO Employee (emp_id, region) VALUES (1001, 'InvalidRegion');

如果返回1452 - Cannot add or update a child row: a foreign key constraint fails的报错，说明校验规则已经生效。

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