MySQL - 一对多关系无法正常工作

Question

我有 2 个表，一个用于员工，另一个用于部门。一个部门可以有多个员工，但一个员工只能在一个部门工作。他们的关系是[1:many]。

我正尝试在 MySQL 中执行此操作，但我遇到了一个问题。如果我有 8 个不同的部门，并且我尝试添加总共超过 8 个在不同部门工作的员工，我会收到以下错误:

Cannot add or update a child row: a foreign key constraint fails (`testdb`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`id`) REFERENCES `department` (`id`))

如果我有 8 名或更少的员工，一切都很好。添加第 9 名员工后，出现上述错误。

部门表:

CREATE TABLE IF NOT EXISTS department(
    id INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(255) NOT NULL UNIQUE
)ENGINE=INNODB;

部门插入:

INSERT INTO department(name) VALUES ('Athens');
INSERT INTO department(name) VALUES ('Patras');
INSERT INTO department(name) VALUES ('Kalamata');
INSERT INTO department(name) VALUES ('Heraklion');
INSERT INTO department(name) VALUES ('Thessaloniki');
INSERT INTO department(name) VALUES ('Xanthi');
INSERT INTO department(name) VALUES ('Larisa');
INSERT INTO department(name) VALUES ('Alexandroupoli');

员工表:

CREATE TABLE IF NOT EXISTS employee(
    id INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL, 
    last_name VARCHAR(255) NOT NULL, 
    email VARCHAR(255) NOT NULL, 
    born INT(20) NOT NULL, 
    country VARCHAR(255) NOT NULL, 
    department_name VARCHAR(255) NOT NULL, 
    FOREIGN KEY (id) references department(id)
)ENGINE=INNODB;

员工插入:

INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Vaggelis','Michos','vagg7@gmail.com','1995','Greece','Athens');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('James','Gunn','james8@gmail.com','1970','USA','Athens');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('George','McMahon','george95@gmail.com','1978','Usa','Patras');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('John','Jones','john13@gmail.com','1992','England','Patras');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Marinos','Kuriakopoulos','marin_kur@gmail.com','1986','Greece','Alexandroupoli');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Dimitris','Nikolaou','dimitis8@yahoo.gr','1984','Greece','Larisa');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Soufiane','El Kaddouri','sofiane@yahoo.com','1974','France','Xanthi');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Maria','Apostolou','mariamaria1@gmail.com','1997','Greece','Larisa');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Ioannis','Marinou','ioannis_ap@yahoo.gr','1982','Greece','Kalamata');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Thanasis','Athanasiou','thanos89@gmail.com','1989','Cyprus','Heraklion');

这是 CREATE-INSERT 操作之后的样子:

如您所见，在 Employees 表中插入第 9 次时，插入失败，我得到了我描述的错误，即:

Cannot add or update a child row: a foreign key constraint fails (`testdb`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`id`) REFERENCES `department` (`id`))

Answer 1

返回departments的外键设置为id，在employees表中就是employee_id；它需要引用 department_id。我将 department_id 添加到您的员工表并将外键引用更改为 department_id。我删除了 department_name，因为它是冗余数据。

CREATE TABLE IF NOT EXISTS employee(
    id INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL, 
    last_name VARCHAR(255) NOT NULL, 
    email VARCHAR(255) NOT NULL, 
    born INT(20) NOT NULL, 
    country VARCHAR(255) NOT NULL, 
    department_id INT(20)
    FOREIGN KEY (department_id) references department(id)

)引擎=INNODB;