MySQL如何正确创建外键 解决EMPLOYEE表shop_id为NULL问题

• 查询返回所有shop_id为NULL的直接原因：你写的EMPLOYEE表插入语句中，指定的插入字段只有first_name、last_name、hire_date、job_title四个，完全没有包含shop_id字段，也没有给该字段传值。在没有给字段设置非空约束、默认值的情况下，数据库会自动给未赋值的字段填充NULL，和外键是否生效无关。
• 外键创建不生效的两个核心原因：
  • MySQL不会解析列级定义的references语法，也就是你建表时在shop_id INT后面直接跟references COFFEE_SHOP(shop_id)的写法不会实际创建外键约束，MySQL只支持表级声明的FOREIGN KEY语法。
  • 你后续补加外键时，表内所有记录的shop_id全为NULL，不满足外键关联的数据一致性要求；如果表使用的是MyISAM等不支持外键的存储引擎，也会导致外键创建失败。

按顺序执行以下操作即可完成两张表的正确关联：

1. 校验并修改存储引擎
   外键约束只有InnoDB引擎支持，先执行语句确认两张表的引擎，不符合就修改：

   -- 查询两张表的当前引擎
   SHOW TABLE STATUS WHERE Name IN ('COFFEE_SHOP', 'EMPLOYEE');
   
   -- 若引擎不是InnoDB，执行以下语句修改
   ALTER TABLE COFFEE_SHOP ENGINE = InnoDB;
   ALTER TABLE EMPLOYEE ENGINE = InnoDB;
   

2. 补全现有员工数据的合法shop_id
   外键要求从表（EMPLOYEE）的关联字段值，必须在主表（COFFEE_SHOP）的被关联字段中存在，不能为非法值。你需要先给所有员工记录分配真实存在的门店ID（COFFEE_SHOP现有合法shop_id为1、2、4、6），参考更新语句如下，可根据实际所属门店调整对应ID：

   UPDATE EMPLOYEE
   SET shop_id = CASE employee_id
       WHEN 1 THEN 1
       WHEN 2 THEN 2
       WHEN 3 THEN 4
       WHEN 4 THEN 6
       WHEN 5 THEN 1
   END;
   

   更新完成后执行SELECT * FROM EMPLOYEE;确认所有shop_id字段都无NULL、无不存在的非法ID。
3. 添加正式外键约束
   数据符合校验规则后，执行表级外键添加语句即可创建成功：

   ALTER TABLE EMPLOYEE
   ADD CONSTRAINT fk_emp_shop FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id);
   

4. 规范后续插入逻辑
   之后新增员工记录时，必须在插入字段列表中带上shop_id，且传入的值必须是COFFEE_SHOP中已存在的合法ID，否则数据库会直接抛出外键校验错误，拒绝插入非法数据。正确插入示例：

   INSERT INTO EMPLOYEE (first_name, last_name, hire_date, job_title, shop_id)
   VALUES ("tom", "smith", "2024-05-01", "barista", 2);
   

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