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

Question

我有两张桌子，一张是给员工的，另一张是给部门的。一个部门可以有多个/不同的员工，但一个员工只能在一个部门工作。他们的关系是一对多。

表创建：

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','[e-mail]','1995','Greece','Athens');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('James','Gunn','[e-mail]','1970','USA','Athens');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('George','McMahon','[e-mail]','1978','Usa','Patras');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('John','Jones','[e-mail]','1992','England','Patras');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Marinos','Kuriakopoulos','[e-mail]','1986','Greece','Alexandroupoli');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Dimitris','Nikolaou','[e-mail]','1984','Greece','Larisa');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Soufiane','El Kaddouri','[e-mail]','1974','France','Xanthi');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Maria','Apostolou','[e-mail]','1997','Greece','Larisa');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Ioannis','Marinou','[e-mail]','1982','Greece','Kalamata');
INSERT INTO employee(first_name,last_name,email,born,country,department_name) VALUES('Thanasis','Athanasiou','[e-mail]','1989','Cyprus','Heraklion');

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

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

Answer 1

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

此外，删除记录可能是非常绝对的。这绝不是最佳实践，但我更喜欢将记录标记为已删除（可能带有时间戳）。这是信息，我猜是元数据，可能会引起兴趣。这样，所有的员工信息都被保留了下来。发人深省。

希望这能有所帮助。