如何在MySQL中构建外键

Question

我有2个表 - 第一表：

CREATE TABLE COFFEE_SHOP 
(
    shop_id INT,
    shop_name VARCHAR(50),
    city VARCHAR(500),
    state CHAR(2),
    PRIMARY KEY (shop_id)
);

INSERT INTO COFFEE_SHOP (shop_id, shop_name, city, state)
VALUES (1, "store g", "melbourne", "CA"),
       (6, "store x", "palms", "FL"),
       (4, "store b", "grandi", "TX"),
       (2, "store a", "flonce", "GA");

第二个表：

CREATE TABLE EMPLOYEE 
(
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    hire_date DATE,
    job_title VARCHAR(50),
    shop_id INT references COFFEE_SHOP(shop_id)
);

INSERT INTO EMPLOYEE (first_name, last_name, hire_date, job_title)
VALUES("jim", "jordan", "2002-03-05", "barista"),
      ("george", "washington", "2015-03-02", "manager"),
      ("abraham", "lincon", "2020-04-07", "grinder"),
      ("barack", "obama", "1999-05-09", "front office"),
      ("george", "bush", "2150-03-10", "tender");

问题：为什么以下查询返回null以及如何修复它，以使第二个表实际上引用了第一个并将shop_id用作第二个表中的外键。

查询：

SELECT * 
FROM EMPLOYEE

此查询将返回所有shop_id值的null。如何连接两个表，以使每个员工都有一个引用的shop_id来自coffee_shop？

另外，我尝试使用以下代码在雇员中制作外键，并且它也无法正常工作。

FOREIGN KEY(shop_id) REFERENCES COFFEE_SHOP(shop_id)

Answer 1

例如，如果您想告诉MySQL Jim Jordan在墨尔本的商店商店G中工作，则需要以这种方式进行插入：

INSERT INTO EMPLOYEE (first_name, last_name, hire_date, job_title, shop_id) VALUES("jim", "jordan", "2002-03-05", "barista", 1)

外键需要知道哪个主要密钥适合您在另一个表中的需求。

由于外键不能为null，因此您必须提供相关的 shop_id 。