MySQL - 一个表上的行删除不会更新另一张表上的关系

Question

我有 2 个表，一个用于员工，另一个用于部门。一个部门可以有多个/不同的员工，但一个员工只能在一个部门工作。他们的关系是一对多。

表格创建:

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

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 (department_name) REFERENCES department(name) 
    ON UPDATE CASCADE ON DELETE CASCADE) 
ENGINE=INNODB;

表格插入:

INSERT INTO department(name,street) VALUES ('Alexandroupoli', 'Leoforos Dimokratias21');
INSERT INTO department(name,street) VALUES ('Athens','Basilisis Sofias 111');
INSERT INTO department(name,street) VALUES ('Patras','Smurnis 34');
INSERT INTO department(name,street) VALUES ('Kalamata','Leoforos Fountas 241');
INSERT INTO department(name,street) VALUES ('Heraklion','Leoforos Enetwn 132');
INSERT INTO department(name,street) VALUES ('Thessaloniki','Karolou 45');
INSERT INTO department(name,street) VALUES ('Xanthi','Agia Barbasa 68');
INSERT INTO department(name,street) VALUES ('Larisa','Hroon Polutexneiou 12');

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');

UPDATE department SET employees_count = (SELECT COUNT(*) FROM employee WHERE department.name = employee.department_name);

在创建 2 个表并在它们之间创建外键约束后，当我从部门表中删除部门时，每个关联的员工也会根据需要被删除。

但是，当我从员工表中删除员工时，department 中的 employees_count 行没有改变。例如，如果我有 2 名员工的 department_name = Athens，那么在删除其中一名员工后，当我转到 departments 表时，employees_count 仍然等于 2 而不是 1。

同样的事情发生在更新 employees 表中的 department_name 时。如果对于一名员工，我将他的 department_name 从“Athens”更新为“Patras”，部门表保持不变。

所以我在想，我删除一个员工后，说不定还要执行这个命令

UPDATE department SET employees_count = (SELECT COUNT(*) FROM employee WHERE department.name = employee.department_name);

那肯定会解决我的问题。

但是，有没有办法让 department 表中的 employees_count 列自动分配给在那里工作的员 worker 数，而不仅仅是一个整数字段？有没有更实用的方法来解决这个问题，而不是在每次删除后添加更新命令？

Answer 1

我会说您不需要将部门计数作为单独的属性存储在表中；您始终可以通过查询找到该值。

此外，删除记录可以是非常绝对的。这绝不是最佳做法，但我更喜欢将记录标记为已删除(可能带有时间戳)。这是信息，我猜是元数据，可能会引起人们的兴趣。这样所有的员工信息都被保留了下来。值得深思。

希望对您有所帮助。